<?php
if(!(is_readable(realpath(dirname(__FILE__)).'/mysqlroutines.inc'))) {
        die('Error - Could not open or read file '.realpath(dirname(__FILE__)).'/mysqlroutines.inc');
} else {
        include_once(realpath(dirname(__FILE__)).'/mysqlroutines.inc');
}

function db_resetTableIncrement($cn, $tablename, $id){
        $cn->query("set @startId=(select IFNULL(MAX(".$id.") + 1, 1) from ".$tablename.");
                SET @qry = CONCAT('ALTER TABLE ".$tablename."
                AUTO_INCREMENT=', @startId);
                PREPARE stmt  FROM @qry;
                execute stmt ;");
		while($cn->next_result()) $cn->store_result();//STU без этой строки падает
}

function db_resetObjectIncrement($cn){
        $cn->query("set @startId=(select IFNULL(MAX(idobject) + 1, 1) from objects);
                SET @qry = CONCAT('ALTER TABLE objects
                AUTO_INCREMENT=', @startId);
                PREPARE stmt  FROM @qry;
                execute stmt ;");
}

function db_clearPreparedTree($cn, $skbranch){
        $query = 'DELETE FROM prepared_tree WHERE skbranch = '.mysqli_real_escape_string($cn, $skbranch).';';
	db_runQuery($cn, $query);        
}

function db_clearAllPreparedTrees($cn){
        $query = 'DELETE FROM prepared_tree;';
	db_runQuery($cn, $query);        
}

function db_clearObjects($cn, $skbranch){
        db_clearPreparedTree($cn, $skbranch);
        $cn->query('DELETE FROM objects WHERE skbranch = '.mysqli_real_escape_string($cn, $skbranch).';'); 
        $cn->query('DELETE FROM `rightsobjects` WHERE skbranch = '.mysqli_real_escape_string($cn, $skbranch).';'); 
        $cn->query('DELETE `objectsprohibitionrightscopy` FROM `objectsprohibitionrightscopy` LEFT JOIN `objects` ON `objectsprohibitionrightscopy`.idobject = `objects`.idobject WHERE `objects`.o_ileft IS NULL;'); 
        $cn->query('DELETE FROM `titleindex` WHERE skbranch = '.mysqli_real_escape_string($cn, $skbranch).';'); 
        db_resetTableIncrement($cn, 'objects', 'idobject');
        //logError($cn, $query);
}

function db_clearReports($cn, $skbranch){
        $cn->query('DELETE FROM `reports` WHERE skbranch = '.mysqli_real_escape_string($cn,$skbranch).';'); 
        db_resetTableIncrement($cn, 'reports', 'idreport');
        $cn->query('DELETE FROM `rightsreports` WHERE skbranch = '.mysqli_real_escape_string($cn,$skbranch).';'); 
        $cn->query('DELETE FROM `objectsreports` WHERE skbranch = '.mysqli_real_escape_string($cn,$skbranch).';'); 
        $cn->query('DELETE FROM `reportindex` WHERE skbranch = '.mysqli_real_escape_string($cn,$skbranch).';'); 
        //logError($cn, $query);
}

function db_clearObjectlinks($cn, $skbranch){
        $cn->query('DELETE FROM `objectlinks` WHERE skbranch = '.mysqli_real_escape_string($cn,$skbranch).';'); 
        //logError($cn, $query);
}

function db_clearSections($cn){
        $cn->query('DELETE FROM `sections`;');
        $cn->query('ALTER TABLE `sections` AUTO_INCREMENT = 1;');
        //logError($cn, $query);
}

function db_clearLinks($cn){
        $cn->query('DELETE FROM `links`;');
        $cn->query('ALTER TABLE `links` AUTO_INCREMENT = 1;');
        //logError($cn, $query);
}

function db_clearUserlinks($cn){
        $cn->query('DELETE FROM `userslinks`;');
        $cn->query('ALTER TABLE `userslinks` AUTO_INCREMENT = 1;');
        //logError($cn, $query);
}

function db_clearReportsusersquery($cn, $skbranch){
        $cn->query('DELETE FROM `reportsusersquery` WHERE skbranch = '.mysqli_real_escape_string($cn,$skbranch).';'); 
        //logError($cn, $query);
}

function db_clearAllowLanguages($cn){
        $cn->query('UPDATE `languages` SET main = 0, allow = 0;');
        //logError($cn, $query);
}

function db_clearBranches($cn){
        $cn->query('DELETE FROM `branches`;');
        $cn->query('ALTER TABLE `branches` AUTO_INCREMENT = 1;');
        $cn->query('DELETE FROM `resourcefromtables` WHERE idtable = 7;');
        //logError($cn, $query);
}

function db_clearRightsPollingObjects($cn){
        $cn->query('DELETE `rightspollingobjects`;');
        //logError($cn, $query);
}

function db_clearResourceFromTables($cn, $skbranch){
        $cn->query('DELETE FROM `resourcefromtables` WHERE idtable IN (1, 2, 5, 1101, 1102, 1111, 2111) AND skbranch = '.mysqli_real_escape_string($cn, $skbranch).';');
        $cn->query('DELETE FROM `resourcefromtables` WHERE idtable IN (3, 4) AND skbranch = 0 AND '.mysqli_real_escape_string($cn, $skbranch).' = (SELECT skbranch FROM branches WHERE ismain);');
}
function db_clearPortal($cn){
        $skbranch = db_getMainSKBranch($cn);
        db_clearObjects($cn, $skbranch);
        db_clearReports($cn, $skbranch);
        db_clearObjectlinks($cn, $skbranch);
        db_clearUserlinks($cn);
        db_clearLinks($cn);
        db_clearSections($cn);
        db_clearReportsusersquery($cn, $skbranch);
        // 60080 db_clearAllowLanguages($cn);
        db_clearRightsPollingObjects($cn);
        db_clearResourceFromTables($cn, $skbranch);
        $cn->query('UPDATE `users` SET `user_isactive` = 0 ;');
}

// 60080
function db_clearBranch($cn, $skbranch){
        db_clearObjects($cn, $skbranch);
        db_clearReports($cn, $skbranch);
        db_clearResourceFromTables($cn, $skbranch);
        db_clearObjectlinks($cn, $skbranch);
        db_clearReportsusersquery($cn, $skbranch);
        db_clearReportDataViewCountForBranch($cn, $skbranch);
        db_deletePollingObjectAssignment($cn, $skbranch); //63469
        $cn->query("UPDATE `branches` SET `iscomplete` = 0  WHERE skbranch = ".mysqli_real_escape_string($cn, $skbranch).";");
}

function db_shrinkIndexes($cn){
        $cn->query('DELETE FROM titleindex WHERE idobject NOT IN(SELECT idobject FROM objects);');
        $cn->query('DELETE FROM titleindex WHERE idreport NOT IN(SELECT idreport FROM reports);');
        $cn->query('DELETE FROM reportindex WHERE idobject NOT IN(SELECT idobject FROM objects);');
        $cn->query('DELETE FROM reportindex WHERE idreport NOT IN(SELECT idreport FROM reports);');        
}

//8472
function db_clearPersonalPages($cn){
        $skbranch = db_getMainSKBranch($cn);
        db_clearUserlinks($cn);
        db_clearLinks($cn);
        db_clearSections($cn);
        $cn->query('DELETE FROM `resourcefromtables` WHERE idtable IN (3, 4) AND  skbranch = 0;');
}

// удалить пользователя по uhash
function db_deleteUser($cn, $uhash){
	$iduser = db_getIDUserByHash($cn, $uhash);
	//удалять сообщения пользователей и их статистику не нужно
	//$query = "UPDATE `forummessages` SET iduser = 0, frmm_isdelete = 1 WHERE iduser = ".$iduser.";";
	//db_runQuery($cn, $query);
	//$query = "DELETE FROM `reportdata` WHERE iduser = ".$iduser.";";
	//db_runQuery($cn, $query);
	$query = "DELETE FROM `objectlinks` WHERE iduser = ".$iduser.";";
	db_runQuery($cn, $query);
        $query = "DELETE FROM `personallinks` WHERE iduser = ".$iduser.";";
	db_runQuery($cn, $query);
	$query = "DELETE FROM `reportsusersquery` WHERE iduser = ".$iduser.";";
	db_runQuery($cn, $query);
	$query = "DELETE FROM `rightsobjects` WHERE iduser = ".$iduser.";";
	db_runQuery($cn, $query);
	$query = "DELETE FROM `rightsreports` WHERE iduser = ".$iduser.";";
	db_runQuery($cn, $query);
	$query = "DELETE FROM `userslinks`  WHERE iduser = ".$iduser.";";
	db_runQuery($cn, $query);
	$query = "DELETE FROM `usersgroups`  WHERE iduser = ".$iduser.";";
	db_runQuery($cn, $query);
 	$query = "UPDATE `users` SET `user_isactive` = 0 WHERE iduser = ".$iduser.";";
	db_runQuery($cn, $query);
        $query = "DELETE FROM prepared_tree WHERE `usersidsarr` = '".$iduser."' OR `usersidsarr` LIKE '%,".$iduser."' OR `usersidsarr` LIKE '".$iduser.",%' OR `usersidsarr` LIKE '%,".$iduser.",%';";
	db_runQuery($cn, $query);        
}

// удалить персональную страницу пользователя по uhash
function db_deletePersonalPage($cn, $uhash){
	$iduser = db_getIDUserByHash($cn, $uhash);
	$query = "DELETE FROM `userslinks`  WHERE iduser = ".$iduser.";";
	db_runQuery($cn, $query);
}

// Удалить все записи в таблице по названию
function db_deleteAllRowsinTable($cn, $tablename){
	$query = "DELETE FROM `".mysqli_real_escape_string($cn,$tablename)."` ;";
	db_runQuery($cn, $query);
}

// Удалить отчет по rguid
function db_deleteReportByGuid($cn, $rguid, $idlanguage = null){
        $escrguid = mysqli_real_escape_string($cn,$rguid);
        if ($idlanguage == null){
		$query = "DELETE FROM `avgreportdata` WHERE r_guid='".$escrguid."';";
		db_runQuery($cn, $query);
		$query = "DELETE FROM `objectsreports` WHERE idreport in (select idreport from `reports` WHERE r_guid = '".$escrguid."' )  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
		$query = "DELETE FROM `personallinks` WHERE r_guid='".$escrguid."' AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
    	        $query = "DELETE FROM `reportdata` WHERE r_guid='".$escrguid."';";
		db_runQuery($cn, $query);
		$query = "DELETE FROM `reportindex` WHERE idreport in (select idreport from `reports` WHERE r_guid = '".$escrguid."' )  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
		$query = "DELETE FROM `reportsusersquery` WHERE r_guid='".$escrguid."';";
		db_runQuery($cn, $query);
		$query = "DELETE FROM `resourcefromtables` WHERE idrecord in (select idreport from `reports` WHERE r_guid = '".$escrguid."' ) AND idtable = 2  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
                $query = "DELETE FROM `rightsreports` WHERE idreport in (select idreport from `reports` WHERE r_guid = '".$escrguid."' )  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
                $query = "DELETE FROM `titleindex`  WHERE idreport in (select idreport from `reports` WHERE r_guid = '".$escrguid."' )  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
                $query = "DELETE FROM `reports` WHERE r_guid='".$escrguid."'  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
        } else {
		$idr = db_getIDReportByGuid($cn, $rguid, $idlanguage);
		$query = "DELETE FROM `avgreportdata` WHERE r_guid='".$escrguid."';";
		db_runQuery($cn, $query);
		$query = "DELETE FROM `objectsreports` WHERE idreport = ".$idr."  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
		$query = "DELETE FROM `personallinks` WHERE r_guid='".$escrguid."'  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
    	        $query = "DELETE FROM `reportdata` WHERE r_guid='".$escrguid."'  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
		$query = "DELETE FROM `reportindex` WHERE idreport = ".$idr."  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
		$query = "DELETE FROM `reportsusersquery` WHERE r_guid='".$escrguid."';";
		db_runQuery($cn, $query);
		$query = "DELETE FROM `resourcefromtables` WHERE idrecord = ".$idr." AND idtable = 2  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
                $query = "DELETE FROM `rightsreports` WHERE idreport = ".$idr."  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
                $query = "DELETE FROM `titleindex`  WHERE idreport = ".$idr."  AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
                $query = "DELETE FROM `reports` WHERE idreport = ".$idr." AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
        }
}

// Удалить права на объект по guid и uhash
function db_deleteRightsObjects($cn, $uhash, $oguid){
		$iduser = db_getIDUserByHash($cn, $uhash);
		$ido = db_getIDObjectByGuid($cn, $oguid);
		$query = "DELETE FROM `rightsobjects`  WHERE iduser = ".$iduser." AND idobject = ".$ido." AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1);";
		db_runQuery($cn, $query);
                $query = "DELETE FROM prepared_tree WHERE `usersidsarr` = '".$iduser."' OR `usersidsarr` LIKE '%,".$iduser."' OR `usersidsarr` LIKE '".$iduser.",%' OR `usersidsarr` LIKE '%,".$iduser.",%';";                
		db_runQuery($cn, $query);
}

// Удалить права на объект по guid
function db_deleteAllRightsObjects($cn, $oguid){
		$ido = db_getIDObjectByGuid($cn, $oguid);
		$query = "DELETE FROM `rightsobjects`  WHERE idobject = ".$ido." AND skbranch = (SELECT skbranch FROM branches WHERE ismain = 1 LIMIT 1); DELETE FROM prepared_tree;";
		db_runQuery($cn, $query);
}

// Удалить права на отчет по guid и uhash
function db_deleteRightsReports($cn, $uhash, $rguid){
		$iduser = db_getIDUserByHash($cn, $uhash);
		$query = "DELETE FROM `rightsreports`  WHERE iduser = ".$iduser." AND idreport in (select idreport from `rightsreports` WHERE r_guid = '".mysqli_real_escape_string($cn,$rguid)."' );";
		db_runQuery($cn, $query);
}

// команда удалить права на объект опроса для пользователя 
function db_deleteRightsPollingObjects($cn, $uhash, $idpollingobject){
	$iduser = db_getIDUserByHash($cn, $uhash);
	$skpollingobject = db_getSKPollingObject($cn, $idpollingobject );
	$query = "DELETE FROM `rightspollingobjects`  WHERE iduser = ".$iduser." AND skpollingobject = ".$skpollingobject.";";
        db_runQuery($cn, $query);
}


function db_getPersonalLinks($cn, $user_ids){
        if (null === $user_ids || $user_ids[0] == 0 )
                return false;
        $resarr = array();
        $query = "SELECT idpersonallink, pl.addurlparams, pl.pl_pos, obj.o_icon, obj.o_guid, pl.r_guid, pl.skbranch, br.ismain 
                   FROM  personallinks as pl LEFT JOIN objects as obj ON pl.o_guid = obj.o_guid  AND pl.skbranch = obj.skbranch
                   INNER JOIN branches as br ON pl.skbranch = br.skbranch
                   WHERE pl.iduser = ".$user_ids[0]." 
                   ORDER BY pl.pl_pos;";
        $result = $cn->query($query);
        if ($result) {
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getObjectName($cn, $oguid, $idlanguage, $skbranch = NULL ){
        $query = "SELECT res.rft_columnname_text as o_name FROM objects as obj INNER JOIN resourcefromtables res ON obj.idobject = res.idrecord AND obj.skbranch = res.skbranch 
                WHERE obj.o_guid = '".$oguid."'
                  AND res.idtable = 1
                  AND res.idlanguage = ".($idlanguage == NULL ? "(SELECT `idlanguage` FROM `languages` WHERE main = 1 LIMIT 1)": $idlanguage)."
                  AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                LIMIT 1;";
        $result = $cn->query($query);
        $ret = $oguid;
        if ($result) {
                $ar = $result->fetch_assoc();
                if (is_array($ar) && sizeof($ar) > 0){
                        $ret = htmlentities($ar['o_name'], ENT_QUOTES, "UTF-8");
                }
                logError($cn, $query);
        }
        return $ret;
}


function db_getReportName($cn, $rguid, $idlanguage, $skbranch = NULL  ){
        $query = "SELECT res.rft_columnname_text as r_name
                FROM reports as rep INNER JOIN resourcefromtables res ON  rep.idreport = res.idrecord AND rep.skbranch = res.skbranch 
                WHERE rep.r_guid = '".$rguid."'
                  AND res.idtable = 2
                  AND res.idlanguage = ".$idlanguage."
                  AND rep.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                LIMIT 1;";
        $result = $cn->query($query);
        $ret = "";
        if ($result) {
                $ar = $result->fetch_assoc();
                if (is_array($ar) && sizeof($ar) > 0){
                        $ret =  htmlentities($ar['r_name'], ENT_QUOTES, 'UTF-8');
                }
                logError($cn, $query);
        }
        return $ret;
}

function db_addPersonalLink($cn, $iduser, $oguid, $rguid, $skbranch, $urlparams ){
        if (null === $iduser || $iduser == 0 )
                return false;
        $query = "call add_personallinks(".$iduser.",'".$oguid."', '".$rguid."', $skbranch, '".$urlparams."');";
        $cn->query($query);
        logError($cn, $query);
}

function db_delPersonalLink($cn, $iduser, $id){
        if (null === $iduser || $iduser == 0 )
                return false;
        $query = "CALL del_personallinks( ".$iduser." , ".$id." );";
        $cn->query($query);
        logError($cn, $query);
}

function db_movePersonalLink($cn, $iduser, $pos, $dest){
        if (null === $iduser || $iduser == 0 )
                return false;
        $query = "call move_personallinks(".$iduser.", ".$pos.", ".$dest.");";
        $cn->query($query);
        logError($cn, $query);
}

function db_addRating_ReportData($cn, $iduser, $oguid, $rguid, $rating, $skbranch){
        if (null === $iduser || $iduser == 0 )
                return false;
        $query = "call add_rating_reportdata('".session_id()."', '".$oguid."', '".$rguid."',".$iduser.",".$rating.", $skbranch);";
        $cn->query($query);
        logError($cn, $query);
}

function db_addViewCount_ReportData($cn, $iduser, $oguid, $rguid, $skbranch){
        if (null === $iduser || $iduser == 0 )
                return false;
        $query = "call add_viewcount_reportdata('".session_id()."', '".$oguid."', '".$rguid."',".$iduser.", $skbranch);";
        return db_runQuery($cn,$query);
}

function db_getViewCount_ReportData($cn, $oguid, $rguid, $skbranch){
        $resarr = array();
        $query = "SELECT SUM(`rd_viewcount`) as sumfield FROM `reportdata` WHERE `o_guid` = '".$oguid."' AND `r_guid` = '".$rguid."' AND `skbranch` = ".$skbranch.";";
        $result = $cn->query($query);
        logError($cn, $query);
        if ($result) {
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        return $resarr;
}

function db_getAvgCount_ReportData($cn, $oguid, $rguid, $skbranch){
        $resarr = array();
        $query = "SELECT ROUND(AVG(`rd_rating`) , 2) as avgfield, COUNT(0) as countfield FROM `reportdata` WHERE `rd_rating` > 0 and `o_guid` = '".$oguid."' AND `r_guid` = '".$rguid."' AND `skbranch` = ".$skbranch.";";
        $result = $cn->query($query);
        logError($cn, $query);
        if ($result) {
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        return $resarr;
}

function db_getReportData($cn, $iduser, $oguid, $rguid, $skbranch){
        $resarr = array();
        $query = "SELECT * FROM `reportdata` WHERE `o_guid` = '".$oguid."' AND `r_guid` = '".$rguid."' AND `iduser` = ".$iduser." AND `skbranch` = ".$skbranch.";";
        $result = $cn->query($query);
        logError($cn, $query);
        if ($result) {
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        return $resarr;
}

function db_getUserDataByHash($cn, $hash)
{
        $query = "SELECT * FROM users WHERE user_hash = '".mysqli_real_escape_string($cn,$hash)."' ;";
        $result = $cn->query($query);
        logError($cn, $query);
        if ($result) {
        	return $result->fetch_assoc();
        } else {
        	return null;
        }
}

function db_getUserDataByID($cn, $iduser)
{
        $query = "SELECT * FROM users WHERE iduser = ".mysqli_real_escape_string($cn,$iduser)." ;";
        $result = $cn->query($query);
        logError($cn, $query);
        if ($result) {
        	return $result->fetch_assoc();
        } else {
        	return null;
        }
}

function db_getUserNameByID($cn, $iduser, $idlanguage = NULL)
{
        // 59865
        $name = '';
        if($iduser && strlen($iduser) > 0 ){
                $query = "SELECT rft_columnname_text as user_name FROM resourcefromtables WHERE idtable = 6 AND idlanguage = ".(isset($idlanguage) ? $idlanguage : db_getUseridlanguageContent($cn, $iduser))." AND idrecord = ".mysqli_real_escape_string($cn,$iduser)." ;";
                $ar = $cn->query($query)->fetch_assoc();
                if (is_array($ar) && sizeof($ar) > 0){
                        $name  = $ar['user_name'];
                }
                logError($cn, $query);
        }
        return $name;

}

function db_saveUserDataByID($cn, $iduser, $user_idlanguageinterface, $user_idlanguagecontent, $user_notification_answer, $user_notification_allallow)
{
        $query = "UPDATE `users` SET  ";
        if (strlen($user_idlanguageinterface) > 0 && strlen($user_idlanguagecontent) > 0 ) {
                $query .= " user_idlanguageinterface = ".$user_idlanguageinterface."
                ,user_idlanguagecontent = ".$user_idlanguagecontent." , ";
        }
        $query .= " user_notification_answer = ".$user_notification_answer."
        ,user_notification_allallow = ".$user_notification_allallow."
        WHERE iduser = ".mysqli_real_escape_string($cn,$iduser)." ;";
        return db_runQuery($cn,$query);
}

function db_saveUserLangsByID($cn, $iduser, $user_idlanguage, $user_idlanguagecontent = '' )
{
        $query = "UPDATE `users` SET
        user_idlanguageinterface = ".$user_idlanguage."
        ,user_idlanguagecontent = ".($user_idlanguagecontent == '' ? $user_idlanguage : $user_idlanguagecontent)."
        WHERE iduser = ".mysqli_real_escape_string($cn,$iduser)." ;";
        return db_runQuery($cn,$query);
}

function db_getUserLangsByID($cn, $iduser)
{
        if($iduser && strlen($iduser) > 0 ){
                $query = "SELECT user_idlanguageinterface, user_idlanguagecontent FROM `users` WHERE iduser = ".mysqli_real_escape_string($cn,$iduser).";";
                $ar = $cn->query($query)->fetch_assoc();
                if (is_array($ar) && sizeof($ar) > 0){
                        $_SESSION['user_idlanguageinterface'] = db_checkInterfaceLangByID($cn, $ar['user_idlanguageinterface']);
                        $_SESSION['user_idlanguagecontent'] = db_checkContentLangByID($cn, $ar['user_idlanguagecontent']);
                        if ($_SESSION['user_idlanguageinterface'] != $ar['user_idlanguageinterface'] || $_SESSION['user_idlanguagecontent'] !=  $ar['user_idlanguagecontent']) { 
                                db_saveUserLangsByID($cn, $iduser, $_SESSION['user_idlanguageinterface'], $_SESSION['user_idlanguagecontent']);
                        }
                } else {
                        $_SESSION['user_idlanguageinterface'] = db_checkInterfaceLangByID($cn, 0);
                        $_SESSION['user_idlanguagecontent'] = db_checkContentLangByID($cn, 0);
                        db_saveUserLangsByID($cn, $iduser, $_SESSION['user_idlanguageinterface'], $_SESSION['user_idlanguagecontent']);
                }
                logError($cn, $query);
        } else {
                $_SESSION['user_idlanguageinterface'] = db_checkInterfaceLangByID($cn, 0);
                $_SESSION['user_idlanguagecontent'] = db_checkContentLangByID($cn, 0);
        }
}


function db_getUseridlanguageContent($cn, $iduser)
{
        $query = "SELECT user_idlanguagecontent FROM `users` WHERE iduser = ".mysqli_real_escape_string($cn,$iduser).";";
        $ar = $cn->query($query)->fetch_assoc();
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar['user_idlanguagecontent'];   
        } 
        logError($cn, $query);
}

function db_isUserAdminToPolls($cn, $usr_arr){
        if (isUserAdmin())
                return TRUE;
        $ids = implode(",", $usr_arr);
        $query = "SELECT 1 FROM `rightspollingobjects` WHERE `deny_allow` = 1 AND  `iduser` IN (".$ids.")  
                  UNION ALL
                  SELECT 1 FROM `rightsadminpollingobjects` WHERE `deny_allow` = 1 AND `iduser` IN (".$ids.")  
                  LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        return (is_array($ar) && sizeof($ar) > 0);
}

function db_isUserGetRightsToPoll($cn, $skpollingobject, $usr_arr){
        if (isUserAdmin())
                return TRUE;
        $ids = implode(",", $usr_arr);
        $query = "SELECT 1 FROM `rightspollingobjects` WHERE `deny_allow` = 1 AND skpollingobject = ".mysqli_real_escape_string($cn,$skpollingobject)." AND `iduser` IN (".$ids.") 
                  UNION ALL 
                  SELECT 1 FROM `rightsadminpollingobjects` WHERE `deny_allow` = 1 AND `iduser` IN (".$ids.") AND (`skproject` IS NULL or `skproject` = 0) AND `skbranch` IN (   
                        SELECT `skbranch` FROM `pollingobjects`  WHERE skpollingobject = ".mysqli_real_escape_string($cn,$skpollingobject).")             
                  UNION ALL 
                  SELECT 1 FROM `rightsadminpollingobjects` WHERE `deny_allow` = 1 AND `iduser` IN (".$ids.") AND `skproject` IS NOT NULL AND `skproject` IN (   
                        SELECT `skproject` FROM `pollingobjects`  WHERE (`skproject` IS NULL or `skproject` = 0) AND skpollingobject = ".mysqli_real_escape_string($cn,$skpollingobject).")             
                  LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        return (is_array($ar) && sizeof($ar) > 0);
}

function db_checkContentLangByID($cn, $idlanguage)
{
        $query = "SELECT idlanguage FROM `languages` WHERE idlanguage = ".mysqli_real_escape_string($cn,$idlanguage)." AND allow = 1 LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar['idlanguage'];
        } else {
				$lngList = getLanguageListFromBrowser();
				foreach ($lngList as $k => $v) {
					$query = "SELECT idlanguage FROM `languages` WHERE allow = 1 AND lang_short like '".$lngList[ $k ][ 0 ]."' LIMIT 1;";
					$ar = $cn->query($query)->fetch_assoc();
					if (is_array($ar) && sizeof($ar) > 0){
							return $ar['idlanguage'];
					}
				}
				return db_getDefaultLangID($cn);     
        }
        logError($cn, $query);
}


function db_checkInterfaceLangByID($cn, $idlanguage)
{
        $detected = false; 
        if (strlen($idlanguage) > 0){
                $query = "SELECT idlanguage, LOWER(lang_short) as `lang_short` FROM `languages` WHERE idlanguage = ".mysqli_real_escape_string($cn,$idlanguage)." LIMIT 1;";
                $ar = $cn->query($query)->fetch_assoc();
                $detected = true; 
                if (is_array($ar) && sizeof($ar) > 0){
                        if ($ar['idlanguage'] != null && !empty($ar['lang_short']) && (mb_stripos($_SESSION['allowLanguages'], $ar['lang_short'] ) !== FALSE )){
                                return $ar['idlanguage'];
                        } else {
                                $detected = false;
                        }
                } else {
                        $detected = false;
                }
        }
        
        if (!$detected) {
                $langFromBrowser = getLangFromBrowser();
                $query = "SELECT idlanguage, lang_short FROM `languages` WHERE lang_short like '".$langFromBrowser."' LIMIT 1;";
                $ar = $cn->query($query)->fetch_assoc();
                if ((is_array($ar) && sizeof($ar) > 0) && ($ar['idlanguage'] != null) && (mb_stripos($_SESSION['allowLanguages'], $ar['lang_short'] ) !== false) ){
                        return $ar['idlanguage'];
                } else {
                        $clientLang = getSetting('portal','defaultLanguage','en');
                        $query = "SELECT idlanguage, lang_short FROM `languages` WHERE lang_short like '".$clientLang."' LIMIT 1;";
                        $ar = $cn->query($query)->fetch_assoc();
                        if ((is_array($ar) && sizeof($ar) > 0) && ($ar['idlanguage'] != null) && (mb_stripos($_SESSION['allowLanguages'], $ar['lang_short'] ) !== false) ){
                                return $ar['idlanguage'];
                        } else {
                               StopPortalOnError();
                               return false;
                        }
                }
        }

        logError($cn, $query);
}

function db_isLanguageValidByID($cn, $idlanguage)
{
        $query = "SELECT 1 FROM `languages` WHERE idlanguage = ".mysqli_real_escape_string($cn,$idlanguage)." AND allow = 1 LIMIT 1;";
        if ($cn->query($query)) {
                $ar = $cn->query($query)->fetch_assoc();
                if (is_array($ar) && sizeof($ar) > 0){
                        return true;
                } else {
                        return false;
                }
        } else {
                return false;
        }
        logError($cn, $query);
}

function db_getDefaultLangShortName($cn)
{
        $query = "SELECT lang_short FROM `languages` WHERE main = 1 AND allow = 1 LIMIT 1;";
        $f = $cn->query($query);
        if ($f) {
                $ar = $cn->query($query)->fetch_assoc();
                if (is_array($ar) && sizeof($ar) > 0){
                        return $ar['lang_short'];
                } else {
                        return getSetting('portal', 'defaultLanguage', 'en');
                }
        } else {
                return getSetting('portal', 'defaultLanguage', 'en');
        }
        logError($cn, $query);
}

function db_getDefaultLangID($cn)
{
        $query = "SELECT idlanguage FROM `languages` WHERE main = 1 AND allow = 1 LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar['idlanguage'];
        } else {
                return db_getLanguageID($cn, getSetting('portal', 'defaultLanguage', 'en'));
        }
        logError($cn, $query);
}

function db_clearReportDataViewCountForBranch($cn, $skbranch)
{
        $query = 'DELETE FROM `reportdata` WHERE skbranch = '.mysqli_real_escape_string($cn,$skbranch).';'; 
        db_runQuery($cn,$query);
        $query = 'DELETE FROM `avgreportdata` WHERE skbranch = '.mysqli_real_escape_string($cn,$skbranch).';'; 
        return db_runQuery($cn,$query);
}


function db_clearReportDataRating($cn)
{
        $query = "UPDATE `reportdata` SET `rd_rating` = 0;";
        db_runQuery($cn,$query);
        $query = "UPDATE `avgreportdata` SET `ard_rating` = 0;";
        return db_runQuery($cn,$query);
}

function db_clearReportDataRatingforObjects($cn, $selobjects, $r_guid, $skbranch)
{
        $query = "UPDATE `reportdata` SET `rd_rating` = 0 WHERE `r_guid` = '".$r_guid."' AND `skbranch` = ".$skbranch." AND `o_guid` IN (SELECT `o_guid` FROM `objects` WHERE `idobject` IN (".implode(",", $selobjects)."));";
        db_runQuery($cn,$query);
        $query = "UPDATE `avgreportdata` SET `ard_rating` = 0 WHERE `r_guid` = '".$r_guid."' AND `skbranch` = ".$skbranch." AND `o_guid` IN (SELECT `o_guid` FROM `objects` WHERE `idobject` IN (".implode(",", $selobjects)."));";
        return db_runQuery($cn,$query);
}

function db_clearReportDataViewCount($cn)
{
        $query = "DELETE FROM `reportdata`;";
        db_runQuery($cn,$query);
        $query = "DELETE FROM `avgreportdata`;";
        return db_runQuery($cn,$query);
}

function db_clearReportDataViewCountforObjects($cn, $selobjects, $r_guid, $skbranch)
{
        $query = "UPDATE `reportdata` SET `rd_viewcount` = 0 WHERE `r_guid` = '".$r_guid."' AND `skbranch` = ".$skbranch."  AND `o_guid` IN (SELECT `o_guid` FROM `objects` WHERE `idobject` IN (".implode(",", $selobjects)."));";
        db_runQuery($cn,$query);

        $query = "UPDATE `avgreportdata` SET `ard_viewcount` = 0 WHERE `r_guid` = '".$r_guid."' AND `skbranch` = ".$skbranch."  AND `o_guid` IN (SELECT `o_guid` FROM `objects` WHERE `idobject` IN (".implode(",", $selobjects)."));";
        return db_runQuery($cn,$query);
}

function db_clearForumMessages($cn)
{
        $query = "DELETE FROM  `forummessages`;";
        db_runQuery($cn,$query);
        $query = "UPDATE `useranswersvalues` SET idforummessage = NULL;";
        db_runQuery($cn,$query);
        $query = "UPDATE `useranswersvaluesarchive` SET idforummessage = NULL;";
        db_runQuery($cn,$query);
        

        $query = "SELECT `file_path` FROM `files`;";
        $result = $cn->query($query);

        if ($result) {
                $row = null;
                while ($row = $result->fetch_assoc()) {
                        if (file_exists($row['file_path'])) {
                                unlink($row['file_path']);
                        }
                }
        }

        $query = "DELETE FROM `files`;";
        return db_runQuery($cn,$query);
}


function db_clearForumMessagesforObjects($cn, $selobjects, $skbranch)
{

        $query = "SELECT `file_path` FROM `files` WHERE idforummessage IN 
                        ( SELECT idforummessage FROM `forummessages` 
                           WHERE `o_guid` IN (SELECT `o_guid` FROM `objects` WHERE `idobject` IN (".implode(",", $selobjects)."))
                             AND `skbranch` = ".$skbranch." 
                        );";
        $result = $cn->query($query);
        if ($result) {
                $row = null;
                while ($row = $result->fetch_assoc()) {
                        if (file_exists($row['file_path'])) {
                                unlink($row['file_path']);
                        }
                }
        }

        $query = "UPDATE `useranswersvalues` SET idforummessage = NULL WHERE idforummessage IN 
                        ( SELECT idforummessage FROM `forummessages` 
                           WHERE `o_guid` IN (SELECT `o_guid` FROM `objects` WHERE `idobject` IN (".implode(",", $selobjects)."))
                             AND `skbranch` = ".$skbranch."
                        );";
        db_runQuery($cn,$query);

        $query = "UPDATE `useranswersvaluesarchive` SET idforummessage = NULL WHERE idforummessage IN 
                        ( SELECT idforummessage FROM `forummessages` 
                           WHERE `o_guid` IN (SELECT `o_guid` FROM `objects` WHERE `idobject` IN (".implode(",", $selobjects)."))
                             AND `skbranch` = ".$skbranch."
                        );";
        db_runQuery($cn,$query);

        $query = "DELETE FROM `files` WHERE idforummessage IN 
                        ( SELECT idforummessage FROM `forummessages` 
                           WHERE `o_guid` IN (SELECT `o_guid` FROM `objects` WHERE `idobject` IN (".implode(",", $selobjects)."))
                             AND `skbranch` = ".$skbranch."
                        );";
        db_runQuery($cn,$query);

        $query = "DELETE FROM `forummessages` 
                        WHERE `o_guid` IN (SELECT `o_guid` FROM `objects` WHERE `idobject` IN (".implode(",", $selobjects)."))
                        AND `skbranch` = ".$skbranch.";";
        return db_runQuery($cn,$query);

}

function db_setNolock($cn){
        $query = "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;";
        $ar = $cn->query($query);
}

function db_getUserIDByHash($cn, $hash)
{
        $resarr = array();
        db_setNolock($cn);
        $query = "SELECT iduser FROM users WHERE user_hash = '".mysqli_real_escape_string($cn,$hash)."' AND (iduser IN (SELECT iduser FROM rightsobjects) OR iduser IN (SELECT iduser FROM rightsreports));";
        $ar = $cn->query($query)->fetch_assoc();
        $id = 0;
        if (is_array($ar) && sizeof($ar) > 0){
                $id = $ar['iduser'];
        } else {
                $id = false;
        }
        logError($cn, $query);
        return $id;
}


function db_getUserRightsToObject($cn, $user_ids, $oguid, $skbranch = NULL)
{
        db_setNolock($cn);
        if (isUserAdmin())
                return TRUE;             
	if (null === $user_ids || $user_ids[0] == 0 ) {
                $query = "SELECT DISTINCT 1 FROM objects as obj  WHERE  obj.o_guid='".mysqli_real_escape_string($cn,$oguid)."' 
                        AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
                $ar = $cn->query($query)->fetch_assoc();
                logError($cn, $query);
    	        return (is_array($ar) && sizeof($ar) > 0);
 	} else {
                $ids = implode(",", $user_ids);
                $resarr = array();
                $query = "SELECT DISTINCT 1 FROM rightsobjects as ro INNER JOIN objects as obj ON ro.idobject = obj.idobject AND ro.skbranch = obj.skbranch 
                        WHERE ro.iduser in (".$ids.") 
                        AND obj.o_guid='".mysqli_real_escape_string($cn,$oguid)."' 
                        AND deny_allow in (0,1,2)
                        AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
                $ar = $cn->query($query)->fetch_assoc();
                logError($cn, $query);
                if (is_array($ar) && sizeof($ar) > 0){
                        $query = "SELECT DISTINCT 1 FROM rightsobjects as ro INNER JOIN objects as obj ON ro.idobject = obj.idobject AND ro.skbranch = obj.skbranch 
                                WHERE ro.iduser in (".$ids.") 
                                AND obj.o_guid='".mysqli_real_escape_string($cn,$oguid)."' 
                                AND `deny_allow` = 0
                                AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
                        $ar = $cn->query($query)->fetch_assoc();
                        logError($cn, $query);
                        if (is_array($ar) && sizeof($ar) > 0){
                                return null;
                        } else {
                                $query = "SELECT DISTINCT 1 FROM rightsobjects as ro INNER JOIN objects as obj ON ro.idobject = obj.idobject AND ro.skbranch = obj.skbranch 
                                        WHERE ro.iduser in (".$ids.") 
                                        AND obj.o_guid='".mysqli_real_escape_string($cn,$oguid)."' 
                                        AND `deny_allow` = 1
                                        AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
                                $ar = $cn->query($query)->fetch_assoc();
                                logError($cn, $query);
                                if (is_array($ar) && sizeof($ar) > 0){
                                        $query = "SELECT DISTINCT 1 FROM rightsobjects as ro INNER JOIN objects as obj ON ro.idobject = obj.idobject AND ro.skbranch = obj.skbranch
                                                   WHERE  ro.iduser in (".$ids.")
                                                   AND obj.o_guid='".mysqli_real_escape_string($cn,$oguid)."' 
                                                   AND `deny_allow` = 2
                                                   AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
                                        $ar = $cn->query($query)->fetch_assoc();
                                        logError($cn, $query);
                                        if (is_array($ar) && sizeof($ar) > 0){
                                                return true;
                                        } else {
                                                // 66658
                                                return db_checkPollRight($cn, $ids, $oguid, $skbranch);
                                        }
                                } else {
                                        // 66658
                                        return db_checkPollRight($cn, $ids, $oguid, $skbranch);
                                }
                        }
                } else {
                        // 66658
                        return db_checkPollRight($cn, $ids, $oguid, $skbranch);
                }
        }
}

function db_checkPollRight($cn, $ids, $oguid, $skbranch = NULL){
        $query = "SELECT 1 FROM `objects` as o INNER JOIN  `users` as u ON u.`user_guid` = o.`o_guid` 
        WHERE o.`o_guid` like '".mysqli_real_escape_string($cn,$oguid)."' 
        AND u.iduser in (".$ids.")
        AND o.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return true;
        } else {
                //Product Backlog Item 58729 (60646)
                //seek assigned polls 
                $query = "SELECT 1 FROM `pollingobjects` as po 
                                INNER JOIN `useranswers` as ua ON po.idpollingobject = ua.idpollingobject
                                INNER JOIN `users` as u ON u.`user_hash` = ua.`user_hash` 
                        WHERE po.`o_guid` = '".mysqli_real_escape_string($cn,$oguid)."' 
                        AND ua.idpollingobjectstatus = 1
                        AND u.iduser in (".$ids.")
                        AND po.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
                $ar = $cn->query($query)->fetch_assoc();
                logError($cn, $query);
                if (is_array($ar) && sizeof($ar) > 0){
                        return true;
                } else {
                        //seek assign observer role to polls management  
                        $query = "SELECT 1 FROM `pollingobjects` as po 
                                INNER JOIN `useranswers` as ua ON po.idpollingobject = ua.idpollingobject
                                INNER JOIN `rightspollingobjects` as rpo ON rpo.`skpollingobject` = po.`skpollingobject` 
                                WHERE po.`o_guid` = '".mysqli_real_escape_string($cn,$oguid)."' 
                                AND rpo.iduser in (".$ids.") AND rpo.deny_allow = 1
                                AND po.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
                        $ar = $cn->query($query)->fetch_assoc();
                        logError($cn, $query);
                        if (is_array($ar) && sizeof($ar) > 0){
                                return true;
                        } else {
                                //seek assign admin role to polls management  
                                $query = "SELECT 1 FROM `pollingobjects` as po 
                                        INNER JOIN `useranswers` as ua ON po.idpollingobject = ua.idpollingobject
                                        INNER JOIN `rightsadminpollingobjects` u ON CASE WHEN ( u.`skproject` IS NULL or u.`skproject` = 0 ) THEN po.`skbranch` = u.`skbranch` ELSE po.`skproject` = u.`skproject` END
                                        WHERE po.`o_guid` = '".mysqli_real_escape_string($cn,$oguid)."' 
                                        AND u.iduser in (".$ids.") AND u.deny_allow = 1
                                        AND po.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
                                        $ar = $cn->query($query)->fetch_assoc();
                                logError($cn, $query);
                                if (is_array($ar) && sizeof($ar) > 0){
                                        return true;
                                } else {
                                        return null;
                                }
                        }
                }
        }
}

function db_getUser_emailAndLangByIdForummessage($cn, $idforummessage, $iduser){
        $query = " SELECT usr.iduser, usr.user_email, usr.user_idlanguageinterface, usr.user_idlanguagecontent FROM `users` as usr, `forummessages` as frm ".
        		 " WHERE usr.iduser <> ".$iduser." AND usr.user_notification_answer = 1 AND usr.iduser = frm.iduser AND ".
        		 " frm.idforummessage = ".$idforummessage.";";
        $result = $cn->query($query);
        if ($result) {
                $row = null;
	        $resarr = array();
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getUser_emailAndLangByToAllowUsers($cn, $idobject, $iduser){
        db_setNolock($cn);
	$query = " DROP TEMPORARY TABLE IF EXISTS `ro_temp` ;";
	db_runQuery($cn, $query);

	$query = " CREATE TEMPORARY TABLE ro_temp (
		`idobject` BIGINT NOT NULL,
		`iduser` INT NOT NULL,
		`ad0` INT NOT NULL,
		`ad1` INT NOT NULL,
		`ad2` INT NOT NULL,
		 PRIMARY KEY (`idobject`, `iduser`)
		)ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;";
	db_runQuery($cn, $query);

    $query = " CREATE TEMPORARY TABLE ro_group_temp (
		`iduser` INT NOT NULL,
		 PRIMARY KEY (`iduser`)
		)ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;";
	db_runQuery($cn, $query);

	$query = " ALTER TABLE `ro_temp` ADD INDEX (`iduser`);";
	db_runQuery($cn, $query);

	$query = " INSERT IGNORE INTO `ro_temp`
	     SELECT DISTINCT ro.idobject, ro.iduser, 0, 0, 0  FROM  `rightsobjects`  ro
        		  WHERE ro.idobject  = ".$idobject.";";
	db_runQuery($cn, $query);

	$query = " INSERT IGNORE INTO `ro_temp`
	     SELECT DISTINCT ro.idobject, ug.iduser, 0, 0, 0  FROM  `rightsobjects`  ro, `usersgroups`  ug
        		  WHERE (ro.idobject  = ".$idobject." AND ug.idgroup = ro.iduser);";
	db_runQuery($cn, $query);

	$query = " UPDATE ro_temp INNER JOIN `rightsobjects` ON ((rightsobjects.`iduser` = ro_temp.`iduser` OR rightsobjects.`iduser` IN (SELECT DISTINCT `idgroup` FROM `usersgroups` WHERE `iduser` = ro_temp.`iduser`)) AND ro_temp.idobject = rightsobjects.idobject)  SET  ad0 = 1 WHERE  rightsobjects.`deny_allow` = 0; ";
	db_runQuery($cn, $query);
	$query = " UPDATE ro_temp INNER JOIN `rightsobjects` ON ((rightsobjects.`iduser` = ro_temp.`iduser` OR rightsobjects.`iduser` IN (SELECT DISTINCT `idgroup` FROM `usersgroups` WHERE `iduser` = ro_temp.`iduser`)) AND ro_temp.idobject = rightsobjects.idobject)  SET  ad1 = 1 WHERE  rightsobjects.`deny_allow` = 1; ";
	db_runQuery($cn, $query);
	$query = " UPDATE ro_temp INNER JOIN `rightsobjects` ON ((rightsobjects.`iduser` = ro_temp.`iduser` OR rightsobjects.`iduser` IN (SELECT DISTINCT `idgroup` FROM `usersgroups` WHERE `iduser` = ro_temp.`iduser`)) AND ro_temp.idobject = rightsobjects.idobject)  SET  ad2 = 1 WHERE  rightsobjects.`deny_allow` = 2; ";
	db_runQuery($cn, $query);

	$query = " DELETE FROM ro_temp WHERE ad0 = 1; ";
	db_runQuery($cn, $query);
	$query = " DELETE FROM ro_temp WHERE (ad1 < 1 OR ad2 < 1) ; ";
	db_runQuery($cn, $query);

	$query = " INSERT IGNORE INTO `ro_group_temp` SELECT DISTINCT  iduser FROM ro_temp; ";
	db_runQuery($cn, $query);

	$query = "  SELECT DISTINCT usr.iduser, usr.user_email, usr.user_idlanguageinterface, usr.user_idlanguagecontent FROM `users` as usr, `ro_temp` as ro ".
             "  WHERE usr.iduser = ro.iduser AND usr.user_notification_allallow = 1 AND usr.user_isgroup = 0 AND usr.iduser <> ".$iduser." ;";

    $result = $cn->query($query);
    $resarr = array();
    if ($result) {
            $row = null;
            while ($row = $result->fetch_assoc()) {
                    array_push($resarr, $row);
            }
    }
    logError($cn, $query);
    return $resarr;
}

function db_getIDObjectByGuid($cn, $oguid, $skbranch = NULL){
        $query = "SELECT idobject 
                    FROM objects 
                   WHERE o_guid='".mysqli_real_escape_string($cn, $oguid)."' 
                     AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
        $ar = $cn->query($query)->fetch_assoc();
        $ido = 0;
        if (is_array($ar) && sizeof($ar) > 0){
                $ido = $ar['idobject'];
        }
        logError($cn, $query);
        return $ido;
}

function db_getMaxObjectDepthFromParetn($cn, $parent_i_l, $parent_i_r){
        $query = "SELECT MAX(`o_depth`) as o_depth FROM `objects` WHERE `o_iright` between ".$parent_i_l." and ".$parent_i_r.";";
        $result = $cn->query($query);
        if ($result) {
                $ar = $result->fetch_assoc();
                if (is_array($ar) && sizeof($ar) > 0){
                        $ret = $ar['o_depth'];
                }
        } else {
                $ret = -1;
        }
        logError($cn, $query);
        return $ret;
}


function db_addForumFile($cn, $idforummessage, $file_name, $file_path){
        $query = "INSERT INTO `files` ( `idfile` ,`idforummessage` ,`file_name` ,`file_path` ) VALUES ( ";
        $query .= " NULL, ".mysqli_real_escape_string($cn,$idforummessage).", '".mysqli_real_escape_string($cn,$file_name)."', '".mysqli_real_escape_string($cn,$file_path)."');";
        db_runQuery($cn, $query);
}

function db_markForumMessageAsDelete($cn, $idforummessage){
        $query = "UPDATE `forummessages` SET `frmm_isdelete` = 1 WHERE `idforummessage` = ".$idforummessage.";"; 
        db_runQuery($cn, $query);
        $query = "UPDATE `useranswersvalues` SET `idforummessage` = NULL WHERE `idforummessage` = ".$idforummessage.";";
        db_runQuery($cn, $query);
}

function db_getPortalGuid($cn){
        $query = "SELECT guid FROM `portal`;";
        $ar = $cn->query($query)->fetch_assoc();
        $iduser = 0;
        if (is_array($ar) && sizeof($ar) > 0){
                $iduser = $ar['guid'];
        }
        logError($cn, $query);
        return $iduser;
}


function db_setPortalGuid($cn, $guid){
        $query = "UPDATE `portal` SET `guid` = '".$guid."';";
        db_runQuery($cn, $query);
}

function db_getPortalSessionKey($cn){
        $seskey = 0;
        $query = "SELECT sessionkey FROM `portal`;";
        $res = $cn->query($query);
        if ($res) {
                $ar = $res->fetch_assoc();
                if (is_array($ar) && sizeof($ar) > 0){
                        $seskey  = $ar['sessionkey'];
                }
                logError($cn, $query);
        }
        return $seskey;
}

function db_setPortalSessionKey($cn, $guid){
        $query = "UPDATE `portal` SET `sessionkey` = '".$guid."';";
        db_runQuery($cn, $query);
}

function db_getObjectDataByID($cn, $oid){
        $resarr = array();
        $query = "SELECT * FROM objects WHERE idobject=".mysqli_real_escape_string($cn, $oid)." LIMIT 1;";
        $result = $cn->query($query);
        logError($cn, $query);
        return $result->fetch_assoc();
}

function db_getIDReportByGuid($cn, $rguid, $idlanguage = 0, $skbranch = NULL){
        $idr = 0;
        $secidlanguage = mysqli_real_escape_string($cn,$idlanguage == NULL ? 0 : $idlanguage);
        $query = "SELECT idreport 
                    FROM reports 
                   WHERE r_guid='".mysqli_real_escape_string($cn,$rguid)."' 
                     AND (ifnull(idlanguage, ".$secidlanguage.") = ".$secidlanguage." OR idlanguage = 0) 
                     AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
        $ar = $cn->query($query)->fetch_assoc();
        if (is_array($ar) && sizeof($ar) > 0){
                $idr  = $ar['idreport'];
        } else {
                if (isset($idlanguage) && $idlanguage != 0){
                        $query = "SELECT idreport 
                                FROM reports 
                                WHERE r_guid='".mysqli_real_escape_string($cn,$rguid)."' 
                                AND idlanguage = (SELECT idlanguage FROM languages WHERE main = 1) AND idlanguage != 0 AND idlanguage IS NOT NULL
                                AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
                        $ar = $cn->query($query)->fetch_assoc();
                        if (is_array($ar) && sizeof($ar) > 0){
                                $idr  = $ar['idreport'];
                        }                        
                }
        }
        logError($cn, $query);
        return $idr;
}

function db_getReportByGuid($cn, $rguid){
        $query = "SELECT * FROM reports WHERE r_guid='".mysqli_real_escape_string($cn,$rguid)."';";
        return $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
}


function db_getIDSectionByGuid($cn, $sguid){
        $query = "SELECT idsection FROM sections WHERE sct_guid='".mysqli_real_escape_string($cn,$sguid)."';";
        $ar = $cn->query($query)->fetch_assoc();
        $idr = 0;
        if (is_array($ar) && sizeof($ar) > 0){
                $idr  = $ar['idsection'];
        }
        logError($cn, $query);
        return $idr;
}

function db_ddgetIDLinkByGuid($cn, $lguid){
        $query = "SELECT idlink FROM links WHERE lnk_guid='".mysqli_real_escape_string($cn,$lguid)."';";
        $ar = $cn->query($query)->fetch_assoc();
        $idr = 0;
        if (is_array($ar) && sizeof($ar) > 0){
                $idr  = $ar['idlink'];
        }
        logError($cn, $query);
        return $idr;
}

function db_getIDUserByHash($cn, $hash){
        $query = "SELECT iduser FROM  `users` WHERE user_hash='".mysqli_real_escape_string($cn,$hash)."';";
        $ar = $cn->query($query)->fetch_assoc();
        $iduser = 0;
        if (is_array($ar) && sizeof($ar) > 0){
                $iduser  = $ar['iduser'];
        }
        logError($cn, $query);
        return $iduser;
}


function db_getPreparedObjectReportTreetoUser_Search($cn, $user_ids, $idlanguage, $skbranch = NULL){
        $resarr = array();
        $useinsearch = array();

		$query = "SET SQL_BIG_SELECTS=1;";
        db_runQuery($cn, $query);
		$query = "DROP TEMPORARY TABLE IF EXISTS `inrepsearch_temp` ;";
		db_runQuery($cn, $query);
		$query = " CREATE TEMPORARY TABLE inrepsearch_temp (
					 `idobject` BIGINT NOT NULL,
					 `idreport` INT NOT NULL,
					 `parent_idobject` BIGINT NOT NULL,
					 `parent_idreport` INT NOT NULL,
					 `o_ileft` BIGINT NOT NULL,
					 `o_iright` BIGINT NOT NULL,
					 `o_icon` varchar(800) NOT NULL,
					 `rft_columnname_text` varchar(2500) NOT NULL,
					 PRIMARY KEY (`idobject`, `idreport`)					
					)ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;
				";
                db_runQuery($cn, $query);
                
        $query  = "CALL load_objecttree_search(".($skbranch == NULL ? "NULL": $skbranch).");";
        $query .= "INSERT INTO inrepsearch_temp SELECT obj.idobject, 0, 0, 0, obj.o_ileft, obj.o_iright, obj.o_icon, IFNULL(res.rft_columnname_text, obj.o_guid) as rft_columnname_text 
                 FROM objects as obj
                 INNER JOIN o_temp as ot ON obj.idobject = ot.idobject
                 LEFT JOIN resourcefromtables res ON obj.idobject = res.idrecord AND res.idlanguage = ".$idlanguage." AND res.idtable = 1
                WHERE obj.parent_idobject = (SELECT idobject 
                                               FROM objects 
                                              WHERE parent_idobject = 0 
                                             LIMIT 1 )
                ORDER BY obj.idobject;";
        $query .= "SELECT * FROM inrepsearch_temp;";

        /* execute multi query */
        if ($cn->multi_query($query)) {
                try {
                        do {
                                $result = $cn->store_result();
                        } while ($cn->more_results() && $cn->next_result());                        

                        if ($result) {
                                $row = null;
                                while ($row = $result->fetch_assoc()) {
                                        array_push($useinsearch, $row);
                                }
                                $result->close();
                        }
                } catch (Exception $e) {
                }
        }
        $size = (is_array($useinsearch) ?  sizeof($useinsearch) : 0 );
        $query = "";
        for($i=0; $i<$size; $i++){
                if ($i > 0) {
                        $query .= " UNION ";

                }
                $query .= "SELECT DISTINCT ".$useinsearch[$i]['idobject'].", orr.idreport, ".$useinsearch[$i]['idobject'].",0,0,1,'1.svg', res.rft_columnname_text
                                     FROM `objectsreports` as orr LEFT JOIN `objects` as obj ON orr.idobject = obj.idobject, resourcefromtables res
                                     WHERE obj.o_iright between ".$useinsearch[$i]['o_ileft']." AND ".$useinsearch[$i]['o_iright']."
                                       AND orr.idreport = res.idrecord
                                       AND orr.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                                       AND res.idlanguage = ".$idlanguage."
                   AND res.idtable = 2";
        }

        if (strlen($query) > 0 ){
                $query = "INSERT IGNORE INTO `inrepsearch_temp` ".$query.";";
                db_runQuery($cn, $query);
        }

        $query = "";
        if ( getSetting('portal','useRights', 0) == 1 ) {
                $ids = implode(",", $user_ids);
                //build indexed temp rigthmap
                $query = "DROP TEMPORARY TABLE IF EXISTS `rightsmap_temp` ;";
                db_runQuery($cn, $query);
                $query = " CREATE TEMPORARY TABLE rightsmap_temp (
                                        `idreport` BIGINT NOT NULL,
                                         PRIMARY KEY (`idreport`)
                                        )ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;
                                ";
                db_runQuery($cn, $query);
                   $query = " ALTER TABLE `rightsmap_temp` ADD INDEX ( `idreport` ) ;";
                db_runQuery($cn, $query);


                //build indexed temp rigthmap to user guids
                $query = "DROP TEMPORARY TABLE IF EXISTS `defaultsright` ;";
                db_runQuery($cn, $query);
                $query = " CREATE TEMPORARY TABLE defaultsright (
                                        `idreport` BIGINT NOT NULL,
                                         PRIMARY KEY (`idreport`)
                                        )ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;
                                ";
                db_runQuery($cn, $query);
                   $query = " ALTER TABLE `defaultsright` ADD INDEX ( `idreport` ) ;";
                db_runQuery($cn, $query);

                $query = "INSERT IGNORE INTO `defaultsright` SELECT DISTINCT orts.idreport FROM `objectsreports` as orts
                        LEFT JOIN `objects` as o ON orts.`idobject` = o.`idobject`
                        LEFT JOIN `users` as u ON u.`user_guid` = o.`o_guid`
                        WHERE u.iduser in (".$ids.") AND orts.idreport NOT IN (SELECT rr.idreport FROM  `rightsreports` as rr WHERE rr.`iduser` IN (".$ids.") AND rr.deny_allow = 0);";
                db_runQuery($cn, $query);

            $query = "INSERT IGNORE INTO `rightsmap_temp` SELECT rr.`idreport` FROM `rightsreports` as rr WHERE rr.`iduser` IN (".$ids.") AND rr.`deny_allow` = 0 ;";
                db_runQuery($cn, $query);
                $query = "DELETE FROM inrepsearch_temp WHERE inrepsearch_temp.`idreport` IN ( SELECT `idreport` FROM `rightsmap_temp`  );";
                db_runQuery($cn, $query);

                $q_st = "DELETE FROM `rightsmap_temp`;";
                db_runQuery($cn, $q_st);
            $query = "INSERT IGNORE INTO `rightsmap_temp` SELECT rr.`idreport` FROM `rightsreports` as rr WHERE rr.`iduser` IN (".$ids.") AND rr.`deny_allow` = 1 ;";
                db_runQuery($cn, $query);
                $query = "DELETE FROM inrepsearch_temp WHERE inrepsearch_temp.`idreport` <> 0 AND inrepsearch_temp.`idreport` NOT IN ( SELECT `idreport` FROM `rightsmap_temp` UNION SELECT `idreport` FROM `defaultsright`);";
                db_runQuery($cn, $query);

                $query = "DELETE FROM `rightsmap_temp`;";
                db_runQuery($cn, $query);
            $query = "INSERT IGNORE INTO `rightsmap_temp` SELECT rr.`idreport` FROM `rightsreports` as rr WHERE rr.`iduser` IN (".$ids.") AND rr.`deny_allow` = 2 ;";
                db_runQuery($cn, $query);
                $query = "DELETE FROM inrepsearch_temp WHERE inrepsearch_temp.`idreport` <> 0 AND inrepsearch_temp.`idreport` NOT IN ( SELECT `idreport` FROM `rightsmap_temp` UNION SELECT `idreport` FROM `defaultsright`);";
                db_runQuery($cn, $query);
        }

        $resarr = array();
        $query = "SELECT DISTINCT idobject, idreport, parent_idobject , parent_idreport, o_ileft, o_iright, o_icon, rft_columnname_text as r_name FROM inrepsearch_temp
                        order by idreport, o_ileft, rft_columnname_text;";
        $result = $cn->query($query);
        if ($result) {
                $row = null;
                while ($row = $result->fetch_assoc()) {
                       array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}


function db_isObjectHideInTree($cn, $idobject)
{
    $resarr = array();
	$query = "SELECT DISTINCT 1 FROM objects as obj WHERE obj.idobject=".$idobject." AND `o_hidefortree` = 1;";
    $ar = $cn->query($query)->fetch_assoc();
    logError($cn, $query);
    if (is_array($ar) && sizeof($ar) > 0){
 		return true;
 	} else {
  		return false;
  	}
}

function db_getPreparedObjectTreetoUser($cn, $idobject, $user_ids, $idlanguage, $skbranch = NULL)
{
        $resarr = array();
        if (db_isObjectHideInTree($cn, $idobject)){
        	return $resarr;
        }

        $query = "SET SQL_BIG_SELECTS=1;";
        if ( getSetting('portal','useRights', 0) == 1 ) {
                $query  .= "CALL load_objecttree( ".$idobject." , 1, '".implode(",", $user_ids)."', ".($skbranch == NULL ? "NULL": $skbranch)." );";
        } else {
                $query  .= "CALL load_objecttree( ".$idobject." , 0, '', ".($skbranch == NULL ? "NULL": $skbranch)." );";
        }

        $query .= "SELECT * FROM  (
                (SELECT IFNULL(res.rft_columnname_text, obj.o_guid) as o_name, ot.idobject, ot.parent_idobject, obj.o_ileft, obj.o_iright, obj.o_depth, obj.o_guid, obj.o_icon, obj.o_datechange, obj.o_hasreports, obj.o_useinsearch, obj.src_guid 
                        FROM objects as obj INNER JOIN o_temp as ot ON obj.idobject = ot.idobject
                        LEFT JOIN  resourcefromtables res ON obj.idobject = res.idrecord AND obj.skbranch = res.skbranch 
                        WHERE res.idlanguage = ".$idlanguage."
                          AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                          AND res.idtable = 1 )
                UNION
                (SELECT IFNULL(res.rft_columnname_text, obj.o_guid) as o_name, ottr.idpobject, ottr.parent_idobject, obj.o_ileft, obj.o_iright, obj.o_depth, obj.o_guid, obj.o_icon, obj.o_datechange, obj.o_hasreports, obj.o_useinsearch, obj.src_guid  
                        FROM objects as obj INNER JOIN o_temp_tree as ottr ON obj.idobject = ottr.idpobject 
                        LEFT JOIN resourcefromtables res ON obj.idobject = res.idrecord  AND obj.skbranch = res.skbranch 
                        AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                        WHERE res.idlanguage = ".$idlanguage."
                          AND res.idtable = 1 )
                ) q
                ORDER BY idobject;";
                
        /* execute multi query */
        if ($cn->multi_query($query)) {
                $cn->next_result();
                $cn->next_result();
                $result = $cn->store_result();
                if ($result) {
                        $row = null;
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_updateObjectNotificateUser($cn, $userid)
{
        $query = "UPDATE objectlinks SET ol_notificate = 1 WHERE ol_type = 4 AND iduser = ".$userid."; ";
        return db_runQuery($cn,$query);
}

function db_getObjectToNotificateUser($cn, $userid, $skbranch = NULL)
{
        $useridlanguageinterface = db_getUseridlanguageContent($cn, $userid);
        $username = db_getUserNameByID($cn, $userid, $useridlanguageinterface);
        $resarr = array();
        $query = "SELECT res.rft_columnname_text as o_name, res2.rft_columnname_text as statever, obj.idobject, obj.o_ver,  DATE_FORMAT(obj.o_datever,'%d.%m.%Y %H:%i') as o_datever, obj.o_guid, usr.user_email, usr.user_idlanguageinterface, '".$username."' as user_name, lng.lang_short
                        FROM objects as obj, objectlinks ol, users usr, resourcefromtables res, resourcefromtables res2, languages lng
                        WHERE obj.idobject = ol.idobject
                          AND ol.ol_type = 4
                          AND ol.iduser = usr.iduser
                          AND obj.idobject = res.idrecord
                          AND obj.o_statever = res2.idrecord
                          AND res.idlanguage = usr.user_idlanguageinterface
                          AND res2.idlanguage = usr.user_idlanguageinterface
                          AND lng.idlanguage = usr.user_idlanguageinterface
                          AND res.idtable = 1
                          AND res2.idtable = 5
                          AND ol.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                          AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                          AND res.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                          AND IFNULL(ol.ol_notificate, 0) = 0
                          AND ol.iduser = ".$userid.";";
        $result = $cn->query($query);
        if ($result) {
                $row = null;
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getObjectPathtoUser($cn, $idobject, $user_ids, $idlanguage, $skbranch = NULL)
{
        $resarr = array();
        $query = "SET SQL_BIG_SELECTS=1;";
        if ( getSetting('portal','useRights', 0) == 1 ) {
                $query  .= "CALL load_objectpath( ".$idobject." , 1, '".implode(",", $user_ids)."', ".($skbranch == NULL ? "NULL": $skbranch)." );";
        } else {
                $query  .= "CALL load_objectpath( ".$idobject." , 0, '', ".($skbranch == NULL ? "NULL": $skbranch)." );";
        }

        $query .= "SELECT res.rft_columnname_text as o_name, obj.idobject, obj.o_guid, obj.o_icon
                                FROM objects as obj, o_temp_tree as ottr, resourcefromtables res
                        WHERE obj.idobject = ottr.idpobject
                          AND obj.idobject = res.idrecord
                          AND res.idlanguage = ".$idlanguage."
                          AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)." 
                          AND res.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)." 
                          AND res.idtable = 1
                ORDER BY obj.idobject ;";

        if ($cn->multi_query($query)) {
                $cn->next_result();
                $cn->next_result();
                $result = $cn->store_result();
                if ($result) {
                        $row = null;
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;
}



function db_getPreparedObjectTreetoAdmin($cn, $idobject, $idlanguage)
{
        $resarr = array();
        $query = "SET SQL_BIG_SELECTS=1;";
        $query .= "CALL load_objecttree( ".$idobject." , 0, '' );";
        $query .= "SELECT * FROM (
                (SELECT res.rft_columnname_text as o_name, obj.idobject, obj.parent_idobject, obj.o_ileft, obj.o_iright, obj.o_depth, obj.o_guid, obj.o_icon, obj.o_datechange, obj.o_hasreports, obj.o_useinsearch, obj.src_guid  FROM objects as obj, o_temp as ot, resourcefromtables res
                        WHERE obj.idobject = ot.idobject
                          AND obj.idobject = res.idrecord
                          AND res.idlanguage = ".$idlanguage."
                          AND res.idtable = 1 )
                UNION
                (SELECT res.rft_columnname_text as o_name, obj.idobject, obj.parent_idobject, obj.o_ileft, obj.o_iright, obj.o_depth, obj.o_guid, obj.o_icon, obj.o_datechange, obj.o_hasreports, obj.o_useinsearch, obj.src_guid  FROM objects as obj, o_temp_tree as ottr, resourcefromtables res
                        WHERE obj.idobject = ottr.idpobject
                          AND obj.idobject = res.idrecord
                          AND res.idlanguage = ".$idlanguage."
                          AND res.idtable = 1 )
                ) Q ORDER BY idobject;
                ";
        /* execute multi query */
        if ($cn->multi_query($query)) {
                $cn->next_result();
                $cn->next_result();
                $result = $cn->store_result();
                if ($result) {
                        $row = null;
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;
}


function db_getRootObjectTreeFromPreparetTree($cn, $skbranch = NULL)
{
        $resarr = array();
        if ( getSetting('portal','useRights', 0) == 1 ) {
	        $query = "SELECT 1 FROM information_schema.tables WHERE table_schema = '".GetSetting('database','database','')."' AND table_name = 'o_temp' LIMIT 1;";
	        $result = $cn->query($query);
	        if ($result->fetch_assoc()) {
		        $query = "SELECT idobject FROM o_temp;";
		        $result = $cn->query($query);
		        if ($result) {
		                $row = null;
		                while ($row = $result->fetch_assoc()) {
		                        array_push($resarr, $row["idobject"]);
		                }
		        }
		        logError($cn, $query);
	        }
	        logError($cn, $query);
		} else {
	        $query = "SELECT idobject FROM objects WHERE parent_idobject = (SELECT idobject FROM objects WHERE parent_idobject = 0) AND o_hidefortree = 0 
                                                         AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)." ;";
	        $result = $cn->query($query);
	        if ($result) {
	                $row = null;
	                while ($row = $result->fetch_assoc()) {
	                        array_push($resarr, $row["idobject"]);
	                }
	        }
	        logError($cn, $query);
        }
        return $resarr;
}

function db_setTimeZone($cn){
        if (isset($_SESSION['Timezone']) && $_SESSION['Timezone'] !== NULL) {
                $query = '      SET @@session.time_zone = "'.$_SESSION['Timezone'].'";';
                return db_runQuery($cn, $query);
        }
}

function db_getForumMessage($cn, $idforummessage, $user_idlanguagecontent){
        db_setTimeZone($cn);
        $query = "      SELECT DISTINCT fm.idforummessage, fm.parent_idforummessage, fm.o_guid, fm.iduser, fm.frmm_ileft, fm.frmm_iright, fm.frmm_ischanged, fm.frmm_depth, fm.frmm_body,  
                                CONCAT(DATE_FORMAT(from_unixtime(fm.frmm_datechange_tmst),'%d.%m.%Y %H:%i'),' UTC ', 
                                TIME_FORMAT(timediff(now(),convert_tz(now(),@@session.time_zone,'+00:00')), '%H:%i')) as  frmm_datechange,
                                rft_columnname_text as user_name, usr.user_email 
                                FROM `forummessages` as fm
                                INNER JOIN `users` as usr ON fm.iduser=usr.iduser
                                LEFT JOIN `resourcefromtables` AS res ON res.idtable = 6 AND res.idrecord = usr.iduser AND res.idlanguage = ".$user_idlanguagecontent." 
                                WHERE fm.idforummessage = '".mysqli_real_escape_string($cn,$idforummessage)."';";
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getForumMessages($cn, $oguid, $iduser, $skbranch = NULL){
        $resarr = array();

        $sspikey = getSessionSSKEY();
        $user_idlanguagecontent =  $_SESSION['user_idlanguagecontent'];
        db_setTimeZone($cn);
        $query = "      SELECT DISTINCT files.idfile, files.file_name, fm.idforummessage, fm.parent_idforummessage, fm.o_guid, fm.iduser, fm.frmm_ileft, fm.frmm_iright, fm.frmm_ischanged, fm.frmm_depth, fm.frmm_body, from_unixtime(fm.frmm_datechange_tmst) as  frmm_datechange,
                                 rft_columnname_text as user_name, usr.user_email ,
                                 IF(frmsw.iduser IS NOT NULL, 1 , 0) as isnewmessage
                                FROM `forummessages` as fm
                                LEFT OUTER JOIN `files` as files ON fm.idforummessage = files.idforummessage
                                LEFT JOIN `forummessagessaw` as frmsw ON frmsw.iduser = ".$iduser." AND fm.iduser <> frmsw.iduser AND fm.skbranch = frmsw.skbranch AND fm.o_guid = frmsw.o_guid AND frmsw.`frmsw_sessionhash` = '".$sspikey."' AND from_unixtime(frmsw.frmsw_datesaw_tmst) <= from_unixtime(fm.frmm_datechange_tmst)
                                INNER JOIN `users` as usr ON fm.iduser=usr.iduser
                                LEFT JOIN `resourcefromtables` AS res ON res.idtable = 6 AND res.idrecord = usr.iduser AND res.idlanguage = ".$user_idlanguagecontent." 
                                WHERE fm.o_guid = '".mysqli_real_escape_string($cn,$oguid)."'  
                                AND fm.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."  
                                AND fm.frmm_isdelete = 0 ORDER BY frmm_ileft;";
        $result = $cn->query($query);
        if ($result) {
                $row = null;
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getForumMessagesFile($cn, $idforummessage, $idfile){
        $resarr = array();
        $query = "SELECT file.* FROM `files` as file WHERE file.idforummessage = ".mysqli_real_escape_string($cn,$idforummessage)." AND file.idfile = ".mysqli_real_escape_string($cn,$idfile)." ;";
        $result = $cn->query($query);
        if ($result) {
                $row = null;
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getForumMessageRoot($cn, $idforummessage){
        $root = 0;
        $query = "SELECT get_forummessageroot(".mysqli_real_escape_string($cn,$idforummessage).") as res;";
	$result = $cn->query($query);
	if ($result) {
	        $ar = $result->fetch_assoc();
	        if (is_array($ar) && sizeof($ar) > 0){
	                $root  = $ar['res'];
                }
        }
        logError($cn, $query);
        return $root;
}

function db_getLanguagesTranslate($cn, $idlanguage){
        $resarr = array();
        $query = "SELECT lng.idlanguage, res.rft_columnname_text as lang_name, lng.lang_short FROM `languages` lng, resourcefromtables res
                WHERE lng.idlanguage = res.idrecord
                  AND res.idtable = 0
                  AND res.idlanguage = ".$idlanguage.";";
        $result = $cn->query($query);
        if ($result) {
                $row = null;
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getLanguagesNative($cn, $allowContentLanguages = null){
        $resarr = array();
        if ($allowContentLanguages == null) {
                $query = "SELECT idlanguage, native_name as lang_name, lang_short FROM `languages` WHERE allow = 1 AND length(lang_short) > 0;";
        } else {
                
                $condition = "''";
                foreach (explode(",", $allowContentLanguages) as $value) {
                        if (mb_strlen(trim($value)) > 1) {
                                $condition .= ",'".trim($value)."'"; 
                        }
                }
                if ($value) {unset($value);}
                $query = "SELECT idlanguage, native_name as lang_name, lang_short FROM `languages` WHERE LOWER(lang_short) in (".strtolower($condition).") AND length(lang_short) > 0;";
        }
        $result = $cn->query($query);
        if ($result) {
                $row = null;
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getLanguages($cn){
        $resarr = array();
        $query = "SELECT idlanguage, lang_name, lang_short FROM `languages` WHERE allow = 1;";

        $result = $cn->query($query);
        if ($result) {
                $row = null;
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getLanguageByShortName($cn, $lang_short, $allow = 1, $default = ""){
        $query = "SELECT idlanguage FROM `languages` WHERE lang_short like '".mysqli_real_escape_string($cn,$lang_short)."' AND (allow = ".$allow." OR -1 = ".$allow.");";
        $res = db_runQueryAndReturnArray($cn, $query);
        if (is_array($res) && sizeof($res) > 0){
                return $res[0]["idlanguage"];
        } elseif (strlen($default) == 0)  {
	        return db_getDefaultLangShortName($cn);
        } else {
                return $default;
        }
}

function db_getCheckLanguageByShortName($cn, $lang_short){
        $query = "SELECT idlanguage, lang_name, lang_short FROM `languages` WHERE lang_short like '".mysqli_real_escape_string($cn,$lang_short)."' AND allow = 1;";
        return (sizeof(db_runQueryAndReturnArray($cn, $query)) > 0);
}

function db_getCheckInterfaceLanguageByShortName($cn, $iduser,  $lang_short){
        $query = "SELECT 1 FROM `languages` LEFT JOIN `users` ON  `users`.`user_idlanguageinterface` = `languages`.`idlanguage` WHERE `languages`.allow = 1 AND `iduser` = ".mysqli_real_escape_string($cn,$iduser)." AND  `lang_short` = '".mysqli_real_escape_string($cn,$lang_short)."' ;";
        return (sizeof(db_runQueryAndReturnArray($cn, $query)) > 0);
}


function db_getLanguageShortByID($cn, $idlanguage, $isInterface = false){
	if (strlen($idlanguage) == 0 || !is_numeric($idlanguage)) {
		return db_getDefaultLangShortName($cn);
        } else {
		$resarr = array();			
	        $query = "SELECT lang_short FROM `languages` WHERE idlanguage = ".$idlanguage.($isInterface?'':" AND allow = 1;");
	        $text = db_getDefaultLangShortName($cn);
	        $result = $cn->query($query); 
	        if ($result) {
	                $ar = $result->fetch_assoc();
	                if (is_array($ar) && sizeof($ar) > 0){
	                        $text  = $ar['lang_short'];
	                }
	        }
	        logError($cn, $query);
	        return $text;
        }
}


function db_getReportLanguageID($cn, $idreport){
	if (strlen($idreport) == 0) {
		return null;
        } else {
		$resarr = array();
	        $query = "SELECT idlanguage FROM `reports` WHERE idreport = ".$idreport.";";
                $result = $cn->query($query); 
	        if ($result) {
	                $ar = $result->fetch_assoc();
	                if (is_array($ar) && sizeof($ar) > 0){
	                        return $ar['idlanguage'];
	                }
	        } else {
                        return null;
                }
	        logError($cn, $query);
        }
}



function db_getRecourceFromTable($cn, $idrecord, $idtable, $idlanguage, $skbranch = NULL ){
        $query = "SELECT rft_columnname_text 
                   FROM `resourcefromtables` 
                  WHERE idrecord = ".$idrecord." 
                    AND idtable = ".$idtable." 
                    AND idlanguage = ".$idlanguage."
                    ".($skbranch == NULL ? "" : "AND skbranch = ".$skbranch).";";
        $ar = $cn->query($query)->fetch_assoc();
        $text = "";
        if (is_array($ar) && sizeof($ar) > 0){
                $text  = $ar['rft_columnname_text'];
        }
        logError($cn, $query);
        return $text;
}

function db_addForumMessages($cn, $idforummessageparetn, $oguid, $iduser, $frmm_body, $skbranch = NULL ){
        if ( strlen($frmm_body) > 0 ) {
                $query = "SELECT 1 FROM `forummessages` LIMIT 1 ;";
                if (sizeof(db_runQueryAndReturnArray($cn, $query)) == 0) {
                        $query = "INSERT INTO `forummessages` (`idforummessage`, `parent_idforummessage`, `o_guid`, `iduser`, `frmm_ileft`, `frmm_iright`, `frmm_depth`, `frmm_body`, `frmm_isdelete`, `frmm_ischanged`, `frmm_datechange_tmst`, `skbranch`) 
                                        VALUES ('0', '0', '00000000-0000-0000-0000-000000000000', '0', '1', '2', '0', 'root', 0, 0, UNIX_TIMESTAMP(NOW()), 0);	";
                        $cn->query($query);
                        logError($cn, $query);
                        $query = "UPDATE `forummessages` SET `idforummessage` = 0;";
                        $cn->query($query);
                        logError($cn, $query);
                }
                $query = "call add_forummessages(".(!isset($idforummessageparetn) ? 0 : $idforummessageparetn).", '".mysqli_real_escape_string($cn, $oguid)."', ".$iduser.", '".mysqli_real_escape_string($cn,$frmm_body)."', ".($skbranch == NULL ? "NULL" : $skbranch).");";
                $cn->query($query);
                logError($cn, $query);
                $query = "SELECT max(idforummessage) as max_id FROM `forummessages` WHERE iduser = ".$iduser.";";
                $ar = $cn->query($query)->fetch_assoc();
                logError($cn, $query);
                $text = "";
                if (is_array($ar) && sizeof($ar) > 0){
                	$text  = $ar['max_id'];
                }
                return $text;
        }
}

function db_editForumMessages($cn, $idforummessage, $iduser, $frmm_body){
        if ( strlen($frmm_body) > 0 ) {
                $query = 'SET @@session.time_zone = "+00:00";';
                $cn->query($query);
                $query = "UPDATE `forummessages` SET `frmm_body` = '".mysqli_real_escape_string($cn,$frmm_body)."',  `frmm_datechange_tmst` = UNIX_TIMESTAMP(NOW()) , `frmm_ischanged` = 1 WHERE `iduser` =  ".$iduser." AND `idforummessage` = ".$idforummessage.";";
                $cn->query($query);
                logError($cn, $query);
        }
}

function db_resetForumMessagesSawDate($cn, $oguid, $iduser, $skbranch = NULL ){
		$sspikey = getSessionSSKEY();
                $query = 'SET @@session.time_zone = "+00:00";';
                $cn->query($query);
		$query = "UPDATE `forummessagessaw` SET `frmsw_sessionhash` = '".$sspikey."', `frmsw_datesaw_tmst` = UNIX_TIMESTAMP(NOW()), `frmsw_datelastsaw_tmst` = UNIX_TIMESTAMP(NOW()) 
                           WHERE `iduser` = ".$iduser." 
                           AND `skbranch` = ".($skbranch == NULL ? "NULL" : $skbranch)."
                           AND `frmsw_sessionhash` = '".$sspikey."';";
		return db_runQuery($cn, $query);
}


function db_getNewForumMessages($cn, $oguid, $iduser, $skbranch = NULL, $limit = 0, $setSaw = TRUE){
        $sspikey = getSessionSSKEY();
        if ($setSaw) {
                $query = 'SET @@session.time_zone = "+00:00";';
                $cn->query($query);
                $query = "SELECT 1 FROM `forummessagessaw` WHERE `iduser` = ".$iduser." AND `o_guid` = '".mysqli_real_escape_string($cn,$oguid)."' AND `skbranch` = ".($skbranch == NULL ? "NULL" : $skbranch)." LIMIT 1;" ;
                $ar = db_runQueryAndReturnArray($cn, $query);
                if (sizeof($ar) == 0){
                        $query = "INSERT INTO `forummessagessaw` (`iduser`, `o_guid`, `skbranch`,  `frmsw_sessionhash`,  `frmsw_datesaw_tmst`,  `frmsw_datelastsaw_tmst` ) VALUES (".$iduser.", '".mysqli_real_escape_string($cn,$oguid)."', ".($skbranch == NULL ? "0" : $skbranch).", '".$sspikey."', UNIX_TIMESTAMP(NOW()), UNIX_TIMESTAMP(NOW()));";
                        db_runQuery($cn, $query);
                } ELSE {
                        $query = "UPDATE `forummessagessaw` SET `frmsw_sessionhash` = '".$sspikey."', `frmsw_datesaw_tmst` = `frmsw_datelastsaw_tmst`, `frmsw_datelastsaw_tmst` = UNIX_TIMESTAMP(NOW()) WHERE `iduser` = ".$iduser." AND `skbranch` = ".($skbranch == NULL ? "NULL" : $skbranch)." AND `o_guid` = '".mysqli_real_escape_string($cn,$oguid)."' AND `frmsw_sessionhash` <> '".$sspikey."';";
                        db_runQuery($cn, $query);
                        $query = "UPDATE `forummessagessaw` SET `frmsw_datelastsaw_tmst` = UNIX_TIMESTAMP(NOW()) WHERE `iduser` = ".$iduser." AND `o_guid` = '".mysqli_real_escape_string($cn,$oguid)."'  AND `skbranch` = ".($skbranch == NULL ? "NULL" : $skbranch)." AND `frmsw_sessionhash` = '".$sspikey."';";
                        db_runQuery($cn, $query);
                }
        }

        $query = "  SELECT DISTINCT fm.idforummessage
                        FROM `forummessages` as fm
                        LEFT JOIN `forummessagessaw` as frmsw ON frmsw.iduser = ".$iduser." AND fm.iduser <> frmsw.iduser AND fm.skbranch = frmsw.skbranch AND fm.o_guid = frmsw.o_guid AND frmsw.`frmsw_sessionhash` = '".$sspikey."' AND from_unixtime(frmsw.frmsw_datesaw_tmst) <= from_unixtime(fm.frmm_datechange_tmst)
                        WHERE IF(frmsw.iduser IS NOT NULL, 1 , 0) = 1 AND fm.o_guid = '".mysqli_real_escape_string($cn,$oguid)."'  
                        AND fm.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."  
                        AND fm.frmm_isdelete = 0 ORDER BY idforummessage ".($limit > 0 ? " LIMIT ".$limit : "").";"; 
        return db_runQueryAndReturnArray($cn, $query);
}


function db_checkLanguagesSettings($cn){
        $query = "SELECT 1 FROM `languages` WHERE allow = 1 AND main = 1 LIMIT 1;" ;
        $f = $cn->query($query);
        if ($f) {
                $ar = $f->fetch_assoc();
                logError($cn, $query);
                if (is_array($ar) && sizeof($ar) > 0){
                        return true;
                } else {
                        if (getSetting('portal','useRights',0) == 0)  {
                                // 65843
                                unset($_SESSION['user_idlanguageinterface']);
                                return true;
                        }
                        return false;
                }
        } else {
                return false;
        }
}

function db_getLanguageID($cn, $lang_short, $allow = 1){
        $query = "SELECT idlanguage FROM languages WHERE lang_short like '".mysqli_real_escape_string($cn,$lang_short)."' AND (allow = ".$allow." OR ".$allow." = -1);" ;
        $f = $cn->query($query);
        if ($f) {
                $ar = $f->fetch_assoc();
                logError($cn, $query);
                if (is_array($ar) && sizeof($ar) > 0){
                        return $ar['idlanguage'];
                } else {
                        return null;
                }
        } else {
                return null;
        }
}

function db_getDBVersion($cn){
        $query = "SELECT `version` FROM `version` ;" ;
		$res = $cn->query($query);
		if (mysqli_errno($cn)==1146){//STU: Table version not exist
			$query = 'CREATE TABLE IF NOT EXISTS  `version` ( `version` int(11) NOT NULL, PRIMARY KEY (`version`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;';
			$cn->query($query);
			$query = "INSERT INTO `version` (`version`) VALUES ('0');";
			$cn->query($query);
			return 0;
		}
        $ar = $res->fetch_assoc();
		
        logError($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar['version'];
        } else {
                return 0;
        }
}
function db_getBranchesRootsForTreetoUser($cn, $user_ids, $idlanguage, $onlyMainBranch = FALSE)
{
        $resarr = array();
        db_createUsersIDsTempTable($cn, $user_ids);       
        if ( getSetting('portal','useRights',0) == 1 && !$onlyMainBranch) {
                $query  = "
                        SELECT DISTINCT bres.rft_columnname_text, r1.skbranch 
                        FROM userids_t ur 
                        INNER JOIN rightsobjects as r1 ON ur.iduser = r1.iduser AND r1.deny_allow = 1
                        INNER JOIN branches ON r1.skbranch = branches.skbranch AND branches.iscomplete = 1
                        INNER JOIN rightsobjects as r2 ON ur.iduser = r1.iduser AND r1.idobject = r2.idobject and r2.deny_allow = 2
                        INNER JOIN resourcefromtables as bres ON bres.idtable = 7 AND bres.idlanguage = ".$idlanguage." AND bres.idrecord = r1.skbranch 
                        ORDER BY bres.rft_columnname_text;";
        } else {
                $query  = "
                        SELECT DISTINCT bres.rft_columnname_text, r1.skbranch FROM branches as r1 
                        INNER JOIN resourcefromtables as bres ON bres.idtable = 7 AND bres.idlanguage = ".$idlanguage." AND bres.idrecord = r1.skbranch 
                        WHERE r1.ismain = 1 ORDER BY bres.rft_columnname_text;";
        }
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getBranchesForProject($cn, $idlanguage, $skproject)
{
        $query  = "
                SELECT DISTINCT bres.rft_columnname_text, p.skbranch FROM `pollingobjects` p
                INNER JOIN branches ON p.skbranch = branches.skbranch AND branches.iscomplete = 1
                INNER JOIN resourcefromtables as bres ON bres.idtable = 7 AND bres.idlanguage = ".$idlanguage." AND bres.idrecord = p.skbranch 
                WHERE p.skproject = ".$skproject." ORDER BY bres.rft_columnname_text;";
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getObjectTreeMaptoUser($cn, $user_ids, $skbranch = NULL)
{
        $resarr = array();
        $query = "SET SQL_BIG_SELECTS=1;";
        $query .= "CALL createtree( '".implode(",", $user_ids)."', ".($skbranch == NULL ? "NULL": $skbranch)." ); ";
        $query .= "SELECT idobject, parent_idobject FROM `chkrt_o_temp` order by parent_idobject; ";
        if ($cn->multi_query($query)) {
                $cn->next_result();
                $cn->next_result();
                $result = $cn->store_result();
                if ($result) {
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getObjectTreetoUser($cn, $parent_idobject, $user_ids, $idlanguage, $skbranch = NULL)
{
        $resarr = array();
        $parent_idobject = ( mb_strtolower($parent_idobject) == 'source' ? 0 : $parent_idobject ) ;
        $query = "SET SQL_BIG_SELECTS=1;";
        if ( getSetting('portal','useRights',0) == 1) {
                $query  .= "CALL load_objects( ".$parent_idobject." , 1, '".implode(",", $user_ids)."', ".($skbranch == NULL ? "NULL": $skbranch)." );";
        } else {
                $query  .= "CALL load_objects( ".$parent_idobject." , 0, '', ".($skbranch == NULL ? "NULL": $skbranch)." );";
        }
        $query .= "SELECT IFNULL(res.rft_columnname_text, obj.o_guid) as o_name, ot.idobject, ot.parent_idobject, obj.o_ileft, obj.o_iright, obj.o_depth, obj.o_guid, obj.o_icon, obj.o_datechange, obj.o_hasreports, obj.o_useinsearch, obj.src_guid
                FROM objects as obj
                INNER JOIN o_temp as ot ON obj.idobject = ot.idobject 
                LEFT JOIN resourcefromtables res ON obj.idobject = res.idrecord AND obj.skbranch = res.skbranch 
				WHERE res.idtable = 1 ".($idlanguage==NULL?'':"AND res.idlanguage = ".$idlanguage." ").
                "AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
				ORDER BY obj.idobject;";
        if ($cn->multi_query($query)) {
                $cn->next_result();
                $cn->next_result();
                $result = $cn->store_result();
                if ($result) {
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;
}


function db_getObjectTreetoSearchtoUser($cn, $parent_idobject, $user_ids, $idlanguage, $skbranch = NULL)
{
        $resarr = array();
        $parent_idobject = ( mb_strtolower($parent_idobject) == 'source' ? 0 : $parent_idobject ) ;
        $query = "SET SQL_BIG_SELECTS=1;";
        if ( getSetting('portal','useRights',0) == 1) {
                $query  .= "CALL load_objects( ".$parent_idobject." , 1, '".implode(",", $user_ids)."', ".($skbranch == NULL ? "NULL": $skbranch)." );";
        } else {
                $query  .= "CALL load_objects( ".$parent_idobject." , 0, '', ".($skbranch == NULL ? "NULL": $skbranch)." );";
        }
        $query .= "SELECT IFNULL(res.rft_columnname_text, obj.o_guid) as o_name, ot.idobject, ot.parent_idobject, obj.o_ileft, obj.o_iright, obj.o_depth, obj.o_guid, obj.o_icon, obj.o_datechange, obj.o_hasreports, obj.o_useinsearch 
                   FROM objects as obj INNER JOIN  o_temp as ot ON obj.idobject = ot.idobject
                   LEFT JOIN resourcefromtables res ON obj.idobject = res.idrecord AND obj.skbranch = res.skbranch  
                  WHERE (obj.o_iright - obj.o_ileft) > 1
                  AND res.idtable = 1
                  AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                  AND res.idlanguage = ".$idlanguage."
                ORDER BY obj.idobject;";
        if ($cn->multi_query($query)) {
                $cn->next_result();
                $cn->next_result();
                $result = $cn->store_result();
                if ($result) {
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getObjectTreetoSearchForPollstoUser($cn, $parent_idobject, $user_ids, $idlanguage, $skbranch = NULL)
{
        //64024
        $resarr = array();
        $parent_idobject = ( mb_strtolower($parent_idobject) == 'source' ? 0 : $parent_idobject ) ;
        $query = "SET SQL_BIG_SELECTS=1;";
        if ( getSetting('portal','useRights',0) == 1) {
                $query  .= "CALL load_objects( ".$parent_idobject." , 1, '".implode(",", $user_ids)."', ".($skbranch == NULL ? "NULL": $skbranch)." );";
        } else {
                $query  .= "CALL load_objects( ".$parent_idobject." , 0, '', ".($skbranch == NULL ? "NULL": $skbranch)." );";
        }
        $query .= "SELECT IFNULL(res.rft_columnname_text, obj.o_guid) as o_name, ot.idobject, ot.parent_idobject, obj.o_ileft, obj.o_iright, obj.o_depth, obj.o_guid, obj.o_icon, obj.o_datechange, obj.o_hasreports, obj.o_useinsearch 
                   FROM objects as obj INNER JOIN  o_temp as ot ON obj.idobject = ot.idobject
                   LEFT JOIN resourcefromtables res ON obj.idobject = res.idrecord AND obj.skbranch = res.skbranch  
                  WHERE res.idtable = 1
                  AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                  AND res.idlanguage = ".$idlanguage."
                ORDER BY obj.idobject;";
        if ($cn->multi_query($query)) {
                $cn->next_result();
                $cn->next_result();
                $result = $cn->store_result();
                if ($result) {
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getServiceDataObjectTreetoAdmin($cn, $parent_idobject, $idlanguage)
{
        $parent_idobject = ( mb_strtolower($parent_idobject) == 'source' ? 0 : $parent_idobject ) ;
        $query = "SET SQL_BIG_SELECTS=1;";
        $query .= "CALL load_objectsAdmin( ".$parent_idobject." );";
        $query .= "SELECT res.rft_columnname_text as o_name, obj.idobject, obj.parent_idobject, obj.o_ileft, obj.o_iright, obj.o_depth, obj.o_guid, obj.o_icon, obj.o_datechange, obj.o_hasreports, obj.o_useinsearch FROM objects as obj, o_temp as ot, resourcefromtables res
                WHERE obj.idobject = ot.idobject
                  AND obj.idobject = res.idrecord
                  AND res.idtable = 1
                  AND res.idlanguage = ".$idlanguage."
                ORDER BY obj.idobject;";

        echo '[ ';
        $_cnt = 0;
        if ($cn->multi_query($query)) {
                $cn->next_result();
                $cn->next_result();
                $result = $cn->store_result();
                if ($result) {
                        while ($row = $result->fetch_assoc()) {
                                echo $_cnt == 0 ? '{' : ',{ ';
                                echo '"text": "'.str_replace('\\', "&frasr;", htmlentities($row['o_name'], ENT_QUOTES, 'UTF-8')).'",';
                                echo '"icon":  "'.getImgIcon($row['o_icon']).'",';
                                echo '"expanded": false, ';
                                echo '"name": "'.str_replace('"', "&quot;", $row['o_guid']).'",';
                                echo '"id": "'.$row['idobject'].'",';
                                if ($row['o_iright'] - $row['o_ileft'] > 1){
                                        echo '"hasChildren": true';
                                } else
                                        echo '"hasChildren": false';
                                echo '} ';
                                $_cnt = 1;
                        }
                        $result->close();
                }
        }
        unset($result);
        echo '] ';
        logError($cn, $query);
}


function db_getTreeDataObjectTreetoAdmin($cn, $parent_idobject, $idlanguage)
{
        $resarr = array();
        $parent_idobject = ( mb_strtolower($parent_idobject) == 'source' ? 0 : $parent_idobject ) ;
        $query = "SET SQL_BIG_SELECTS=1;";
        $query .= "CALL load_objectsAdmin( ".$parent_idobject." );";
        if ($parent_idobject == 0) {
                $query .= 'SELECT CONCAT("<b>",(SELECT bres.rft_columnname_text FROM resourcefromtables as bres WHERE bres.idtable = 7 AND bres.idlanguage = res.idlanguage AND bres.idrecord = obj.skbranch LIMIT 1),"</b>  -  ", res.rft_columnname_text)  as o_name';
        } else {
                $query .= "SELECT res.rft_columnname_text as o_name";
        }
        $query .= ", obj.idobject, obj.parent_idobject, obj.o_ileft, obj.o_iright, obj.o_depth, obj.o_guid, obj.o_icon, obj.o_datechange, obj.o_hasreports, obj.o_useinsearch FROM objects as obj, o_temp as ot, resourcefromtables res
                WHERE obj.idobject = ot.idobject
                  AND obj.idobject = res.idrecord
                  AND res.idtable = 1
                  AND res.idlanguage = ".$idlanguage."
                ORDER BY obj.idobject;";
        if ($cn->multi_query($query)) {
                $cn->next_result();
                $cn->next_result();
                $result = $cn->store_result();
                if ($result) {
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;
        unset($resarr);
}

// записать обращение к отчету которого нет
function db_addReportsUsersQuery($cn, $userid, $oguid, $rguid, $idlanguage = 'NULL', $skbranch = NULL)
{
        $query = "INSERT IGNORE INTO `reportsusersquery` (`o_guid` ,`r_guid` ,`iduser`, `idlanguage`, `skbranch` )  ";
        $query .= " SELECT '".$oguid."', 
                           '".$rguid."', 
                           ".$userid.", 
                           IF (EXISTS(SELECT 1 FROM reports WHERE ifnull(idlanguage,0) <> 0 AND r_guid = '".$rguid."' LIMIT 1), ".$idlanguage.", NULL),
                           ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
        db_runQuery($cn, $query);
}


// удалить из списка рассылки
function db_deleteReportsUsersQuery($cn, $userid, $oguid, $rguid, $idlanguage = 'NULL', $skbranch = NULL)
{
        $query = "DELETE FROM `reportsusersquery` WHERE ";
        $query .= "     `o_guid` = '".$oguid."' ";
        $query .= " AND `r_guid` = '".$rguid."' ";
        $query .= " AND `iduser` = ".$userid."";
        $query .= " AND ifnull(`idlanguage`,0) in ( ".$idlanguage.", 0)";
        $query .= " AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
        db_runQuery($cn, $query);
}

// удалить из списка рассылки
function db_isReportsUsersQuery($cn, $userid, $oguid, $rguid, $idlanguage = 'NULL', $skbranch = NULL){
        $query = "SELECT count(0) as `res` FROM `reportsusersquery` WHERE ";
        $query .= "     `o_guid` = '".$oguid."' ";
        $query .= " AND `r_guid` = '".$rguid."' ";
        $query .= " AND `iduser` = ".$userid."";
        $query .= " AND ifnull(`idlanguage`,0) in ( ".$idlanguage.", 0) ";
        $query .= " AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)." LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return ($ar['res'] > 0);
        } else {
                return false;
        }
}

// получить список емайлов для расслыки
function db_getReportsUsersQuery($cn, $oguid, $rguid, $skbranch = NULL){
        $resarr = array();
        $query = "SELECT rq.`iduser` , usr.`user_email` , usr.`user_idlanguageinterface`, usr.`user_idlanguagecontent`, lng.`lang_short` ";
        $query .= "        FROM `reportsusersquery` AS rq, `users` AS usr, `languages` AS lng ";
        $query .= "        WHERE rq.`iduser` = usr.`iduser` ";
        $query .= "        AND lng.`idlanguage` = usr.`user_idlanguageinterface` ";
        $query .= " AND rq.`o_guid` = '".$oguid."' ";
        $query .= " AND rq.`r_guid` = '".$rguid."' ";
        $query .= " AND rq.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";

        $result = $cn->query($query);
        if ($result) {
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}

// получить список емайлов для расслыки администраторам о старте портала
function db_getUniqueAdminEMails($cn){
        $resarr = array();
        $query = "SELECT DISTINCT rft_columnname_text as user_name, user_email, user_idlanguageinterface, user_idlanguagecontent, 
					IF(usr.`user_idlanguageinterface` = 0, 'default', lngi.`lang_short`) AS lang_short
                       FROM `users` AS usr INNER JOIN `languages` AS lngi ON IF(usr.`user_idlanguageinterface` = 0, 1, usr.`user_idlanguageinterface` = lngi.`idlanguage`)
						INNER JOIN `languages` AS lngc ON IF(usr.`user_idlanguagecontent` = 0, lngc.main, usr.`user_idlanguagecontent` = lngc.`idlanguage`)
					JOIN `resourcefromtables` AS res ON idtable = 6 AND res.idrecord = usr.iduser AND res.idlanguage = lngc.`idlanguage`
                        WHERE user_isadmin = 1;";

        $result = $cn->query($query);
        if ($result) {
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}


function db_getAdmin($cn){
        $resarr = array();
        $query = "SELECT usr.*, lng.`lang_short` ";
        $query .= "        FROM `users` AS usr, `languages` AS lng ";
        $query .= "        WHERE lng.`idlanguage` = usr.`user_idlanguageinterface` ";
        $query .= "        AND user_isadmin = 1; ";

        $result = $cn->query($query);
        if ($result) {
                while ($row = $result->fetch_assoc()) {
                        array_push($resarr, $row);
                }
        }
        logError($cn, $query);
        return $resarr;
}
function db_isUserAdmin($cn, $iduser){
        $query = "SELECT 1 FROM `users` WHERE iduser = ".mysqli_real_escape_string($cn, $iduser)." AND user_isadmin = 1; ";
        return sizeof(db_runQueryAndReturnArray($cn, $query)) > 0;
}
// дата создания отчета ( индексации )
function db_getReportCreationDate($cn, $oguid, $rguid, $idlanguage, $skbranch = NULL){
        $ido = db_getIDObjectByGuid($cn, $oguid, $skbranch);
        $idr = db_getIDReportByGuid($cn, $rguid, $idlanguage, $skbranch);
        db_setTimeZone($cn);
        $query = "SELECT DATE_FORMAT(from_unixtime(ri_creationdate_tmst),'%d.%m.%Y %H:%i') AS ri_creationdate FROM `reportindex` WHERE skbranch = ".$skbranch." and idobject = ".$ido." and idreport = ".$idr." LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar['ri_creationdate'];
        } else {
                return '';
        }
}

function db_getReportIndexBody($cn, $oguid, $rguid, $idlanguage, $skbranch = NULL){
        $ido = db_getIDObjectByGuid($cn, $oguid, $skbranch);
        $idr = db_getIDReportByGuid($cn, $rguid, $idlanguage, $skbranch);
        $query = "SELECT `body` as body FROM `reportindex` WHERE idobject = ".$ido." and idreport = ".$idr." LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar['body'];
        } else {
                return '';
        }
}

function db_isReportNotExpire($cn, $oguid, $rguid, $idlanguage, $skbranch = NULL)
{
        $ido = db_getIDObjectByGuid($cn, $oguid, $skbranch);
        $idr = db_getIDReportByGuid($cn, $rguid, $idlanguage, $skbranch);
        db_setTimeZone($cn);
        $query = "SELECT 1 FROM `reportindex` 
                  WHERE idobject = ".$ido." 
                  AND idreport = ".$idr." 
                  AND (from_unixtime(ri_expirationdate_tmst) > NOW() or ri_expirationdate_tmst is null) 
                  AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                  LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        return (is_array($ar) && sizeof($ar) > 0);
}

function db_isReportHaveExpireDate($cn, $oguid, $rguid, $idlanguage, $skbranch = NULL)
{
        $ido = db_getIDObjectByGuid($cn, $oguid, $skbranch);
        $idr = db_getIDReportByGuid($cn, $rguid, $idlanguage, $skbranch);
        $query = "SELECT 1 FROM `reportindex` 
                   WHERE idobject = ".$ido." 
                     AND idreport = ".$idr." 
                     AND (ri_expirationdate_tmst is not null) 
                     AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                     LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        return (is_array($ar) && sizeof($ar) > 0);
}

function db_ReportResetExpireDate($cn, $oguid, $rguid, $idlanguage, $skbranch = NULL)
{
        $ido = db_getIDObjectByGuid($cn, $oguid, $skbranch);
        $idr = db_getIDReportByGuid($cn, $rguid, $idlanguage, $skbranch);
        $query = 'SET @@session.time_zone = "+00:00";';
        $result = $cn->query($query);
        logError($cn, $query);
        $query = "UPDATE `reportindex` 
                     SET ri_expirationdate_tmst = UNIX_TIMESTAMP(NOW())  
                   WHERE  idobject = ".$ido." 
                     AND idreport = ".$idr."
                     AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
        $result = $cn->query($query);
        logError($cn, $query);
}

function db_getReportIDLanguage($cn, $oguid, $rguid, $idlanguage, $skbranch = NULL)
{
        $ido = db_getIDObjectByGuid($cn, $oguid, $skbranch);
        $idr = db_getIDReportByGuid($cn, $rguid, $idlanguage, $skbranch);
        $query = "SELECT `idlanguage` FROM `objectsreports`
                   WHERE  idobject = ".$ido." 
                     AND idreport = ".$idr."
                     AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch).";";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['idlanguage'];
        } else {
                return $idlanguage;
        }
}

function db_isObjectsReportsExits($cn, $oguid, $rguid, $idlanguage, $skbranch = NULL)
{
        $ido = db_getIDObjectByGuid($cn, $oguid, $skbranch);
        $idr = db_getIDReportByGuid($cn, $rguid, $idlanguage, $skbranch);
        $query = "SELECT 1 FROM objectsreports 
                   WHERE idobject = ".$ido." 
                     AND idreport = ".$idr." 
                     AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                     LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        return (is_array($ar) && sizeof($ar) > 0);
}

function db_GetObjectSource($cn, $oguid, $skbranch = NULL)
{
        $query = "SELECT src_guid 
                  FROM objects WHERE o_guid = '".$oguid."' 
                  AND src_guid IS NOT NULL AND LENGTH(src_guid) > 0
                  AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                  LIMIT 1;";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['src_guid'];
        } else {
                return null;
        }
}

//71970
function db_isObjectHaveSource($cn, $oguid, $skbranch = NULL)
{
        $src_o = db_GetObjectSource($cn, $oguid, $skbranch);
        return !empty($src_o) && strlen($src_o) > 0;
}

function db_isObjectExits($cn, $oguid, $skbranch = NULL)
{
        $query = "SELECT 1 
                        FROM objects WHERE o_guid = '".$oguid."' 
                        AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                        LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        return (is_array($ar) && sizeof($ar) > 0);
}

function db_isObjectsMultianguage($cn, $oguid, $rguid, $skbranch = NULL)
{
        $ido = db_getIDObjectByGuid($cn, $oguid, $skbranch);
        $idr = db_getIDReportByGuid($cn, $rguid, 0, $skbranch);
        $query = "SELECT 1 FROM objectsreports 
                   WHERE idobject = ".$ido." 
                     AND idreport = ".$idr." 
                     AND (idlanguage IS NULL or idlanguage = 0)
                     AND skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                     LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        return (is_array($ar) && sizeof($ar) > 0);
}

function db_getUserRightsToReport($cn, $user_ids, $rguid, $oguid, $idlanguageContent, $skbranch = NULL)
{
	if (null === $user_ids || $user_ids[0] == 0 )
		return true;

	$ids = implode(",", $user_ids);

        $deflangid = db_getDefaultLangID($cn);
        $addSelectWithDefaultLang = "".($deflangid != null && $deflangid <> $idlanguageContent ? "OR rep.idlanguage = ".$deflangid." " : "");

	//9791 Сначала запретим те отчеты, на которые стоит явный запрет
	$query = "SELECT DISTINCT 1 FROM rightsreports as rr INNER JOIN reports as rep ON rr.idreport = rep.idreport AND rr.skbranch = rep.skbranch 
                        WHERE rr.iduser in (".$ids.") 
                        AND rep.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                        AND rep.r_guid='".mysqli_real_escape_string($cn,$rguid)."' 
                        AND (rep.idlanguage = ".$idlanguageContent." OR rep.idlanguage IS NULL OR rep.idlanguage = 0 ".$addSelectWithDefaultLang.")
                        AND `deny_allow` = 0;";
	$ar = $cn->query($query)->fetch_assoc();
	logError($cn, $query);
	if (is_array($ar) && sizeof($ar) > 0)
		return null;
	//9791 Теперь разрешим доступ к отчету, если объект отчета - наше физлицо.
	if ($oguid != ""){
	   $query = "SELECT DISTINCT 1 FROM users as u WHERE u.iduser in (".$ids.") and u.user_guid='".mysqli_real_escape_string($cn,$oguid)."';";
	   $ar = $cn->query($query)->fetch_assoc();
	   logError($cn, $query);
	   if (is_array($ar) && sizeof($ar) > 0)
			return true;
	}

	//9791 Дальше идет проверка прав стандартная, без оглядки на guid объекта

	//Если нет права 1, то запрет
	$query = "SELECT DISTINCT 1 FROM rightsreports as rr INNER JOIN reports as rep ON rr.idreport = rep.idreport AND rr.skbranch = rep.skbranch 
                        WHERE rr.iduser in (".$ids.") 
                        AND rep.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                        AND rep.r_guid='".mysqli_real_escape_string($cn,$rguid)."' 
                        AND (rep.idlanguage = ".$idlanguageContent." OR rep.idlanguage IS NULL OR rep.idlanguage = 0 ".$addSelectWithDefaultLang.")
                        AND `deny_allow` = 1;";
	$ar = $cn->query($query)->fetch_assoc();
	logError($cn, $query);
	if (!is_array($ar) || sizeof($ar) < 1)
		return null;

	//Если нет права 2, то запрет
	$query = "SELECT DISTINCT 1 FROM rightsreports as rr INNER JOIN reports as rep ON rr.idreport = rep.idreport AND rr.skbranch = rep.skbranch 
                        WHERE rr.iduser in (".$ids.") 
                        AND rep.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                        AND rep.r_guid='".mysqli_real_escape_string($cn,$rguid)."' 
                        AND (rep.idlanguage = ".$idlanguageContent." OR rep.idlanguage IS NULL OR rep.idlanguage = 0 ".$addSelectWithDefaultLang.")
                        AND `deny_allow` = 2;";
	$ar = $cn->query($query)->fetch_assoc();
	logError($cn, $query);
	if (!is_array($ar) || sizeof($ar) < 1)
		return null;

	//Если дошли до этой строчки, значит все права есть
	return true;
}

function db_getReportsToObject($cn, $oguid, $user_ids, $idlanguageContent, $skbranch = NULL)
{
        $resarr = array();
        $query = "SET SQL_BIG_SELECTS=1;";
        if ( getSetting('portal','useRights', 0) == 1 ) {
           $ids = implode(",", $user_ids);
		   $query  .= "CALL load_reports( '".mysqli_real_escape_string($cn, $oguid)."' , 1 ,'".$ids."', ".$idlanguageContent.", ".($skbranch == NULL ? "NULL": $skbranch)." );";
     	} else {
         	$query .= "CALL load_reports( '".mysqli_real_escape_string($cn, $oguid)."' , 0, '', ".$idlanguageContent.", ".($skbranch == NULL ? "NULL": $skbranch)." );";
        }
        $deflangid = db_getDefaultLangID($cn);
        $addSelectWithDefaultLang = "".($deflangid != null && $deflangid <> $idlanguageContent ? "OR rep.idlanguage = ".$deflangid." " : "");
        $query .= " SELECT res.rft_columnname_text as r_name, rep.idreport, rep.r_guid, rep.r_position, rep.idlanguage
                FROM reports as rep INNER JOIN r_temp as rt ON rep.idreport = rt.idreport
                INNER JOIN  resourcefromtables res ON rep.idreport = res.idrecord AND rep.skbranch = res.skbranch AND res.idlanguage = IF(IFNULL(rep.idlanguage,0) = 0, ".$idlanguageContent.", rep.idlanguage)
                WHERE rep.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                  AND res.idtable = 2
				  AND (rep.idlanguage = ".$idlanguageContent." OR rep.idlanguage IS NULL OR rep.idlanguage = 0 ".$addSelectWithDefaultLang.")
                  AND res.rft_columnname_text <> '-'
            ORDER BY rep.r_position;";
        if ($cn->multi_query($query)) {
                $cn->next_result();
                $cn->next_result();
                $result = $cn->store_result();
                if ($result) {
                        $row = null;
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_getUsersIsAuthorsToForumMessage($cn, $idforummessage){
        $resarr = array();
    	$query = "SELECT `iduser` FROM `forummessages` WHERE `idforummessage` = ".$idforummessage.";";
        if ($cn->multi_query($query)) {
                $result = $cn->store_result();
                if ($result) {
                        $row = null;
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row['iduser']);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;


}

// вернет массив пользователей которым привязан object с ol_type = 1, 2
function db_getUsersToObjectlinks($cn, $oguid, $skbranch = NULL){
        $resarr = array();

        $query = "SELECT iduser FROM `objectlinks` as oo INNER JOIN `objects` as obj 
                        ON oo.`idobject`= obj.`idobject` AND oo.`skbranch`= obj.`skbranch`
                        WHERE obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                         AND obj.`o_guid` = '".$oguid."' and oo.ol_type in (1,2);";
        if ($cn->multi_query($query)) {
                $result = $cn->store_result();
                if ($result) {
                        $row = null;
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row['iduser']);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;
}


// привязан ли пользователь к отчету
function db_isUserObjectLink($cn, $iduser, $oguid, $skbranch = NULL, $ol_type = "1,2"){
        $resarr = array();
        $query = "SELECT 1 FROM `objectlinks` as oo INNER JOIN `objects` as obj
                ON oo.`idobject`= obj.`idobject` AND oo.`skbranch`= obj.`skbranch` 
                WHERE  obj.`o_guid` = '".$oguid."' 
                AND obj.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                AND oo.iduser = ".$iduser." and oo.ol_type in (".$ol_type.") LIMIT 1;";
        $ar = $cn->query($query)->fetch_assoc();
        logError($cn, $query);
        return (is_array($ar) && sizeof($ar) > 0);
}

function db_getReportsToObjectAdmin($cn, $oguid, $idlanguage, $skbranch = NULL)
{
        $resarr = array();
        $query = "SET SQL_BIG_SELECTS=1;";
        $query .= "CALL load_reportsAdmin( '".mysqli_real_escape_string($cn,$oguid)."', ".$idlanguage.", ".($skbranch == NULL ? "NULL": $skbranch).");";
        $query .= " SELECT res.rft_columnname_text as r_name, rep.idreport, rep.r_guid, rep.r_position, rep.idlanguage
                FROM reports as rep, r_temp as rt, resourcefromtables res
                WHERE rep.idreport = rt.idreport
                  AND rep.skbranch = ".($skbranch == NULL ? "(SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1)": $skbranch)."
                  AND rep.idreport = res.idrecord
                  AND res.idtable = 2
                  AND (rep.idlanguage = ".$idlanguage." OR rep.idlanguage IS NULL)
                  AND res.idlanguage = ".$idlanguage."
                  AND res.rft_columnname_text <> '-'
            ORDER BY rep.r_position;";
        if ($cn->multi_query($query)) {
                $cn->next_result();
                $cn->next_result();
                $result = $cn->store_result();
                if ($result) {
                        $row = null;
                        while ($row = $result->fetch_assoc()) {
                                array_push($resarr, $row);
                        }
                        $result->close();
                }
        }
        logError($cn, $query);
        return $resarr;
}

function db_SortIndicatorsByFolder($cn, $contlngid, $indicators, $order_column, $order_val)
{
        $resarr = array();
        if (is_array($indicators) && count($indicators) > 0) {
			$query = " DROP TEMPORARY TABLE IF EXISTS `indicators_temp` ;";
			db_runQuery($cn, $query);

			$query = " CREATE TEMPORARY TABLE `indicators_temp` (
				`Folder` varchar(1500) NOT NULL,
				`Value` varchar(1500) NOT NULL,
				`OGuid` varchar(1500) NOT NULL,
				`ValueType` varchar(1500) NOT NULL,
				`DateFrom` varchar(1500) NOT NULL,
				`DateTo` varchar(1500) NOT NULL,
				`DatePlan` varchar(1500) NOT NULL,
				`DateFact` varchar(1500) NOT NULL,
				`Plan` varchar(1500) NOT NULL,
				`Fact` varchar(1500) NOT NULL
				)ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;";
			db_runQuery($cn, $query);

            for ($i = 0; $i < count($indicators) ; $i++) {
            	$query = "INSERT IGNORE INTO `indicators_temp` VALUES
            		('".mysqli_real_escape_string($cn, $indicators[$i]["Folder"])."',
            		 '".db_getObjectName($cn, $indicators[ $i ][ "Value" ], $contlngid )."',
            		 '".mysqli_real_escape_string($cn, $indicators[$i]["Value"])."',
            		 '".mysqli_real_escape_string($cn, $indicators[$i]["ValueType"])."',
            		 '".mysqli_real_escape_string($cn, $indicators[$i]["DateFrom"])."',
            		 '".mysqli_real_escape_string($cn, $indicators[$i]["DateTo"])."',
            		 '".mysqli_real_escape_string($cn, $indicators[$i]["DatePlan"])."',
            		 '".mysqli_real_escape_string($cn, $indicators[$i]["DateFact"])."',
            		 '".mysqli_real_escape_string($cn, $indicators[$i]["Plan"])."',
            		 '".mysqli_real_escape_string($cn, $indicators[$i]["Fact"])."'
            		 );";
    			db_runQuery($cn, $query);
            }

	        $query = "SELECT * FROM `indicators_temp` ORDER BY `Folder` ASC, ".$order_column." ".$order_val." ;";
	        if ($cn->multi_query($query)) {
	                $result = $cn->store_result();
	                if ($result) {
	                        $row = null;
	                        while ($row = $result->fetch_assoc()) {
	                                //STU 9924 array_push($resarr, $row);
									$guid = ''.$row['OGuid'];
									$resarr[$guid] = $row;
	                        }
	                        $result->close();
	                }
	        }
	        logError($cn, $query);
        }
        return $resarr;
}

function db_getYearWithWeek($year, $month, $week){
	if ($month==12 && $week<10) return $year+1;
	if ($month==1 && $week>50) return $year-1;
	return $year;
}

function db_getWeekNumber($cn, $date, $format, $firstdayweek){
	if(strtolower($format)=='-1'){
		$query = "SELECT IF(DAYOFWEEK('".$date."')=1,WEEK(DATE_ADD('".$date."', INTERVAL -3 DAY),1),WEEK(DATE_ADD('".$date."', INTERVAL 5-DAYOFWEEK('".$date."') DAY),1)) AS week";
		$ar = db_runQueryAndReturnArray($cn, $query);
		if (is_array($ar) && sizeof($ar) > 0){
			return $ar[0]['week'];
		}
		return 0;
	}

	$array_days = array('Monday'=>1, 'Tuesday'=>2, 'Wednesday'=>3, 'Thursday'=>4, 'Friday'=>5, 'Saturday'=>6, 'Sunday'=>0);
	$number_dayweek = $array_days[$firstdayweek];

	$date_obj = new DateTime($date);
	$date_1_jan = new DateTime($date_obj->format('Y-01-01'));
	$num_date = $date_obj->format('z');//z	Порядковый номер дня в году (начиная с 0)	От 0 до 365
	$day_week_date_1_jan = $date_1_jan->format('w');//w	Порядковый номер дня недели	от 0 (воскресенье) 6(суббота)

	$diff_day_week = $day_week_date_1_jan - $number_dayweek;
	if ($diff_day_week<0) $diff_day_week+=7;
	$num_date+=$diff_day_week;//Сдвинем номер дня, чтобы он считался по нашему дню недели, а не по 1январскому.
	switch(strtolower($format)){
		case "firstfourdayweek":
			if ($diff_day_week>3) $num_date+=-7;//То же самое, если бы мы считали по полной неделе
			break;
		case "firstfullweek":
			$num_date+=($day_week_date_1_jan==$number_dayweek)?0:-7;
			break;
	}
	$num_week = floor($num_date/7)+1;
	if ($num_week<=0){
		$num_week += db_getWeekNumber($cn, ($date_obj->sub(new DateInterval('P7D')))->format('Y-m-d'), $format, $firstdayweek)+1;
	}
	return $num_week;//Если у нас 1 января - начало недели, то можно отсчитать недели, помня, что 7 января - уже вторая неделя.
}

// получить id ветки по СК ключу
function db_getIDBranch($cn, $skbranch ){
        $query = "SELECT `idbranch` FROM `branches` WHERE `skbranch` = '".mysqli_real_escape_string($cn,$skbranch)."';" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['idbranch'];
        } else {
                return '';
        }
} 
// получить СК ветки по внешнему ключу
function db_getSKBranch($cn, $idbranch ){
        $query = "SELECT `skbranch` FROM `branches` WHERE `idbranch` = '".mysqli_real_escape_string($cn,$idbranch)."';" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['skbranch'];
        } else {
                return NULL;
        }
} 

//получить СК ветки по вероятному ответу пользователя
function db_getSKBranchByUserAnswerValue($cn, $skpollingobject, $skpolltype, $userid  ){
        $query = "SELECT `skbranch` FROM `pollingobjects` as po 
                        INNER JOIN `useranswers` as ua ON po.idpollingobject = ua.idpollingobject
                        INNER JOIN `polltypes` as pt ON ua.idpolltype = pt.idpolltype
                        INNER JOIN `users` as u ON ua.user_hash = u.user_hash
                  WHERE pt.skpolltype = ".mysqli_real_escape_string($cn,$skpolltype)." AND 
                        po.skpollingobject = ".mysqli_real_escape_string($cn,$skpollingobject)." AND 
                        u.iduser = ". $userid." LIMIT 1;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['skbranch'];
        } else {
                return 0;
        }
}

// дефолтная ветка модели
function db_getMainSKBranch($cn){
        $query = "SELECT `skbranch` FROM `branches` WHERE ismain = 1 LIMIT 1;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['skbranch'];
        } else {
                return 0;
        }
}

function db_getMainIDBranch($cn){
        $query = "SELECT `idbranch` FROM `branches` WHERE ismain = 1 LIMIT 1;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['idbranch'];
        } else {
                return 0;
        }
}

function db_getMainIDLanguage($cn){
        $query = "SELECT `idlanguage` FROM `languages` WHERE main = 1 AND allow = 1 LIMIT 1;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['idlanguage'];
        } else {
                return 0;
        }
}

function db_getSKPollingObjectByBranch($cn, $skbranch, $oguid, $rguid){
        db_setTimeZone($cn);
        $query = "SELECT skpollingobject
                FROM `pollingobjects` as  po
                   WHERE `skbranch` = ".mysqli_real_escape_string($cn,$skbranch)." 
                   AND  `o_guid` = '".mysqli_real_escape_string($cn,$oguid)."' 
                   AND  `r_guid` = '".mysqli_real_escape_string($cn,$rguid)."' ;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['skpollingobject'];
        } else {
                return NULL;
        }
}

function db_getObjectFullVersion($cn, $oguid, $skbranch, $contlngid){
        $query = "SELECT res_ver.rft_columnname_text as `objectfullversion`
                        FROM (SELECT idobject FROM objects WHERE `skbranch` = ".mysqli_real_escape_string($cn,$skbranch)." AND `o_guid` = '".mysqli_real_escape_string($cn,$oguid)."') as obj
                        INNER JOIN `resourcefromtables` res_ver ON `obj`.`idobject` = res_ver.`idrecord` AND res_ver.`idtable` = 1111 AND res_ver.idlanguage =  ".$contlngid." ;";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['objectfullversion'];
        } else {
                return "";
        }
}

function db_getReportFullVersion($cn, $rguid, $skbranch, $contlngid){
        $query = "SELECT res_ver.rft_columnname_text as `objectfullversion`
                        FROM (SELECT idreport FROM reports WHERE `skbranch` = ".mysqli_real_escape_string($cn,$skbranch)." AND `r_guid` = '".mysqli_real_escape_string($cn,$rguid)."') as rep
                        INNER JOIN `resourcefromtables` res_ver ON `rep`.`idreport` = res_ver.`idrecord` AND res_ver.`idtable` = 2111 AND res_ver.idlanguage =  ".$contlngid." ;";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['objectfullversion'];
        } else {
                return "";
        }
}

function db_getObjectVersionDescription($cn, $oguid, $skbranch, $contlngid){
        $query = "SELECT res_ver.`rft_columnname_long` 
                        FROM (SELECT idobject FROM objects WHERE `skbranch` = ".mysqli_real_escape_string($cn,$skbranch)." AND `o_guid` = '".mysqli_real_escape_string($cn,$oguid)."') as obj
                        INNER JOIN `resourcefromtables` res_ver ON `obj`.`idobject` = res_ver.`idrecord` AND res_ver.`idtable` = 1101 AND res_ver.idlanguage =  ".$contlngid.";";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['rft_columnname_long'];
        } else {
                return "";
        }
}

function db_getPollTypeAllowComment($cn, $skpolltype){
        $query = "SELECT `AllowComment` FROM `polltypes` 
                   WHERE `skpolltype` = ".mysqli_real_escape_string($cn,$skpolltype)." LIMIT 1 ;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['AllowComment'] == 1;
        } else {
                return false;
        }
}

function db_getPollTypeAllowToChangeAnswer($cn, $skpolltype){
        /*62359 где AllowToChangeAnswer – новый параметр, задающий тип разрешения смены ответа:
                1 - В течение сессии браузера
                2 - Пока опрос активен
        */
        $query = "SELECT IFNULL(`AllowToChangeAnswer`, 1) as `AllowToChangeAnswer` FROM `polltypes` 
                   WHERE `skpolltype` = ".mysqli_real_escape_string($cn,$skpolltype)." ;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['AllowToChangeAnswer'] == 1;
        } else {
                return false;
        }
}

function db_getIDPollTypeBySK($cn, $skpolltype){
        $query = "SELECT `idpolltype` FROM `polltypes` WHERE `skpolltype` = ".$skpolltype.";";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['idpolltype'];
        } else {
                return NULL;
        }
}

function db_getPolltypeName($cn, $idpolltype, $lngid){
        
        $query = "SELECT `rft_columnname_text` FROM `resourcefromtables` WHERE `idrecord` = (SELECT `skpolltype`  FROM `polltypes` WHERE idpolltype = '".mysqli_real_escape_string($cn, $idpolltype)."' LIMIT 1) AND `idtable` = 8 AND idlanguage = ".$lngid." LIMIT 1;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['rft_columnname_text'];
        } else {
                return "";
        }
}

function db_getPolltypeNameBySK($cn, $skpolltype, $lngid){
        
        $query = "SELECT `rft_columnname_text` FROM `resourcefromtables` WHERE `idrecord` =  ".mysqli_real_escape_string($cn, $skpolltype)." AND `idtable` = 8 AND idlanguage = ".$lngid." LIMIT 1;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['rft_columnname_text'];
        } else {
                return "";
        }
}


function db_getBranchName($cn, $skbranch, $lngid){
        $query = "SELECT `rft_columnname_text` FROM `resourcefromtables` WHERE `idrecord` = ".$skbranch." AND `idtable` = 7 AND idlanguage = ".$lngid." ";
        $query .= "  LIMIT 1;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['rft_columnname_text'];
        } else {
                return "";
        }
}

function db_getProjectName($cn, $skproject, $lngid){
        $query = "SELECT `rft_columnname_text` FROM `resourcefromtables` WHERE `idrecord` = ".$skproject." AND `idtable` = 10 AND idlanguage = ".$lngid."  " ;
        $query .= "  LIMIT 1;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['rft_columnname_text'];
        } else {
                return "";
        }
}

function db_getObjectVersionuHashByPollingObject($cn, $skpollingobject ){
        $query = "SELECT `objectversionuhash` FROM `pollingobjects` as po 
                  WHERE po.skpollingobject = ".mysqli_real_escape_string($cn,$skpollingobject)." ;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0){
                return $ar[0]['objectversionuhash'];
        } else {
                return '';
        }
}

function db_checkObjectVersionuHashByPollingObject($cn, $skpollingobject, $objectversionuhash ){
        $query = "SELECT 1 as res FROM `pollingobjects` as po 
                  WHERE po.skpollingobject = ".mysqli_real_escape_string($cn,$skpollingobject)." AND objectversionuhash = '".mysqli_real_escape_string($cn,$objectversionuhash)."' ;" ;
        $arw = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0 && sizeof($ar[0]) > 0){
                return $ar[0]['res'] == 1;
        } else {
                return false;
        }
}

function db_getPollObjectStatus($cn, $skpollingobject, $skpolltype ){
        $query = "SELECT idpollingobjectstatus
        FROM `pollingobjects` as po 
        INNER JOIN `useranswers` as ua ON po.idpollingobject = ua.idpollingobject
        INNER JOIN `polltypes` as pt ON ua.idpolltype = pt.idpolltype
        WHERE po.skpollingobject = ".$skpollingobject." AND 
              pt.skpolltype = ".$skpolltype."  LIMIT 1;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0 && sizeof($ar[0]) > 0){
                return $ar[0]['idpollingobjectstatus'];
        } else {
                return 2;
        }
}

function db_getGuidsByPollingObject($cn, $skpollingobject ){
        $query = "SELECT `o_guid`, `r_guid`, `skbranch`, `skproject` FROM `pollingobjects` as po 
                  WHERE po.skpollingobject = ".mysqli_real_escape_string($cn,$skpollingobject)." ;" ;
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getPolltypeQuestionType($cn, $skpolltype){
        $query = "SELECT idpolltypequestiontype, skpolltypequestion
                   FROM  `polltypequestions` as pq 
                   WHERE pq.skpolltype = ".$skpolltype."  ;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        return $ar;
}

function db_getPolltypeQuestion($cn, $skpollingobject, $skpolltype, $iduser, $lngid ){
        $query = "SELECT res_pq.rft_columnname_text, pq.idpolltypequestiontype, pq.skpolltypequestion
                   FROM `useranswers` as ua_w 
                   INNER JOIN `polltypequestions` as pq ON pq.skpolltype = ua_w.skpolltype
                   INNER JOIN `resourcefromtables` as res_pq ON res_pq.idrecord = pq.skpolltypequestion AND idtable = 9 AND idlanguage = ".$lngid." 
                   WHERE ua_w.skpollingobject = ".$skpollingobject." AND 
                        ua_w.iduser = ".$iduser." AND 
                        ua_w.skpolltype = ".$skpolltype."  ;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        return $ar;
}

function db_getQuestionSelectValues($cn, $skpolltypequestion, $lngid ){
        $query = "SELECT  qsv.skpolltypequestionselectvalue, res_qsv.rft_columnname_text
                    FROM `polltypequestions` as pq 
                    INNER JOIN `polltypequestionselectvalues` as qsv ON qsv.skpolltypequestion = pq.skpolltypequestion 
                    INNER JOIN `resourcefromtables` as res_qsv ON res_qsv.idrecord = qsv.skpolltypequestionselectvalue AND idtable = 90 AND idlanguage = ".$lngid." 
                    WHERE pq.skpolltypequestion = ".$skpolltypequestion."  ;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        return $ar;
}

function db_getQuestionSelectValueByID($cn, $skpolltypequestion, $id, $lngid){
        $query = "SELECT  res_qsv.rft_columnname_text
                    FROM `polltypequestions` as pq 
                    INNER JOIN `polltypequestionselectvalues` as qsv ON qsv.skpolltypequestion = pq.skpolltypequestion 
                    INNER JOIN `resourcefromtables` as res_qsv ON res_qsv.idrecord = qsv.skpolltypequestionselectvalue AND idtable = 90 AND idlanguage = ".$lngid." 
                    WHERE qsv.skpolltypequestionselectvalue = ".$id." AND pq.skpolltypequestion = ".$skpolltypequestion."  ;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if ($ar === NULL || !is_array($ar) || sizeof($ar) == 0 || sizeof($ar[0]) == 0){
                return '';
        } else {
                return $ar[0]['rft_columnname_text'];
        }
}

function db_getPolltypeQuestionValueByID($cn, $id, $lngid){
        $query = "SELECT res_pq.rft_columnname_text
                   FROM `resourcefromtables` as res_pq WHERE res_pq.idrecord = ".$id." AND idtable = 9 AND idlanguage = ".$lngid."  ;" ;
        $ar = db_runQueryAndReturnArray($cn, $query);
        if ($ar === NULL || !is_array($ar) || sizeof($ar) == 0 || sizeof($ar[0]) == 0){
                return '';
        } else {
                return $ar[0]['rft_columnname_text'];
        }
}

function db_savePolltypeQuestionComment($cn, $skpollingobject, $skpolltype, $iduser, $uav_body){
        $query = "UPDATE useranswersvalues SET uav_body  = '".mysqli_real_escape_string($cn,$uav_body)."' 
                   WHERE skpollingobject=".$skpollingobject.' AND skpolltype='.$skpolltype.' AND iduser='.$iduser."; ";
        db_runQuery($cn, $query);
        $query = 'INSERT IGNORE INTO useranswersvalues 
                        SELECT * FROM ( SELECT '.$skpollingobject.' as a, '.$skpolltype.' as b, '.$iduser." as c, 0 as value, '".mysqli_real_escape_string($cn,$uav_body)."' as f, NULL as g, null as time,'".(!isset($_SESSION['ss_key']) ? $_SESSION['ss_key'] : '')."' as sskey) as tmp  
                         WHERE NOT EXISTS (SELECT 1 FROM useranswersvalues WHERE skpollingobject=".$skpollingobject." AND skpolltype=".$skpolltype." AND iduser=".$iduser." LIMIT 1);";
        db_runQuery($cn, $query);
}

function db_savePolltypeQuestionAnswer($cn, $skpollingobject, $skpolltype, $iduser, $uav_value, $uav_body, $postonforum = false, $oguid = NULL, $skbranch = NULL ){
        $query = 'UPDATE useranswersvalues 
                      SET uav_value = '.mysqli_real_escape_string($cn,$uav_value).", 
                          uav_body  = '".mysqli_real_escape_string($cn,$uav_body)."' ,
                          ss_key= '".$_SESSION['ss_key']."' ,
                          uav_tmst = UNIX_TIMESTAMP(NOW())
						WHERE skpollingobject=".$skpollingobject.' AND skpolltype='.$skpolltype.' AND iduser='.$iduser." AND ( ss_key= '".$_SESSION['ss_key']."' OR uav_value = 0 OR  EXISTS
                                                        ( SELECT 1 FROM `polltypes` WHERE `AllowToChangeAnswer` = 2 AND skpolltype=".$skpolltype." LIMIT 1)); ";
        db_runQuery($cn, $query);
        $query = 'INSERT IGNORE INTO useranswersvalues 
                        SELECT * FROM ( SELECT '.$skpollingobject.' as a, '.$skpolltype.' as b, '.$iduser.' as c, '.mysqli_real_escape_string($cn,$uav_value)." as e, '".mysqli_real_escape_string($cn,$uav_body)."' as f, NULL as g, UNIX_TIMESTAMP(NOW()) as h, '".$_SESSION['ss_key']."') as tmp  
                         WHERE NOT EXISTS (SELECT 1 FROM useranswersvalues WHERE skpollingobject=".$skpollingobject." AND skpolltype=".$skpolltype." AND iduser=".$iduser." LIMIT 1);";
        db_runQuery($cn, $query);

        $query = "UPDATE useranswers as ua
			INNER JOIN  useranswersvalues as uav ON uav.skpollingobject = ua.skpollingobject AND uav.skpolltype = ua.skpolltype AND uav.iduser = ua.iduser
                        SET ua.hasanswer = CASE WHEN uav.uav_value != 0 THEN 1 ELSE 0 END
                                WHERE uav.skpollingobject=".$skpollingobject.' AND uav.skpolltype='.$skpolltype.' AND uav.iduser='.$iduser."; ";
        db_runQuery($cn, $query);                                

        if ($postonforum) {
                $query = 'SELECT idforummessage FROM useranswersvalues WHERE skpollingobject='.$skpollingobject.' AND skpolltype='.$skpolltype.' AND iduser='.$iduser.'; ';
                $fm = db_runQueryAndReturnArray($cn, $query);
                if (is_array($fm) && sizeof($fm) > 0 && $fm[0]['idforummessage'] !== NULL && $fm[0]['idforummessage'] > 0) {
                        $query = " UPDATE `forummessages` SET `frmm_body` = '".mysqli_real_escape_string($cn,$uav_body)."',  `frmm_datechange_tmst` = UNIX_TIMESTAMP(NOW()) , `frmm_ischanged` = 1 WHERE `iduser` =  ".$iduser." AND `idforummessage` =  ".$fm[0]['idforummessage']." ;";
                        db_runQuery($cn, $query);
                } else {
                        $query = "     call add_forummessages(0, '".mysqli_real_escape_string($cn, $oguid)."', ".$iduser.", '".mysqli_real_escape_string($cn,$uav_body)."', ".$skbranch.");";
                        db_runQuery($cn, $query);
                        $query = '    SELECT @idforummessage := max(idforummessage) FROM `forummessages` WHERE iduser = '.$iduser.'; ';
                        db_runQuery($cn, $query);
                        $query = '     UPDATE useranswersvalues 
                                           SET idforummessage = @idforummessage 
                                         WHERE skpollingobject='.$skpollingobject.' AND skpolltype='.$skpolltype.' AND iduser='.$iduser.'; ';
                        db_runQuery($cn, $query);
                }
        }
}

function db_hasPolltypeObjectHistory($cn, $skpollingobject ){
        $query = " SELECT COUNT(0) as cnt FROM pollingobjectsarchive WHERE skpollingobject=".$skpollingobject." ; ";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0 && sizeof($ar[0]) > 0){
                return $ar[0]['cnt'] > 0;
        } else {
                return FALSE;
        }
}

function db_getPolltypeQuestionAnswersExpected($cn, $skpollingobject, $skpolltype  ){
        db_setTimeZone($cn);
        $query = "SELECT ua_w.iduser, ua_w.skpollingobject, ua_w.skpolltype, uav.uav_value, uav.uav_body,
        DATE_FORMAT(from_unixtime(uav_tmst),'%d.%m.%Y %H:%i') as uav_tmst
           FROM useranswers as ua_w LEFT JOIN useranswersvalues as uav
                   ON ua_w.iduser = uav.iduser AND ua_w.skpolltype = uav.skpolltype AND ua_w.skpollingobject = uav.skpollingobject
           WHERE ua_w.skpollingobject=".$skpollingobject." AND ua_w.skpolltype=".$skpolltype."; ";
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getPolltypeQuestionAnswersExpectedWithNames($cn, $skpollingobject, $skpolltype, $idlanguage, $order_column, $order_val, $answers = NULL){
        db_setTimeZone($cn);
        $query = "SELECT ua_w.iduser, ua_w.skpollingobject, ua_w.skpolltype, uav.uav_value, uav.uav_body,
                        DATE_FORMAT(from_unixtime(uav_tmst),'%d.%m.%Y %H:%i') as uav_tmst, rft.rft_columnname_text as user_name, 
                        CASE WHEN ua_w.`hasanswer` = 1 THEN 
                                CASE
                                        WHEN pq.idpolltypequestiontype = 1 THEN res_pq.rft_columnname_text
                                        ELSE res_qsv.rft_columnname_text
                                END 
                        ELSE '' 
                        END as answer_value
                        FROM useranswers as ua_w LEFT JOIN useranswersvalues as uav
                                ON ua_w.iduser = uav.iduser AND ua_w.skpolltype = uav.skpolltype AND ua_w.skpollingobject = uav.skpollingobject
                        INNER JOIN `polltypequestions` as pq 
                                ON pq.skpolltype = ua_w.skpolltype                                
                        INNER JOIN `resourcefromtables` as res_pq 
                                ON res_pq.idrecord = pq.skpolltypequestion AND res_pq.idtable = 9 AND res_pq.idlanguage = ".$idlanguage."                                 
                        INNER JOIN resourcefromtables rft 
                                ON rft.idtable = 6 AND rft.idrecord = ua_w.iduser AND rft.idlanguage = ".$idlanguage."  
                        LEFT JOIN `polltypequestionselectvalues` as qsv 
                                ON qsv.skpolltypequestion = pq.skpolltypequestion AND qsv.skpolltypequestionselectvalue = uav.uav_value
                        LEFT JOIN `resourcefromtables` as res_qsv 
                                ON res_qsv.idrecord = qsv.skpolltypequestionselectvalue AND res_qsv.idtable = 90 AND res_qsv.idlanguage = ".$idlanguage." 
                        WHERE ua_w.skpollingobject=".$skpollingobject." AND ua_w.skpolltype=".$skpolltype." ";
                        if (isset($answers) ){
                                if ($answers == 'hasnot' ){
                                        $query .= " AND  ua_w.`hasanswer` = 0 ";
                                }
                                if ($answers == 'has' ){
                                        $query .= " AND  ua_w.`hasanswer` = 1 ";
                                }
                        }                        
                        $query .= " ".((isset($order_column) && mb_strlen($order_column) > 0) ? " ORDER BY ".$order_column." ".$order_val."; " : " ;");
        return db_runQueryAndReturnArray($cn, $query);//63976 
}


function db_getPolltypeQuestionAnswer($cn, $skpollingobject, $skpolltype, $iduser ){
        db_setTimeZone($cn);
        $query = "SELECT *,DATE_FORMAT(from_unixtime(uav_tmst),'%d.%m.%Y %H:%i') as uav_tmst FROM useranswersvalues WHERE skpollingobject=".$skpollingobject." AND skpolltype=".$skpolltype." AND iduser=".$iduser."; ";
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getPolltypeQuestionAnswers($cn, $skpollingobject, $skpolltype  ){
        db_setTimeZone($cn);
        $query = "
                SELECT uav.*, DATE_FORMAT(from_unixtime(uav_tmst),'%d.%m.%Y %H:%i') as uav_tmst 
                FROM useranswersvalues uav
                INNER JOIN useranswers ua ON uav.skpollingobject = ua.skpollingobject AND  uav.skpolltype = ua.skpolltype AND  uav.iduser = ua.iduser 
                WHERE uav.skpollingobject=".$skpollingobject." AND uav.skpolltype=".$skpolltype." AND ua.hasanswer != 0; ";
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getPolltypeQuestionAnswersToUser($cn, $skpollingobject, $skpolltype, $iduser ){
        db_setTimeZone($cn);
        $query = "
                SELECT uav.*, DATE_FORMAT(from_unixtime(uav_tmst),'%d.%m.%Y %H:%i') as uav_tmst 
                FROM useranswersvalues uav
                INNER JOIN useranswers ua ON uav.skpollingobject = ua.skpollingobject AND  uav.skpolltype = ua.skpolltype AND  uav.iduser = ua.iduser 
                WHERE uav.skpollingobject=".$skpollingobject." AND uav.skpolltype=".$skpolltype." AND ua.hasanswer != 0 AND uav.iduser = ".$iduser."; ";
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getUserAnswersValuesArchive($cn, $idpollingobjectarchive, $skpolltype, $iduser ){
        db_setTimeZone($cn);
        $query = "SELECT *,DATE_FORMAT(from_unixtime(uav_tmst),'%d.%m.%Y %H:%i') as uava_time FROM useranswersvaluesarchive WHERE idpollingobjectarchive=".$idpollingobjectarchive." AND skpolltype=".$skpolltype." AND iduser=".$iduser."; ";
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getPollingObject($cn, $skpollingobject){
        $query = 'SELECT * FROM pollingobjects WHERE skpollingobject='.$skpollingobject.' ; ';
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getSKPollingObject($cn, $idpollingobject){
        $query = "SELECT skpollingobject FROM `pollingobjects` WHERE idpollingobject='".mysqli_real_escape_string($cn,$idpollingobject)."' ;";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0 && sizeof($ar[0]) > 0){
                return $ar[0]['skpollingobject'];
        } else {
                return 0;
        }
}

function db_getSKPollType($cn, $idpolltype){
        $query = "SELECT skpolltype FROM `polltypes` WHERE idpolltype='".mysqli_real_escape_string($cn,$idpolltype)."' ;";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0 && sizeof($ar[0]) > 0){
                return $ar[0]['skpolltype'];
        } else {
                return 0;
        }
}

function db_getlanguagesForReport($cn, $r_guid, $skbranch){
        $query = "SELECT distinct IFNULL(languages.lang_short,'') as lang_short  FROM reports LEFT JOIN languages ON reports.idlanguage = languages.idlanguage WHERE reports.skbranch = ".$skbranch." AND reports.r_guid='".mysqli_real_escape_string($cn, $r_guid)."' ; ";
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getPollObjectsWithoutAnswerForMenu($cn, $userid){
        $query = "SELECT COUNT(0) as cnt  FROM useranswers as ua_w
                        INNER JOIN pollingobjects as po ON ua_w.skpollingobject = po.skpollingobject
                        INNER JOIN `objects` o ON po.`idobject` = o.`idobject` 
                        INNER JOIN `branches` br ON br.`skbranch` = po.`skbranch` AND br.iscomplete = 1
                        WHERE ua_w.idpollingobjectstatus = 1 AND IFNULL(ua_w.hasanswer,0) = 0 AND ua_w.`iduser` = ".$userid." ;";
	$ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) > 0 && sizeof($ar[0]) > 0){
                return $ar[0]['cnt'];
        } else {
                return 0;
        }
}

function db_createUsersIDsTempTable($cn, $u_ids) {
        db_dropTempTable($cn, 'userids_t');
	$q_st = " CREATE TEMPORARY TABLE `userids_t` (
		`iduser` INT NOT NULL,
		PRIMARY KEY (`iduser`)
		)ENGINE=MEMORY";
        db_runQuery($cn, $q_st);
        for($i=0; $i< sizeof($u_ids); $i++){
                db_runQuery($cn, "INSERT IGNORE INTO `userids_t` SELECT ".$u_ids[$i]);
        }
}


function db_createObjectsFullRightsMap($cn, $u_ids) {
        db_setNolock($cn);
        db_createUsersIDsTempTable($cn, $u_ids);

        db_dropTempTable($cn, 'rightsmap_temp');
	
	$q_st = "    CREATE TEMPORARY TABLE `rightsmap_temp` (
		`idobject` BIGINT NOT NULL,
		`o_guid` CHAR(40) NOT NULL,
		`o_icon` varchar(800) NOT NULL,
                `skbranch` INT NOT NULL,
		PRIMARY KEY (`idobject`),
		INDEX `o_guid` (`o_guid`)
		)ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;";
	db_runQuery($cn, $q_st);
	
        db_dropTempTable($cn, 'rightsmap_temp_allow2');
	
	$q_st = "    CREATE TEMPORARY TABLE `rightsmap_temp_allow2` (
		`idobject` BIGINT NOT NULL,
		PRIMARY KEY (`idobject`)
		)ENGINE=MEMORY";
	db_runQuery($cn, $q_st);
	
	
        $q_st = "INSERT IGNORE INTO `rightsmap_temp_allow2` 
        SELECT ro.`idobject`
          FROM `rightsobjects` as ro INNER JOIN userids_t ut ON ro.`iduser` = ut.`iduser` AND ro.`deny_allow` = 2;";
        db_runQuery($cn, $q_st);

	$q_st = "INSERT IGNORE INTO `rightsmap_temp` 
				SELECT ro.`idobject`, o.`o_guid`, o.`o_icon`, o.`skbranch`
				  FROM `rightsmap_temp_allow2` as ro2
				  INNER JOIN `rightsobjects` as ro ON ro.`idobject` = ro2.`idobject` AND ro.`deny_allow` = 1
                                  INNER JOIN userids_t ut ON ro.`iduser` = ut.`iduser` 
				  INNER JOIN `objects` as o ON o.`idobject` = ro.`idobject` 
				 WHERE  o.o_hidefortree <> 1 ;";
	db_runQuery($cn, $q_st);

        // get user rights by user_guid              
	$q_st = "INSERT IGNORE INTO `rightsmap_temp` 
				SELECT o.`idobject`, o.`o_guid`, o.`o_icon`, o.`skbranch`
				  FROM `objects` as o
				  INNER JOIN  `users` as u ON u.`user_guid` = o.`o_guid`
                                  INNER JOIN userids_t ut ON u.`iduser` = ut.`iduser`;";
	db_runQuery($cn, $q_st);

        // get user rights by assigned polls              
	$q_st = "INSERT IGNORE INTO `rightsmap_temp` 
				SELECT o.`idobject`, o.`o_guid`, o.`o_icon`, o.`skbranch`
                                FROM  `objects` as o 
                                INNER JOIN `pollingobjects` as po ON o.`idobject` = po.`idobject` 
                                INNER JOIN `useranswers` as ua ON po.skpollingobject = ua.skpollingobject
                                INNER JOIN userids_t ut ON ua.`iduser` = ut.`iduser` AND ua.idpollingobjectstatus = 1;";
	db_runQuery($cn, $q_st);

        $q_st = "INSERT IGNORE INTO `rightsmap_temp` 
				SELECT o.`idobject`, o.`o_guid`, o.`o_icon`, o.`skbranch`
                                FROM  `objects` as o 
                                INNER JOIN `pollingobjects` as po ON o.`idobject` = po.`idobject` 
                                INNER JOIN `useranswers` as ua ON po.skpollingobject = ua.skpollingobject
                                INNER JOIN userids_t ut ON ua.`iduser` = ut.`iduser`
                                INNER JOIN `rightspollingobjects` as rpo ON rpo.`skpollingobject` = po.`skpollingobject` AND  rpo.iduser = ut.iduser 
                                WHERE rpo.deny_allow = 1;";
	db_runQuery($cn, $q_st);

        $q_st = "INSERT IGNORE INTO `rightsmap_temp` 
                                SELECT o.`idobject`, o.`o_guid`, o.`o_icon`, o.`skbranch`
                                FROM  `objects` as o 
                                INNER JOIN `pollingobjects` as po ON o.`idobject` = po.`idobject` 
                                INNER JOIN `rightsadminpollingobjects` u ON CASE WHEN ( u.`skproject` IS NULL or u.`skproject` = 0 ) THEN po.`skbranch` = u.`skbranch` ELSE po.`skproject` = u.`skproject` END
                                INNER JOIN userids_t ut ON ut.`iduser` = u.`iduser`
                                WHERE u.deny_allow = 1;";
        db_runQuery($cn, $q_st);

        $q_st = "DELETE FROM `rightsmap_temp` WHERE `idobject` IN (
                        SELECT `idobject` FROM `rightsobjects` as ro
                        INNER JOIN userids_t ut ON ut.`iduser` = ro.`iduser`
                        AND `deny_allow` = 0);"; 
	db_runQuery($cn, $q_st);
}

function db_getNewMesssageCountForMenu($cn, $u_ids){
        $ids = implode(",", $u_ids);
        
        $query = 'SET @@session.time_zone = "+00:00";';
        $cn->query($query);

	db_prepareSQLBIGSELECT($cn);
        
        db_createObjectsFullRightsMap($cn, $u_ids);

        db_dropTempTable($cn, 'msgs_new');
	$q_st = "CREATE TEMPORARY TABLE `msgs_new` (
		`o_guid` CHAR(40) NOT NULL,
		`new_cnt` INT NULL,
                `skbranch` INT NULL,
		PRIMARY KEY (`o_guid`)
		)ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;";
	db_runQuery($cn, $q_st);
 
        //66188 Task 66189
        $mainskbranch = db_getMainSKBranch($cn);
        
        // add main branch 
	$q_st = "INSERT IGNORE INTO `msgs_new` 
	SELECT fm.`o_guid`, 0, fm.`skbranch`
	  FROM `forummessages` as fm
	  INNER JOIN `rightsmap_temp` as ro ON ro.`o_guid` COLLATE utf8mb4_unicode_ci = fm.`o_guid` COLLATE utf8mb4_unicode_ci
                  AND ro.`skbranch` = fm.`skbranch`
          WHERE frmm_isdelete = 0 AND fm.`iduser` NOT IN (".$ids.")
                AND ro.`skbranch` = ".$mainskbranch." AND fm.`skbranch` = ".$mainskbranch."                  
	  GROUP BY fm.`o_guid`, fm.`skbranch` ;";
        db_runQuery($cn, $q_st);

        // add polltypes
	$q_st = "INSERT IGNORE INTO `msgs_new` 
	SELECT fm.`o_guid`, 0, fm.`skbranch`
	  FROM `forummessages` as fm
	  INNER JOIN `rightsmap_temp` as ro ON ro.`o_guid` COLLATE utf8mb4_unicode_ci = fm.`o_guid` COLLATE utf8mb4_unicode_ci
                  AND ro.`skbranch` = fm.`skbranch`
          INNER JOIN `pollingobjects` as po ON ro.`idobject` = po.`idobject` 
          INNER JOIN `useranswers` as ua ON po.idpollingobject = ua.idpollingobject AND ua.`iduser` IN (".$ids.")
          WHERE frmm_isdelete = 0 AND fm.`iduser` NOT IN (".$ids.") 
                  AND ro.`skbranch` != ".$mainskbranch." AND fm.`skbranch` != ".$mainskbranch."                  
          GROUP BY fm.`o_guid`, fm.`skbranch` ;";
        db_runQuery($cn, $q_st);

	$q_st = "UPDATE `msgs_new` INNER JOIN (
                SELECT fm.`o_guid`, fm.skbranch, count(fm.idforummessage) as cnt
                FROM `forummessages` as fm
                LEFT JOIN `forummessagessaw` as frmsw ON frmsw.iduser IN (".$ids.") AND fm.iduser <> frmsw.iduser AND fm.skbranch = frmsw.skbranch AND fm.o_guid = frmsw.o_guid 
                        /* AND frmsw.`frmsw_sessionhash` = '".getSessionSSKEY()."'  Task 64434 */
                        AND from_unixtime(frmsw.frmsw_datesaw_tmst) <= from_unixtime(fm.frmm_datechange_tmst)
                WHERE IF(frmsw.iduser IS NOT NULL, 1 , 0) = 1 AND fm.frmm_isdelete = 0
                GROUP BY fm.`o_guid`, fm.`skbranch`  ) q ON `msgs_new`.`o_guid` COLLATE utf8mb4_general_ci = q.`o_guid`  COLLATE utf8mb4_general_ci 
                AND `msgs_new`.`skbranch` = q.`skbranch` 
         SET `msgs_new`.`new_cnt` = q.cnt;";

	db_runQuery($cn, $q_st);

        $q_st = "SELECT SUM(`new_cnt`) as cnt FROM `msgs_new`;";
	$ar = db_runQueryAndReturnArray($cn,$q_st);
        if (is_array($ar) && sizeof($ar) > 0 && sizeof($ar[0]) > 0){
                return $ar[0]['cnt'];
        } else {
                return 0;
        }
}

function db_setForumMessageWasSaw($cn, $idforummessage, $resetAllPAgeTimes = FALSE){
        if (!isset($resetAllPAgeTimes) || !$resetAllPAgeTimes) {
                $q_st = "UPDATE `forummessagessaw` as frmsw
                INNER JOIN  `forummessages` as fm ON fm.skbranch = frmsw.skbranch 
                        AND fm.o_guid = frmsw.o_guid AND frmsw.`frmsw_sessionhash` = '".getSessionSSKEY()."' 
                        AND from_unixtime(frmsw.frmsw_datesaw_tmst) <= from_unixtime(fm.frmm_datechange_tmst)
                SET frmsw.frmsw_datesaw_tmst = fm.frmm_datechange_tmst + 1 
                WHERE fm.idforummessage = ".mysqli_real_escape_string($cn,$idforummessage)." ;";
        } else {
                //66190 Task 66195
                $q_st = "UPDATE `forummessagessaw` as frmsw
                INNER JOIN  `forummessages` as fm ON fm.skbranch = frmsw.skbranch 
                        AND fm.o_guid = frmsw.o_guid AND frmsw.`frmsw_sessionhash` = '".getSessionSSKEY()."'
                        AND from_unixtime(frmsw.frmsw_datesaw_tmst) <= from_unixtime(fm.frmm_datechange_tmst)
                SET frmsw.frmsw_datesaw_tmst = UNIX_TIMESTAMP(NOW())
                WHERE fm.idforummessage = ".mysqli_real_escape_string($cn,$idforummessage)." ;";
        }
	db_runQuery($cn, $q_st);
}

function db_getPollTypesList($cn, $idlanguage, $iduser)
{
        $query  = "SELECT DISTINCT pt.skpolltype, res.rft_columnname_text as `name` 
                        FROM `polltypes` pt 
                        INNER JOIN useranswers as ua_w ON pt.`skpolltype` = ua_w.`skpolltype`
                        INNER JOIN `users` u ON ua_w.iduser = u.iduser
                        INNER JOIN `resourcefromtables` res ON pt.skpolltype = res.idrecord
                        WHERE ua_w.idpollingobjectstatus = 1 AND res.idtable = 8 AND u.iduser = ".$iduser." AND res.idlanguage = ".$idlanguage." ORDER BY res.rft_columnname_text;";
                        // add ua.idpollingobjectstatus = 1 - Task 62715
        return db_runQueryAndReturnArray($cn, $query);
}

function db_getPollTypesListAdmin($cn, $idlanguage)
{
        $query  = "SELECT DISTINCT pt.skpolltype, res.rft_columnname_text as `name` 
                        FROM `polltypes` pt 
                        INNER JOIN `resourcefromtables` res ON pt.skpolltype = res.idrecord
                        INNER JOIN (SELECT DISTINCT skpolltype FROM useranswers) ua ON ua.skpolltype = pt.skpolltype
                        WHERE res.idtable = 8 AND res.idlanguage = ".$idlanguage." ORDER BY res.rft_columnname_text;";
        return db_runQueryAndReturnArray($cn, $query);
}


function db_getPollingObjectSource($cn, $skpollingobject)
{
        $query = " SELECT IF(IFNULL(skproject,0) < 1, 'skbranch', 'skproject') as source, IF(IFNULL(skproject,0) < 1, skbranch, skproject) as id
                FROM `pollingobjects` po WHERE  po.skpollingobject = ".$skpollingobject.";";
        $ar = db_runQueryAndReturnArray($cn, $query);
        return $ar;
}

function db_getPollingObjectsArchive($cn, $idpollingobjectarchive, $skpolltype, $iduser, $langid)
{
        db_setTimeZone($cn);
        $query = " SELECT poa.idpollingobjectarchive, poa.objectversionhash, uava.skpolltype, poa.objectversion, rfta_ver.rft_columnname_text as objectfullversion, 
                        DATE_FORMAT(from_unixtime(poa.objectversiondate_tmst),'%d.%m.%Y %H:%i') as objectversiondate ,
                        poa.o_guid, poa.r_guid, DATE_FORMAT(from_unixtime(uava.uav_tmst),'%d.%m.%Y %H:%i') as answerdate, uavat.text as answer
                        FROM `pollingobjectsarchive` poa 
                INNER JOIN `pollingobjects` po ON po.skpollingobject = poa.skpollingobject
                INNER JOIN `useranswers` ua_w ON po.`skpollingobject` = ua_w.`skpollingobject`  
                INNER JOIN `users` u ON ua_w.`iduser` = u.`iduser` 
                INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                LEFT JOIN `useranswersvaluesarchive` uava ON uava.`idpollingobjectarchive` = poa.`idpollingobjectarchive`  AND u.`iduser` = uava.`iduser`
                LEFT JOIN `useranswersvaluesarchivetext` uavat ON uavat.`idpollingobjectarchive` = poa.`idpollingobjectarchive`  AND u.`iduser` = uavat.`iduser` AND idlanguage = ".$langid."
                LEFT JOIN `resourcefromtablespollarchive` rfta_ver ON poa.idpollingobjectarchive = rfta_ver.idpollingobjectarchive AND poa.o_guid = rfta_ver.guidrecord AND rfta_ver.idtable = 1111 AND rfta_ver.idlanguage = ".$langid."            
                WHERE  u.`iduser` = ".$iduser." AND u.user_isactive = 1 
                AND pt.skpolltype = ".$skpolltype."
                AND poa.idpollingobjectarchive = ".$idpollingobjectarchive.";";
        $ar = db_runQueryAndReturnArray($cn, $query);
        return $ar;
}

function db_createPollingObjectsArchiveByOldVersion($cn, $idpollingobject,  $conditionChange, $isSaveFiles = FALSE, $deleteAnswers = TRUE)
{
        $query = 'SET @@session.time_zone = "+00:00"; ';
        db_runQuery($cn, $query);  
        $query  = "SELECT `skpollingobject` FROM `pollingobjects` WHERE `idpollingobject` = '".$idpollingobject."';";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if ($ar !== NULL && is_array($ar) && sizeof($ar) != 0 && sizeof($ar[0]) != 0 && $ar[0]['skpollingobject'] !== NULL){
                $skpollingobject = $ar[0]['skpollingobject'];
                $query  = "SELECT 1 as res FROM `pollingobjects` WHERE `skpollingobject` = '".$skpollingobject."' AND NOT (".$conditionChange.");";
                $ar = db_runQueryAndReturnArray($cn, $query);
                if (!is_array($ar) || sizeof($ar) == 0 || sizeof($ar[0])  == 0 || $ar[0]['res'] != 1){
                        return db_createPollingObjectsArchive($cn, $skpollingobject, null, $isSaveFiles, $deleteAnswers);   
                }
        }
        return NULL;
}

function db_deletePollingObjectsAnswersForNewVersion($cn, $idpollingobject,  $conditionChange )
{
        $query = 'SET @@session.time_zone = "+00:00"; ';
        db_runQuery($cn, $query);  
        $query  = "SELECT `skpollingobject` FROM `pollingobjects` WHERE `idpollingobject` = '".$idpollingobject."';";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if ($ar !== NULL && is_array($ar) && sizeof($ar) != 0 && sizeof($ar[0]) != 0 && $ar[0]['skpollingobject'] !== NULL){
                $skpollingobject = $ar[0]['skpollingobject'];
                $query  = "SELECT 1 as res FROM `pollingobjects` WHERE `skpollingobject` = '".$skpollingobject."' AND NOT (".$conditionChange.");";
                $ar = db_runQueryAndReturnArray($cn, $query);
                if (!is_array($ar) || sizeof($ar) == 0 || sizeof($ar[0])  == 0 || $ar[0]['res'] != 1){
                        $query  = "DELETE FROM `useranswersvalues` WHERE skpollingobject = ".$skpollingobject.";";
                        db_runQuery($cn, $query);  
                }
        }
}

function db_createPollingObjectsArchive($cn, $skpollingobject, $iduser, $isSaveFiles = FALSE, $deleteAnswers = FALSE)
{
        $query  = "SELECT `idpollingobjectarchive` FROM `pollingobjectsarchive` WHERE `objectversionhash` = (
                SELECT `objectversionhash` FROM `pollingobjects` WHERE skpollingobject = ".$skpollingobject.");";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (!is_array($ar) || sizeof($ar) == 0 || sizeof($ar[0])  == 0 || $ar[0]['idpollingobjectarchive'] === NULL){

                        $query  = "START TRANSACTION;";
                        db_runQuery($cn, $query);        

                        $query  = "BEGIN;";
                        db_runQuery($cn, $query);   
                        try{
                                $query  = "INSERT IGNORE INTO `pollingobjectsarchive` 
                                        SELECT 0, `skpollingobject`, `objectversion`, `objectversionhash`,  `objectversiondate_tmst`, `objectversionuhash`, `reportversion`, `reportversiondate_tmst`, `reportversionuhash`, ".($isSaveFiles ? '1' : '0').", `o_guid` , `r_guid` 
                                        FROM `pollingobjects` WHERE skpollingobject = ".$skpollingobject.";";
                                db_runQuery($cn, $query);        

                                $query  = " SET @idpollingobjectarchive = NULL; ";
                                db_runQuery($cn, $query);        

                                $query  = " SELECT @idpollingobjectarchive := MAX(idpollingobjectarchive) FROM `pollingobjectsarchive` WHERE  skpollingobject = ".$skpollingobject."; "; 
                                db_runQuery($cn, $query);        

                                if (isset($iduser)){
                                        $iduser_ep = mysqli_real_escape_string($cn,$iduser);
                                        $query  = "INSERT IGNORE INTO `useranswersvaluesarchive` 
                                                SELECT @idpollingobjectarchive, `skpolltype`, `iduser`, `uav_value`, `uav_body`, `idforummessage`, `uav_tmst` 
                                                        FROM `useranswersvalues` WHERE skpollingobject = ".$skpollingobject." AND iduser = ".$iduser_ep.";";
                                        db_runQuery($cn, $query);        
                
                                        $query  = "INSERT IGNORE INTO `useranswersvaluesarchivetext` 
                                                        SELECT @idpollingobjectarchive, pt.skpolltype, u.iduser, idlanguage, rft_columnname_text
                                                        FROM `pollingobjects` as po 
                                                        INNER JOIN `useranswers` as ua_w ON po.skpollingobject = ua_w.skpollingobject
                                                        INNER JOIN `polltypes` as pt ON ua_w.skpolltype = pt.skpolltype
                                                        INNER JOIN `useranswersvalues` as uav ON po.skpollingobject = uav.skpollingobject AND uav.skpolltype = pt.skpolltype
                                                        INNER JOIN `users` as u ON ua_w.iduser = u.iduser
                                                        INNER JOIN `polltypequestions` as pq ON pq.skpolltype = pt.skpolltype
                                                        INNER JOIN `resourcefromtables` as res_pq ON res_pq.idrecord = pq.skpolltypequestion AND idtable = 9
                                                        WHERE po.skpollingobject=  ".$skpollingobject."  AND pq.idpolltypequestiontype = 1  AND u.iduser = ".$iduser_ep.";";
                                        db_runQuery($cn, $query);  
                
                                        $query  = "INSERT IGNORE INTO `useranswersvaluesarchivetext` 
                                                        SELECT @idpollingobjectarchive, pt.skpolltype, u.iduser, idlanguage, rft_columnname_text
                                                        FROM `pollingobjects` as po 
                                                        INNER JOIN `useranswers` as ua_w ON po.skpollingobject = ua_w.skpollingobject
                                                        INNER JOIN `polltypes` as pt ON ua_w.skpolltype = pt.skpolltype
                                                        INNER JOIN `useranswersvalues` as uav ON po.skpollingobject = uav.skpollingobject AND uav.skpolltype = pt.skpolltype
                                                        INNER JOIN `users` as u ON ua_w.iduser = u.iduser
                                                        INNER JOIN `polltypequestions` as pq ON pq.skpolltype = pt.skpolltype
                                                        INNER JOIN `polltypequestionselectvalues` as qsv ON qsv.skpolltypequestion = pq.skpolltypequestion AND qsv.skpolltypequestionselectvalue = uav.uav_value
                                                        INNER JOIN `resourcefromtables` as res_qsv ON res_qsv.idrecord = qsv.skpolltypequestionselectvalue AND idtable = 90                    
                                                        WHERE po.skpollingobject=  ".$skpollingobject."  AND pq.idpolltypequestiontype = 2 AND u.iduser = ".$iduser_ep.";";
                                        db_runQuery($cn, $query); 
                                }
        
                                if($deleteAnswers) {
                                        $query  = "DELETE FROM `useranswersvalues` WHERE skpollingobject = ".$skpollingobject.";";
                                        db_runQuery($cn, $query);  
                                }

                                $query  = "SELECT @idpollingobjectarchive as `idpollingobjectarchive`;";                     
                                $ar = db_runQueryAndReturnArray($cn, $query);
                                $query  = "COMMIT;";
                                db_runQuery($cn, $query);        
                                return $ar[0]['idpollingobjectarchive'];
                        } 
                        catch (Exception $ex) {
                                $query  = "ROLLBACK;";
                                db_runQuery($cn, $query);        
                                logmessage("Error in  db_createPollingObjectsArchive : ".$ex->getMessage());
                                return NULL;
                        }

        } else {
                $idpollingobjectarchive = $ar[0]['idpollingobjectarchive'];
                $query  = "START TRANSACTION;";
                db_runQuery($cn, $query);        

                $query  = "BEGIN;";
                db_runQuery($cn, $query);   
                             
                try{

                        $query  = "DELETE FROM `resourcefromtablespollarchive` WHERE  idrecord = ".$ar[0]['idpollingobjectarchive']." AND idtable in (1103,1104);";
                        db_runQuery($cn, $query);        

                        $query  = "INSERT IGNORE INTO `resourcefromtablespollarchive` 
                                SELECT ".$ar[0]['idpollingobjectarchive'].", NULL, ".$ar[0]['idpollingobjectarchive'].", idtable + 2 , idlanguage, skbranch, rft_columnname_text, rft_columnname_long 
                                FROM `resourcefromtables` WHERE idrecord = ".$skpollingobject." AND idtable in (1101,1102,1111,2111);";
                        db_runQuery($cn, $query);        

                        if (isset($iduser)){
                                $iduser_ep = mysqli_real_escape_string($cn,$iduser);
                                $query  = "DELETE FROM `useranswersvaluesarchive` WHERE  idpollingobjectarchive = ".$ar[0]['idpollingobjectarchive'].";";
                                db_runQuery($cn, $query);        
        
                                $query  = "INSERT IGNORE INTO `useranswersvaluesarchive` 
                                                SELECT ".$ar[0]['idpollingobjectarchive'].", `skpolltype`, `iduser`, `uav_value`, `uav_body`, `idforummessage`, `uav_tmst` 
                                                FROM `useranswersvalues` WHERE skpollingobject = ".$skpollingobject." AND iduser = ".$iduser_ep.";";
                                db_runQuery($cn, $query);  
        
                                $query  = "DELETE FROM `useranswersvaluesarchivetext` WHERE  idpollingobjectarchive = ".$ar[0]['idpollingobjectarchive']." AND iduser = ".$iduser_ep.";";
                                db_runQuery($cn, $query);        
        
                                $query  = "INSERT IGNORE INTO `useranswersvaluesarchivetext` 
                                                SELECT ".$ar[0]['idpollingobjectarchive'].", pt.skpolltype, u.iduser, idlanguage, rft_columnname_text
                                                FROM `pollingobjects` as po 
                                                INNER JOIN `useranswers` as ua_w ON po.skpollingobject = ua_w.skpollingobject
                                                INNER JOIN `polltypes` as pt ON ua_w.skpolltype = pt.skpolltype
                                                INNER JOIN `useranswersvalues` as uav ON po.skpollingobject = uav.skpollingobject AND uav.skpolltype = pt.skpolltype
                                                INNER JOIN `users` as u ON ua_w.iduser = u.iduser
                                                INNER JOIN `polltypequestions` as pq ON pq.skpolltype = pt.skpolltype
                                                INNER JOIN `resourcefromtables` as res_pq ON res_pq.idrecord = pq.skpolltypequestion AND idtable = 9
                                                WHERE po.skpollingobject=  ".$skpollingobject."  AND pq.idpolltypequestiontype = 1 AND u.iduser = ".$iduser_ep.";";
                                db_runQuery($cn, $query);  
        
                                $query  = "INSERT IGNORE INTO `useranswersvaluesarchivetext` 
                                                SELECT ".$ar[0]['idpollingobjectarchive'].", pt.skpolltype, u.iduser, idlanguage, rft_columnname_text
                                                FROM `pollingobjects` as po 
                                                INNER JOIN `useranswers` as ua_w ON po.skpollingobject = ua_w.skpollingobject
                                                INNER JOIN `polltypes` as pt ON ua_w.skpolltype = pt.skpolltype
                                                INNER JOIN `useranswersvalues` as uav ON po.skpollingobject = uav.skpollingobject AND uav.skpolltype = pt.skpolltype
                                                INNER JOIN `users` as u ON ua_w.iduser = u.iduser
                                                INNER JOIN `polltypequestions` as pq ON pq.skpolltype = pt.skpolltype
                                                INNER JOIN `polltypequestionselectvalues` as qsv ON qsv.skpolltypequestion = pq.skpolltypequestion AND qsv.skpolltypequestionselectvalue = uav.uav_value
                                                INNER JOIN `resourcefromtables` as res_qsv ON res_qsv.idrecord = qsv.skpolltypequestionselectvalue AND idtable = 90                    
                                                WHERE po.skpollingobject=  ".$skpollingobject."  AND pq.idpolltypequestiontype = 2 AND u.iduser = ".$iduser_ep.";";
                                db_runQuery($cn, $query);  
                        }

                        if($deleteAnswers) {
                                $query  = "DELETE FROM `useranswersvalues` WHERE skpollingobject = ".$skpollingobject.";";
                                db_runQuery($cn, $query);        
                        }
                        $query  = "COMMIT;";
                        db_runQuery($cn, $query);      
                        return $idpollingobjectarchive;
                } 
                catch (Exception $ex) {
                        $query  = "ROLLBACK;";
                        db_runQuery($cn, $query);        
                        logmessage("Error in  db_createPollingObjectsArchive : ".$ex->getMessage());
                        return NULL;
                }
        }
}

function db_deletePollingObjectsArchivesBySetupDepth($cn, $skpollingobject){
        $archivedepth = getSetting('portal','polls.archivedepth', 30);  // 
        if (!is_numeric($archivedepth) || $archivedepth == 0 || $archivedepth < 0 ) {
		//глубина архива 0 - не удалять 
		return true;
        }
        
        $query  = "SELECT idpollingobjectarchive, objectversionhash FROM pollingobjectsarchive 
                WHERE skpollingobject = ".$skpollingobject."
                ORDER BY idpollingobjectarchive DESC
                LIMIT ".$archivedepth.",18446744073709551615;";

        $arr = db_runQueryAndReturnArray($cn, $query);
        for($i=0; $i<sizeof($arr); $i++){
                $query = "DELETE FROM useranswersvaluesarchive WHERE idpollingobjectarchive = ".$arr[$i]['idpollingobjectarchive'].";";
                db_runQuery($cn, $query); 
                $query = "DELETE FROM useranswersvaluesarchivetext WHERE idpollingobjectarchive = ".$arr[$i]['idpollingobjectarchive'].";";
                db_runQuery($cn, $query); 
                $query = "DELETE FROM resourcefromtablespollarchive WHERE idpollingobjectarchive = ".$arr[$i]['idpollingobjectarchive'].";";
                db_runQuery($cn, $query); 
                $query = "DELETE FROM pollingobjectsarchive WHERE idpollingobjectarchive = ".$arr[$i]['idpollingobjectarchive'].";";
                db_runQuery($cn, $query); 
        }
        return $arr;
}

function db_createPollingObjectsArchiveResourcesForObject($cn, $idpollingobjectarchive){
        $query  = "INSERT IGNORE INTO `resourcefromtablespollarchive` 
        SELECT idpollingobjectarchive, po.o_guid, rft.idrecord, rft.idtable , rft.idlanguage, rft.skbranch, rft.rft_columnname_text, rft.rft_columnname_long
          FROM `pollingobjectsarchive` as poa
          INNER JOIN `pollingobjects` as po USING(skpollingobject)
          INNER JOIN `objects` as o ON o.o_guid = po.o_guid AND o.skbranch = po.skbranch
          INNER JOIN `resourcefromtables` as rft ON po.skbranch = rft.skbranch AND o.idobject = rft.idrecord 
          WHERE poa.idpollingobjectarchive = ".$idpollingobjectarchive." AND rft.idtable in (1, 1101, 1111);";
        db_runQuery($cn, $query);   
}

function db_createPollingObjectsArchiveResourcesForReport($cn, $idpollingobjectarchive){
        $query  = "INSERT IGNORE INTO `resourcefromtablespollarchive` 
        SELECT idpollingobjectarchive, po.r_guid, rft.idrecord, rft.idtable , rft.idlanguage, rft.skbranch, rft.rft_columnname_text, rft.rft_columnname_long
          FROM `pollingobjectsarchive` as poa
          INNER JOIN `pollingobjects` as po USING(skpollingobject)
          INNER JOIN `reports` as r ON r.r_guid = po.r_guid AND r.skbranch = po.skbranch
          INNER JOIN `resourcefromtables` as rft ON po.skbranch = rft.skbranch AND r.idreport = rft.idrecord 
          WHERE poa.idpollingobjectarchive = ".$idpollingobjectarchive." AND rft.idtable in (2, 1102, 2111) ;";
        db_runQuery($cn, $query);   
}

function db_getPollingObjectsArchivesToDelete($cn, $skpollingobject, $idpolltype)
{
        $query  = "SELECT `idpollingobjectarchive`, `objectversionhash` FROM `pollingobjectsarchive` WHERE `skpollingobject` = ".$skpollingobject."
                AND `skpollingobject` NOT IN 
                        (SELECT po.skpollingobject 
                                FROM pollingobjects po 
                                INNER JOIN useranswers ua ON po.idpollingobject = ua.idpollingobject
                                WHERE po.skpollingobject = ".$skpollingobject." AND ua.idpolltype <> '".mysqli_real_escape_string($cn,$idpolltype)."');";
        return db_runQueryAndReturnArray($cn, $query);
}

function db_deletePollingObjectsArchive($cn, $skpollingobject, $idpolltype)
{
        $query  = "SELECT `skpolltype` FROM `polltypes` WHERE  `idpolltype` = '".mysqli_real_escape_string($cn,$idpolltype)."';";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (!is_array($ar) || sizeof($ar) == 0 || sizeof($ar[0])  == 0 || $ar[0]['skpolltype'] === NULL){
                return false;
        } else {
                $skpolltype = $ar[0]['skpolltype'];
        }
        $pobj = db_getPollingObject($cn, $skpollingobject);
        $idpollingobject = $pobj[0]['idpollingobject'];

        $query  = "SELECT `idpollingobjectarchive` FROM `pollingobjectsarchive` WHERE `skpollingobject` = ".$skpollingobject.";";
        $ar = db_runQueryAndReturnArray($cn, $query);
        for($p=0; $p<sizeof($ar); $p++){
                $query  = "START TRANSACTION;";
                db_runQuery($cn, $query);        

                $query  = "BEGIN;";
                db_runQuery($cn, $query);        
                try{
                        $query  = "DELETE FROM `useranswersvaluesarchive` WHERE  idpollingobjectarchive = ".$ar[$p]['idpollingobjectarchive']." AND skpolltype = ".$skpolltype.";";
                        db_runQuery($cn, $query);        

                        $query  = "DELETE FROM `useranswersvaluesarchivetext` WHERE  idpollingobjectarchive = ".$ar[$p]['idpollingobjectarchive']." AND skpolltype = ".$skpolltype.";";
                        db_runQuery($cn, $query);        

                        $query  = "DELETE FROM `pollingobjectsarchive` WHERE  skpollingobject = ".$skpollingobject." AND NOT EXISTS(SELECT 1 FROM `useranswers` WHERE `idpollingobject` = '".$idpollingobject."' AND `idpolltype` <> '".mysqli_real_escape_string($cn,$idpolltype)."');";
                        db_runQuery($cn, $query);        

                        $query  = "DELETE FROM `resourcefromtables` WHERE  idrecord = ".$ar[$p]['idpollingobjectarchive']." AND idtable in (1103,1104)  AND NOT EXISTS(SELECT 1 FROM `useranswers` WHERE `idpollingobject` = '".$idpollingobject."' AND `idpolltype` <> '".mysqli_real_escape_string($cn,$idpolltype)."');";
                        db_runQuery($cn, $query);        

                        $query  = "COMMIT;";
                        db_runQuery($cn, $query);      
      
                } 
                catch (Exception $ex) {
                        $query  = "ROLLBACK;";
                        db_runQuery($cn, $query);        
                        logmessage("Error in  db_deletePollingObjectsArchive : ".$ex->getMessage());
                        return NULL;
                }
        }
}

function db_deletePollingObjectAssignment($cn, $skbranch)
{
        $query  = "DELETE useranswers
                FROM useranswers
                INNER JOIN pollingobjects ON useranswers.skpollingobject = pollingobjects.skpollingobject
                WHERE pollingobjects.skbranch = ".$skbranch.";";
        db_runQuery($cn, $query);
}

function db_deletePollingObject($cn, $skpollingobject, $idpolltype)
{
        $query  = "SELECT `skpolltype` FROM `polltypes` WHERE  `idpolltype` = '".mysqli_real_escape_string($cn,$idpolltype)."';";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (!is_array($ar) || sizeof($ar) == 0 || sizeof($ar[0])  == 0 || $ar[0]['skpolltype'] === NULL){
                return FALSE;
        } else {
                $skpolltype = $ar[0]['skpolltype'];
        }
        $pobj = db_getPollingObject($cn, $skpollingobject);
        $skbranch = $pobj[0]['skbranch'];
        $idpollingobject = $pobj[0]['idpollingobject'];

        $query  = "START TRANSACTION;";
        db_runQuery($cn, $query);        

        $query  = "BEGIN;";
        db_runQuery($cn, $query);        
        try{
                $query  = "DELETE FROM `useranswersvalues` WHERE skpollingobject = ".$skpollingobject." AND skpolltype = ".$skpolltype.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `useranswers` WHERE idpollingobject = '".$idpollingobject."' AND `idpolltype` = '".mysqli_real_escape_string($cn,$idpolltype)."';";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `rightspollingobjects` WHERE skpollingobject = ".$skpollingobject."  AND NOT EXISTS(SELECT 1 FROM `useranswers` WHERE `idpollingobject` = '".$idpollingobject."' AND `idpolltype` <> '".mysqli_real_escape_string($cn,$idpolltype)."');";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `pollingobjects` WHERE skpollingobject = ".$skpollingobject."  AND NOT EXISTS(SELECT 1 FROM `useranswers` WHERE `idpollingobject` = '".$idpollingobject."' AND `idpolltype` <> '".mysqli_real_escape_string($cn,$idpolltype)."');";
                db_runQuery($cn, $query);        
                
                // 61571
                $query  = "DELETE FROM `resourcefromtables` WHERE idtable = 8 AND idrecord in ( SELECT skpolltype FROM `polltypes` WHERE `skpolltype` NOT IN (SELECT skpolltype FROM `pollingobjects`));";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `polltypequestions` WHERE skpolltype in ( SELECT skpolltype FROM `polltypes` WHERE `skpolltype` NOT IN (SELECT skpolltype FROM `pollingobjects`));";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `polltypes` WHERE `idpolltype` NOT IN (SELECT idpolltype FROM `useranswers`) AND `idpolltype` = '".mysqli_real_escape_string($cn,$idpolltype)."';"; // 62112
                db_runQuery($cn, $query);   
                             
                $query  = "DELETE FROM `projects` WHERE `skproject` NOT IN (SELECT skproject FROM `pollingobjects`) ;";// 62172
                db_runQuery($cn, $query);   

                $query  = "COMMIT;";
                db_runQuery($cn, $query);      

                $query  = "SELECT 1 as `res` FROM `pollingobjects` WHERE skbranch = ".$skbranch." LIMIT 1;";
                $ar = db_runQueryAndReturnArray($cn, $query);
                if (is_array($ar) && sizeof($ar) == 0 && db_getMainSKBranch($cn) != $skbranch){
                        db_deleteBranchData($cn, $skbranch);
                        db_clearBranch($cn, $skbranch); // 59956
                }                
                return TRUE;
        } 
        catch (Exception $ex) {
                $query  = "ROLLBACK;";
                db_runQuery($cn, $query);        
                logmessage("Error in  db_deletePollingObject : ".$ex->getMessage());
                return NULL;
        }
}

// 59957
function db_deleteBranchData($cn, $skbranch)
{
        $query  = "SELECT 1 as `res` FROM `branches` WHERE `skbranch` = '".mysqli_real_escape_string($cn,$skbranch)."' AND ismain = 1;";
        $ar = db_runQueryAndReturnArray($cn, $query);
        if (is_array($ar) && sizeof($ar) != 0 ){
                return FALSE;
        }
        $query  = "START TRANSACTION;";
        db_runQuery($cn, $query);        

        $query  = "BEGIN;";
        db_runQuery($cn, $query);        
        try{
                $query  = "DELETE FROM `avgreportdata` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `branches` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

//                $query  = "DELETE FROM `forummessages` WHERE skbranch = ".$skbranch.";";
//                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `forummessagessaw` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `objectlinks` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `objectsreports` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `personallinks` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `pollingobjects` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `reportdata` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `reportindex` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `reports` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `reportsusersquery` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `resourcefromtables` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `resourcefromtablespollarchive` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `rightsobjects` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `rightsreports` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `titleindex` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `objectsprohibitionrightscopy` WHERE idobject IN (SELECT idobject FROM `objects` WHERE skbranch = ".$skbranch.");";
                db_runQuery($cn, $query);        

                $query  = "DELETE FROM `objects` WHERE skbranch = ".$skbranch.";";
                db_runQuery($cn, $query);        

                $query  = "COMMIT;";
                db_runQuery($cn, $query);      
        } 
        catch (Exception $ex) {
                $query  = "ROLLBACK;";
                db_runQuery($cn, $query);        
                logmessage("Error in  db_deleteBranchData : ".$ex->getMessage());
                return NULL;
        }
}

function db_createObjectResources($cn, $contlngid){
        db_dropTempTable($cn, 'temp_resource');
        $q_st = "    CREATE TEMPORARY TABLE `temp_resource` (
                `idrecord` BIGINT NOT NULL PRIMARY KEY,
                `rft_columnname_text` VARCHAR(2500)  COLLATE utf8mb4_unicode_ci NOT NULL
                )ENGINE = MyISAM, COLLATE = utf8mb4_general_ci;";
        db_runQuery($cn, $q_st);
                
        $q_st = " INSERT INTO `temp_resource` SELECT idrecord, rft_columnname_text FROM `resourcefromtables` WHERE `idtable` = 1 AND idlanguage = ".$contlngid." ; ";
        db_runQuery($cn, $q_st);
}

function db_getPollObjetcList($cn, $idpolltype, $skproject, $skbranch, $contlngid, $usr_arr, $answers = NULL, $fillResources = TRUE ){
        try{
                db_prepareSQLBIGSELECT($cn);
                db_dropTempTable($cn, 'poll');
                $q_st = "    CREATE TEMPORARY TABLE `poll` (
                        `skpollingobject` INT NOT NULL,
                        `skpolltype` INT NOT NULL,
                        `skbranch` INT NOT NULL,
                        `branch_name` varchar(2500)  COLLATE utf8mb4_unicode_ci NOT NULL,
                        `objectversion` varchar(250)  COLLATE utf8mb4_unicode_ci NOT NULL,
                        `objectfullversion` varchar(250)  COLLATE utf8mb4_unicode_ci NOT NULL,
                        `objectversiondate_tmst` INT NOT NULL,
                        `o_guid` VARCHAR(40) COLLATE utf8mb4_unicode_ci  NOT NULL,
                        `o_name` varchar(2500) COLLATE utf8mb4_unicode_ci ,
                        `r_guid` VARCHAR(40)  COLLATE utf8mb4_unicode_ci NULL,
                        `r_name` varchar(2500) COLLATE utf8mb4_unicode_ci ,
                        `idobject` BIGINT,
                        `idreport` INT,
                        `hasanswer` TINYINT,
                        `o_icon` VARCHAR(800)
                        )ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;";
                db_runQuery($cn, $q_st);

		if (isset($usr_arr)) {
                        db_createUsersIDsTempTable($cn, $usr_arr);
			$q_st = "INSERT IGNORE INTO `poll`
					SELECT DISTINCT po.`skpollingobject`, pt.`skpolltype`, po.`skbranch`, res.rft_columnname_text as branch_name, po.`objectversion`, '' as objectfullversion, po.`objectversiondate_tmst`, po.`o_guid`, '' as `o_name`, po.`r_guid`, '' as `r_name`, po.`idobject`, 0 as `idreport`, ua_w.hasanswer, o.`o_icon`
					FROM `pollingobjects` po 
                                        INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject`  
					INNER JOIN `objects` o ON po.`idobject` = o.`idobject`
					INNER JOIN `users` u ON ua_w.`iduser` = u.`iduser` AND u.user_isactive = 1 
                                        INNER JOIN userids_t ut ON ut.`iduser` = u.`iduser` 
					INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
					LEFT JOIN `resourcefromtables` as res ON po.`skbranch`  = res.`idrecord` AND res.`idtable` = 7 
					WHERE  pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."'
					AND res.idlanguage = ".$contlngid."";
                } else {
                        $q_st = "INSERT IGNORE INTO `poll`
                                SELECT DISTINCT po.`skpollingobject`, pt.`skpolltype`, po.`skbranch`, res.rft_columnname_text as branch_name, po.`objectversion`, '' as objectfullversion, po.`objectversiondate_tmst`, po.`o_guid`, '' as `o_name`, po.`r_guid`, '' as `r_name`, po.`idobject`, 0 as `idreport`, ua_w.hasanswer, o.`o_icon`
                                FROM `pollingobjects` po 
                                INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject`  
				INNER JOIN `objects` o ON po.`idobject` = o.`idobject`
				INNER JOIN `users` u ON ua_w.`iduser` = u.`iduser` AND u.user_isactive = 1  
				INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                                LEFT JOIN `resourcefromtables` as res ON po.`skbranch`  = res.`idrecord` AND res.`idtable` = 7 
                                WHERE pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."'
                                AND res.idlanguage = ".$contlngid."";
                }
                if (isset($skproject)) {
                        $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                } else {
                        $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                }

                if (isset($answers) ){
                        if ($answers == 'hasnot' ){
                                $q_st .= " AND  ua_w.`hasanswer` = 0 ";
                        }
                        if ($answers == 'has' ){
                                $q_st .= " AND  ua_w.`hasanswer` = 1 ";
                        }
                }
                db_runQuery($cn, $q_st);

                $q_st = "CREATE INDEX ix1 ON `poll` (idobject);";
                db_runQuery($cn, $q_st);

                if (isset($fillResources) && $fillResources) {

                        db_createObjectResources($cn, $contlngid);
                        $q_st = "UPDATE `poll` INNER JOIN `temp_resource` q 
                                        ON  `poll`.`idobject` = q.`idrecord`
                                SET `poll`.`o_name` = q.rft_columnname_text;";
                        db_runQuery($cn, $q_st);

                        $q_st = "UPDATE `poll` INNER JOIN `resourcefromtables` as res ON  `skpollingobject` = `idrecord` AND `idtable` = 1111 AND idlanguage = ".$contlngid."  
                                SET `objectfullversion` = rft_columnname_text;";
                        db_runQuery($cn, $q_st);
                        
                        $q_st = "UPDATE `poll`
                                        INNER JOIN objectsreports as obrp ON `poll`.idobject = obrp.idobject
                                        INNER JOIN `reports` as r ON obrp.idreport = r.idreport AND `poll`.`r_guid` COLLATE utf8mb4_general_ci = r.`r_guid` COLLATE utf8mb4_general_ci AND (ifnull(r.idlanguage, ".$contlngid.") = ".$contlngid." OR r.idlanguage = 0)  
                                        INNER JOIN `resourcefromtables` as res ON r.`idreport` = res.`idrecord` AND res.`idtable` = 2 AND res.idlanguage = ".$contlngid."  
                                SET `poll`.`r_name` = rft_columnname_text, `poll`.`idreport` = r.`idreport` ;";
                        db_runQuery($cn, $q_st);
                }
        } 
        catch (Exception $ex) {
                logmessage("Error db_getPollObjetcList : ".$ex->getMessage());
                return NULL;
        }
}

function db_getPollObjetcListCut($cn, $idpolltype, $skproject, $skbranch, $usr_arr,  $answers = NULL ){
        try{
                db_prepareSQLBIGSELECT($cn);

                $q_st = "DROP TEMPORARY TABLE IF EXISTS `poll` ;";
                db_runQuery($cn, $q_st);
                
                $q_st = "    CREATE TEMPORARY TABLE `poll` (
                        `skpollingobject` INT NOT NULL,
                        `skpolltype` INT NOT NULL,
                        `skbranch` INT NOT NULL
                        )ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;";
                db_runQuery($cn, $q_st);

		if (isset($usr_arr)) {
			db_createUsersIDsTempTable($cn, $usr_arr);
			$q_st = "INSERT IGNORE INTO `poll`
					SELECT DISTINCT po.`skpollingobject`, pt.`skpolltype`, po.`skbranch`
					FROM `pollingobjects` po 
                                        INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject`  
					INNER JOIN `users` u ON ua_w.`iduser` = u.`iduser`  AND u.user_isactive = 1
                                        INNER JOIN userids_t ut ON ut.`iduser` = u.`iduser` 
					INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`"
                                        .(isset($answers) && ($answers == 'has' || $answers) ? " INNER JOIN `useranswersvalues` as uav ON uav.iduser = u.iduser AND uav.skpollingobject = po.skpollingobject AND  uav.skpolltype = pt.skpolltype " : "")
                                        ." 
                                        WHERE pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."'";
                } else {
                        $q_st = "INSERT IGNORE INTO `poll`
                                SELECT DISTINCT po.`skpollingobject`, pt.`skpolltype`, po.`skbranch`
	                        FROM `pollingobjects` po 
                                INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject`  
				INNER JOIN `users` u ON ua_w.`iduser` = u.`iduser` 
				INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`"
                                        .(isset($answers) && ($answers == 'has' || $answers) ? " INNER JOIN `useranswersvalues` as uav ON uav.iduser = u.iduser AND uav.skpollingobject = po.skpollingobject AND  uav.skpolltype = pt.skpolltype " : "")
                                ." 
                                WHERE  u.user_isactive = 1 
                                AND pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."'";
                }
                if (isset($skproject)) {
                        $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                } else {
                        $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                }
                db_runQuery($cn, $q_st);

        } 
        catch (Exception $ex) {
                logmessage("Error db_getPollObjetcListCut : ".$ex->getMessage());
                return NULL;
        }
}


function db_getPollObjetcListAdmin($cn, $idpolltype, $skproject, $skbranch, $contlngid, $usr_arr, $calcProgress = TRUE, $answers = NULL, $fillResources = NULL)
{
	try{
		db_prepareSQLBIGSELECT($cn);
                db_dropTempTable($cn, 'poll');

                $q_st = "    
                CREATE TEMPORARY TABLE `poll` (
				`idpollingobject` varchar(250) COLLATE utf8mb4_unicode_ci  NOT NULL,
				`skpollingobject` INT NOT NULL,
				`skpolltype` INT NOT NULL,
				`idpolltype` varchar(250)  COLLATE utf8mb4_unicode_ci NOT NULL,
				`skbranch` INT NOT NULL,
				`branch_name` varchar(2500)  COLLATE utf8mb4_unicode_ci NOT NULL,
				`objectversion` varchar(250)  COLLATE utf8mb4_unicode_ci NOT NULL,
				`objectversiondate_tmst` INT NOT NULL,
				`o_guid` VARCHAR(40) COLLATE utf8mb4_unicode_ci  NOT NULL,
				`o_name` varchar(2500) COLLATE utf8mb4_unicode_ci ,
				`r_guid` VARCHAR(40)  COLLATE utf8mb4_unicode_ci NULL,
				`r_name` varchar(2500) COLLATE utf8mb4_unicode_ci ,
				`idobject` BIGINT,
				`idreport` INT,
				`o_icon` VARCHAR(800),
				`progress` INT NOT NULL,
		                `progress_of` INT NOT NULL,
				`progress_percent` INT NOT NULL,
                                PRIMARY KEY (`skpollingobject`, `skpolltype`),
                                INDEX `skpollingobject` (`skpollingobject`),
                                INDEX `idpollingobject` (`idpollingobject`),
                                INDEX `skbranch` (`skbranch`),
                                INDEX `idpolltype` (`idpolltype`)                                          
				)ENGINE=MYISAM, COLLATE = utf8mb4_general_ci;";
		db_runQuery($cn, $q_st);
		if (isUserAdmin()) {
                        $q_st = "INSERT IGNORE INTO `poll`
                                        SELECT DISTINCT po.`idpollingobject`, po.`skpollingobject`, pt.`skpolltype`, pt.`idpolltype`, po.`skbranch`, res.rft_columnname_text as branch_name, po.`objectversion`, po.`objectversiondate_tmst`, po.`o_guid`, '' as `o_name`, po.`r_guid`, '' as `r_name`, po.`idobject`, 0 as `idreport`, o.`o_icon`, 0 as `progress`, 0 as `progress_of`, 0 `progress_percent` 
                                        FROM `pollingobjects` po 
                                        INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject`
                                        INNER JOIN `objects` o ON po.`idobject` = o.`idobject`
                                        INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                                        LEFT JOIN `resourcefromtables` as res ON po.`skbranch` = res.`idrecord` AND res.`idtable` = 7 
                                        WHERE pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."'
                                        AND res.idlanguage = ".$contlngid."";
                        if (isset($skproject)) {
                                $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                        } else {
                                $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                        }	 
                        if (isset($answers) ){
                                if ($answers == 'hasnot' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 0 ";
                                }
                                if ($answers == 'has' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 1 ";
                                }
                        }
                        db_runQuery($cn, $q_st);

                } else {
                        db_createUsersIDsTempTable($cn, $usr_arr);
                        $q_st = "INSERT IGNORE INTO `poll`
                                        SELECT DISTINCT po.`idpollingobject`, po.`skpollingobject`, pt.`skpolltype`, pt.`idpolltype`, po.`skbranch`, res.rft_columnname_text as branch_name, po.`objectversion`, po.`objectversiondate_tmst`, po.`o_guid`, '' as `o_name`, po.`r_guid`, '' as `r_name`, po.`idobject`, 0 as `idreport`, o.`o_icon`, 0 as `progress`, 0 as `progress_of`, 0 `progress_percent` 
                                        FROM `pollingobjects` po 
                                        INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject`
                                        INNER JOIN `objects` o ON po.`idobject` = o.`idobject` 
                                        INNER JOIN `rightspollingobjects` u ON po.`skpollingobject` = u.`skpollingobject` AND u.deny_allow = 1 
                                        INNER JOIN userids_t ut ON u.`iduser` = ut.`iduser` 
                                        INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                                        LEFT JOIN `resourcefromtables` as res ON po.`skbranch` = res.`idrecord` AND res.`idtable` = 7 
                                        WHERE  pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."'
                                        AND res.idlanguage = ".$contlngid."";

                        if (isset($skproject)) {
                                $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                        } else {
                                $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                        }	 
                        if (isset($answers) ){
                                if ($answers == 'hasnot' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 0 ";
                                }
                                if ($answers == 'has' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 1 ";
                                }
                        }
                        db_runQuery($cn, $q_st);

                        $q_st = "INSERT IGNORE INTO `poll`
                                        SELECT DISTINCT po.`idpollingobject`, po.`skpollingobject`, pt.`skpolltype`, pt.`idpolltype`, po.`skbranch`, res.rft_columnname_text as branch_name, po.`objectversion`, po.`objectversiondate_tmst`, po.`o_guid`, '' as `o_name`, po.`r_guid`, '' as `r_name`, po.`idobject`, 0 as `idreport`, o.`o_icon`, 0 as `progress`, 0 as `progress_of`, 0 `progress_percent` 
                                        FROM `pollingobjects` po 
                                        INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject`
                                        INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                                        INNER JOIN `rightsadminpollingobjects` u ON pt.`skpolltype` = u.`skpolltype` AND u.deny_allow = 1 AND CASE WHEN ( u.`skproject` IS NULL or u.`skproject` = 0 ) THEN po.`skbranch` = u.`skbranch` ELSE po.`skproject` = u.`skproject` END
                                        INNER JOIN userids_t ut ON u.`iduser` = ut.`iduser` 
                                        INNER JOIN `objects` o ON po.`idobject` = o.`idobject` 
                                        LEFT JOIN `resourcefromtables` as res ON po.`skbranch` = res.`idrecord` AND res.`idtable` = 7 
                                        WHERE  pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."'
                                        AND res.idlanguage = ".$contlngid."";

                        if (isset($skproject)) {
                                $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                        } else {
                                $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                        }	
                        if (isset($answers) ){
                                if ($answers == 'hasnot' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 0 ";
                                }
                                if ($answers == 'has' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 1 ";
                                }
                        }
                        db_runQuery($cn, $q_st);     
                }

                
                if (isset($calcProgress) && $calcProgress) {
                        db_dropTempTable($cn, 'polllimiter');

                        $q_st = "    
                        CREATE TEMPORARY TABLE `polllimiter` (
                                        `skpollingobject` INT NOT NULL,
                                        `skpolltype` INT NOT NULL,
                                        INDEX `skpollingobject` (`skpollingobject`),
                                        INDEX `skpolltype` (`skpolltype`)                                          
                                        )ENGINE=MYISAM, COLLATE = utf8mb4_general_ci;";
                        db_runQuery($cn, $q_st);
                        $q_st = "  INSERT INTO  `polllimiter` 
                                        SELECT DISTINCT `skpollingobject`, `skpolltype` FROM `poll`;";
                        db_runQuery($cn, $q_st);
                        $q_st = "  
                                UPDATE `poll`
                                INNER JOIN (
                                        SELECT `useranswers`.skpollingobject, `useranswers`.skpolltype, COUNT(0) as progress_of, SUM(`useranswers`.hasanswer) as progress 
                                        FROM `useranswers` 
                                        INNER JOIN `polllimiter` ON `useranswers`.skpollingobject = `polllimiter`.skpollingobject AND `useranswers`.skpolltype = `polllimiter`.skpolltype
                                        group BY `useranswers`.skpollingobject, `useranswers`.skpolltype
                                ) q ON `poll`.skpollingobject = q.skpollingobject AND `poll`.skpolltype = q.skpolltype
                                SET `poll`.progress_of = q.progress_of,  `poll`.progress = q.progress ;";
                        db_runQuery($cn, $q_st);

                        $q_st = "UPDATE `poll` SET progress_percent = IFNULL( ROUND(IFNULL(progress,0) * 1.0 / (IFNULL(progress_of,0) * 1.0 / 100.0) , 0) , 0) ;";
                        db_runQuery($cn, $q_st);

                }
                
                if (isset($fillResources) && $fillResources) {
                        db_createObjectResources($cn, $contlngid);
                        $q_st = "UPDATE `poll` INNER JOIN `temp_resource` q 
                                     ON  `poll`.`idobject` = q.`idrecord`
                                    SET `poll`.`o_name` = q.rft_columnname_text;";
                        db_runQuery($cn, $q_st);
                        $q_st = "UPDATE `poll` INNER JOIN `resourcefromtables` as res ON  `skpollingobject` = `idrecord` AND `idtable` = 1111 AND idlanguage = ".$contlngid."  
                                        SET `objectversion` = rft_columnname_text;";
                        db_runQuery($cn, $q_st);

                        $q_st = "UPDATE `poll`
                                        INNER JOIN objectsreports as obrp ON `poll`.idobject = obrp.idobject
                                        INNER JOIN `reports` as r ON obrp.idreport = r.idreport AND `poll`.`r_guid` COLLATE utf8mb4_general_ci = r.`r_guid` COLLATE utf8mb4_general_ci AND (ifnull(r.idlanguage, ".$contlngid.") = ".$contlngid." OR r.idlanguage = 0)  
                                        INNER JOIN `resourcefromtables` as res ON r.`idreport` = res.`idrecord` AND res.`idtable` = 2 AND res.idlanguage = ".$contlngid."  
                                SET `poll`.`r_name` = rft_columnname_text, `poll`.`idreport` = r.`idreport` ;";
                        db_runQuery($cn, $q_st);
                }

	} 
	catch (Exception $ex) {
			logmessage("Error in db_getPollObjetcListAdmin : ".$ex->getMessage());
			return NULL;
	}
}

function db_getPollsAnswerByRespondentAdmin($cn, $usr_arr, $iduserrespondent, $contlngid, $idpolltype = NULL, $skproject = NULL, $skbranch = NULL, $answers = NULL)
{
        try{
                db_prepareSQLBIGSELECT($cn);
                db_dropTempTable($cn, 'poll');
                $q_st = "    CREATE TEMPORARY TABLE `poll` (
                        `skpollingobject` INT NOT NULL,
                        `skpolltype` INT NOT NULL,
                        `skbranch` INT NOT NULL,
                        `branch_name` varchar(2500)  COLLATE utf8mb4_unicode_ci NOT NULL,
                        `objectversion` varchar(250)  COLLATE utf8mb4_unicode_ci NOT NULL,
                        `objectfullversion` varchar(250)  COLLATE utf8mb4_unicode_ci NOT NULL,
                        `objectversiondate_tmst` INT NOT NULL,
                        `o_guid` VARCHAR(40) COLLATE utf8mb4_unicode_ci  NOT NULL,
                        `o_name` varchar(2500) COLLATE utf8mb4_unicode_ci ,
                        `r_guid` VARCHAR(40)  COLLATE utf8mb4_unicode_ci NULL,
                        `r_name` varchar(2500) COLLATE utf8mb4_unicode_ci ,
                        `idobject` BIGINT,
                        `idreport` INT,
                        `hasanswer` TINYINT,
                        `o_icon` VARCHAR(800),
                        `iduser` INT
                        )ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;";
                db_runQuery($cn, $q_st);
                $skpolltype = db_getSKPollType($cn, $idpolltype);
		if (isUserAdmin() || db_isUserAdminToPoll($cn, $usr_arr, $skpolltype, $skproject , $skbranch))  {
			$q_st = "INSERT IGNORE INTO `poll`
					SELECT DISTINCT po.`skpollingobject`, pt.`skpolltype`, po.`skbranch`, res.rft_columnname_text as branch_name, po.`objectversion`, '' as objectfullversion, po.`objectversiondate_tmst`, po.`o_guid`, '' as `o_name`, po.`r_guid`, '' as `r_name`, po.`idobject`, 0 as `idreport`, ua_w.hasanswer, o.`o_icon`, ua_w.`iduser`
					FROM `pollingobjects` po 
                                        INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject`  
					INNER JOIN `objects` o ON po.`idobject` = o.`idobject`
					INNER JOIN `users` u ON ua_w.`iduser` = u.`iduser` AND u.user_isactive = 1 
					INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
					LEFT JOIN `resourcefromtables` as res ON po.`skbranch`  = res.`idrecord` AND res.`idtable` = 7 
					WHERE  u.`iduser` = ".mysqli_real_escape_string($cn,$iduserrespondent)." AND pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."'
					AND res.idlanguage = ".$contlngid."";
                                        if (isset($skproject)) {
                                                $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                                        } else {
                                                $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                                        }

                        if (isset($answers) ){
                                if ($answers == 'hasnot' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 0 ";
                                }
                                if ($answers == 'has' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 1 ";
                                }
                        }
                        db_runQuery($cn, $q_st);
                } else {            
                        db_createUsersIDsTempTable($cn, $usr_arr);
                        $q_st = "INSERT IGNORE INTO `poll`
					SELECT DISTINCT po.`skpollingobject`, pt.`skpolltype`, po.`skbranch`, res.rft_columnname_text as branch_name, po.`objectversion`, '' as objectfullversion, po.`objectversiondate_tmst`, po.`o_guid`, '' as `o_name`, po.`r_guid`, '' as `r_name`, po.`idobject`, 0 as `idreport`, ua_w.hasanswer, o.`o_icon`, ua_w.`iduser`
					FROM `pollingobjects` po 
                                        INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject`  
					INNER JOIN `objects` o ON po.`idobject` = o.`idobject`
					INNER JOIN `users` u ON ua_w.`iduser` = u.`iduser` AND u.user_isactive = 1 
                                        INNER JOIN `rightspollingobjects` urpo ON po.`skpollingobject` = urpo.`skpollingobject` AND urpo.deny_allow = 1 
                                        INNER JOIN userids_t ut ON urpo.`iduser` = ut.`iduser` 
					INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
					LEFT JOIN `resourcefromtables` as res ON po.`skbranch`  = res.`idrecord` AND res.`idtable` = 7 
					WHERE  u.`iduser` = ".mysqli_real_escape_string($cn,$iduserrespondent)." AND pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."'
					AND res.idlanguage = ".$contlngid."";
                                        if (isset($skproject)) {
                                                $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                                        } else {
                                                $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                                        }

                        if (isset($answers) ){
                                if ($answers == 'hasnot' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 0 ";
                                }
                                if ($answers == 'has' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 1 ";
                                }
                        }
                        db_runQuery($cn, $q_st);                                        
                }

                $q_st = "CREATE INDEX ix1 ON `poll` (idobject);";
                db_runQuery($cn, $q_st);

                db_createObjectResources($cn, $contlngid);
                $q_st = "UPDATE `poll` INNER JOIN `temp_resource` q 
                                ON  `poll`.`idobject` = q.`idrecord`
                        SET `poll`.`o_name` = q.rft_columnname_text;";
                db_runQuery($cn, $q_st);

                $q_st = "UPDATE `poll` INNER JOIN `resourcefromtables` as res ON  `skpollingobject` = `idrecord` AND `idtable` = 1111 AND idlanguage = ".$contlngid."  
                        SET `objectfullversion` = rft_columnname_text;";
                db_runQuery($cn, $q_st);
                        
                $q_st = "UPDATE `poll`
                                INNER JOIN objectsreports as obrp ON `poll`.idobject = obrp.idobject
                                INNER JOIN `reports` as r ON obrp.idreport = r.idreport AND `poll`.`r_guid` COLLATE utf8mb4_general_ci = r.`r_guid` COLLATE utf8mb4_general_ci AND (ifnull(r.idlanguage, ".$contlngid.") = ".$contlngid." OR r.idlanguage = 0)  
                                INNER JOIN `resourcefromtables` as res ON r.`idreport` = res.`idrecord` AND res.`idtable` = 2 AND res.idlanguage = ".$contlngid."  
                        SET `poll`.`r_name` = rft_columnname_text, `poll`.`idreport` = r.`idreport` ;";
                db_runQuery($cn, $q_st);
        } 
        catch (Exception $ex) {
                logmessage("Error db_getPollsAnswerByRespondentAdmin : ".$ex->getMessage());
                return NULL;
        }
}


function db_getPollsAnswerByRespondentForDetailsAdmin($cn, $usr_arr, $iduserrespondent, $contlngid, $idpolltype = NULL, $skproject = NULL, $skbranch = NULL, $answers = NULL)
{
	try{
		db_prepareSQLBIGSELECT($cn);
                db_dropTempTable($cn, 'poll');


		$q_st = "    CREATE TEMPORARY TABLE `poll` (
				`idpollingobject` varchar(250) COLLATE utf8mb4_unicode_ci  NOT NULL,
				`skpollingobject` INT NOT NULL,
				`skpolltype` INT NOT NULL,
				`idpolltype` varchar(250)  COLLATE utf8mb4_unicode_ci NOT NULL,
				`skbranch` INT NOT NULL,
				`branch_name` varchar(2500)  COLLATE utf8mb4_unicode_ci NOT NULL,
				`objectversion` varchar(250)  COLLATE utf8mb4_unicode_ci NOT NULL,
				`objectversiondate_tmst` INT NOT NULL,
				`o_guid` VARCHAR(40) COLLATE utf8mb4_unicode_ci  NOT NULL,
				`o_name` varchar(2500) COLLATE utf8mb4_unicode_ci ,
				`r_guid` VARCHAR(40)  COLLATE utf8mb4_unicode_ci NULL,
				`r_name` varchar(2500) COLLATE utf8mb4_unicode_ci ,
				`idobject` BIGINT,
				`idreport` INT,
				`o_icon` VARCHAR(800),
				`progress` INT NOT NULL,
		                `progress_of` INT NOT NULL,
				`progress_percent` INT NOT NULL,
                                PRIMARY KEY (`skpollingobject`),
                		UNIQUE INDEX `idpollingobject` (`idpollingobject`),			
				INDEX `skbranch` (`skbranch`),
				INDEX `idpolltype` (`idpolltype`),                                            
				INDEX `skpolltype` (`skpolltype`)                                            
				)ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;";
                db_runQuery($cn, $q_st);
                if (isUserAdmin()) {
                        $q_st = "INSERT IGNORE INTO `poll`
                                        SELECT DISTINCT po.`idpollingobject`, po.`skpollingobject`, pt.`skpolltype`, pt.`idpolltype`, po.`skbranch`, res.rft_columnname_text as branch_name, po.`objectversion`, po.`objectversiondate_tmst`, po.`o_guid`, '' as `o_name`, po.`r_guid`, '' as `r_name`, po.`idobject`, 0 as `idreport`, o.`o_icon`, ua_w.`hasanswer` as `progress`,1 as `progress_of`, 0 `progress_percent` 
                                        FROM `pollingobjects` po 
                                        INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject` AND ua_w.iduser = ".$iduserrespondent."
                                        INNER JOIN `objects` o ON po.`idobject` = o.`idobject`
                                        INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                                        LEFT JOIN `resourcefromtables` as res ON po.`skbranch` = res.`idrecord` AND res.`idtable` = 7 
                                        WHERE res.idlanguage = ".$contlngid." ";
                        if (isset($idpolltype)) {
                                $q_st .= " AND pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."' ";
                        }                
                        if (isset($skproject)) {
                                $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                        } else if (isset($skbranch)) {
                                $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                        }	
                        if (isset($answers) ){
                                if ($answers == 'hasnot' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 0 ";
                                }
                                if ($answers == 'has' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 1 ";
                                }
                        }      
                        db_runQuery($cn, $q_st);
                } else {                
                        db_createUsersIDsTempTable($cn, $usr_arr);
                        $q_st = "INSERT IGNORE INTO `poll`
                                        SELECT DISTINCT po.`idpollingobject`, po.`skpollingobject`, pt.`skpolltype`, pt.`idpolltype`, po.`skbranch`, res.rft_columnname_text as branch_name, po.`objectversion`, po.`objectversiondate_tmst`, po.`o_guid`, '' as `o_name`, po.`r_guid`, '' as `r_name`, po.`idobject`, 0 as `idreport`, o.`o_icon`, ua_w.`hasanswer` as `progress`,1 as `progress_of`, 0 `progress_percent` 
                                        FROM `pollingobjects` po 
                                        INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject` AND ua_w.iduser = ".$iduserrespondent."
                                        INNER JOIN `objects` o ON po.`idobject` = o.`idobject`
                                        INNER JOIN `rightspollingobjects` u ON po.`skpollingobject` = u.`skpollingobject`  AND u.deny_allow = 1 
                                        INNER JOIN userids_t ut ON u.`iduser` = ut.`iduser` 
                                        INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                                        LEFT JOIN `resourcefromtables` as res ON po.`skbranch` = res.`idrecord` AND res.`idtable` = 7 
                                        WHERE  res.idlanguage = ".$contlngid." ";
                        if (isset($idpolltype)) {
                                $q_st .= " AND pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."' ";
                        }                
                        if (isset($skproject)) {
                                $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                        } else if (isset($skbranch)) {
                                $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                        }	 
                        if (isset($answers) ){
                                if ($answers == 'hasnot' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 0 ";
                                }
                                if ($answers == 'has' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 1 ";
                                }
                        }      
                        db_runQuery($cn, $q_st);

                        $q_st = "INSERT IGNORE INTO `poll`
                                        SELECT DISTINCT po.`idpollingobject`, po.`skpollingobject`, pt.`skpolltype`, pt.`idpolltype`, po.`skbranch`, res.rft_columnname_text as branch_name, po.`objectversion`, po.`objectversiondate_tmst`, po.`o_guid`, '' as `o_name`, po.`r_guid`, '' as `r_name`, po.`idobject`, 0 as `idreport`, o.`o_icon`, ua_w.`hasanswer` as `progress`,1 as `progress_of`, 0 `progress_percent` 
                                        FROM `pollingobjects` po 
                                        INNER JOIN useranswers as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject` AND ua_w.iduser = ".$iduserrespondent."
                                        INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                                        INNER JOIN `rightsadminpollingobjects` u ON pt.`skpolltype` = u.`skpolltype` AND u.deny_allow = 1 AND CASE WHEN ( u.`skproject` IS NULL or u.`skproject` = 0 ) THEN po.`skbranch` = u.`skbranch` ELSE po.`skproject` = u.`skproject` END
                                        INNER JOIN userids_t ut ON u.`iduser` = ut.`iduser` 
                                        INNER JOIN `objects` o ON po.`idobject` = o.`idobject`
                                        LEFT JOIN `resourcefromtables` as res ON po.`skbranch` = res.`idrecord` AND res.`idtable` = 7 
                                        WHERE  res.idlanguage = ".$contlngid." ";
                        if (isset($idpolltype)) {
                                $q_st .= " AND pt.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."' ";
                        }                
                        if (isset($skproject)) {
                                $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                        } else if (isset($skbranch)) {
                                $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                        }	 
                        if (isset($answers) ){
                                if ($answers == 'hasnot' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 0 ";
                                }
                                if ($answers == 'has' ){
                                        $q_st .= " AND  ua_w.`hasanswer` = 1 ";
                                }
                        }      
                        db_runQuery($cn, $q_st);
                }

                db_createObjectResources($cn, $contlngid);
                $q_st = "UPDATE `poll` INNER JOIN `temp_resource` q 
                                ON  `poll`.`idobject` = q.`idrecord`
                        SET `poll`.`o_name` = q.rft_columnname_text;";
		db_runQuery($cn, $q_st);

		$q_st = "UPDATE `poll` INNER JOIN `resourcefromtables` as res ON  `skpollingobject` = `idrecord` AND `idtable` = 1111 AND idlanguage = ".$contlngid."  
                                SET `objectversion` = rft_columnname_text;";
                db_runQuery($cn, $q_st);


                $q_st = "UPDATE `poll`
                                INNER JOIN objectsreports as obrp ON `poll`.idobject = obrp.idobject
                                INNER JOIN `reports` as r ON obrp.idreport = r.idreport AND `poll`.`r_guid` COLLATE utf8mb4_general_ci = r.`r_guid` COLLATE utf8mb4_general_ci AND (ifnull(r.idlanguage, ".$contlngid.") = ".$contlngid." OR r.idlanguage = 0)  
                                INNER JOIN `resourcefromtables` as res ON r.`idreport` = res.`idrecord` AND res.`idtable` = 2 AND res.idlanguage = ".$contlngid."  
				SET `poll`.`r_name` = rft_columnname_text, `poll`.`idreport` = r.`idreport` ;";
		db_runQuery($cn, $q_st);

	} 
	catch (Exception $ex) {
			logmessage("Error in db_getPollsAnswerByRespondentForDetailsAdmin : ".$ex->getMessage());
			return NULL;
	}
}

function db_getPollRespondetsListAdmin($cn, $idpolltype, $skproject, $skbranch, $usr_arr, $contlngid, $finished = NULL)
{
	try{
                db_prepareSQLBIGSELECT($cn);
                db_dropTempTable($cn, 'poll');
                
                $skpolltype =  db_getSKPollType($cn, $idpolltype);

		$q_st = "    CREATE TEMPORARY TABLE `poll` (
				`skpolltype` INT NOT NULL,
				`iduser` INT NOT NULL,
				`user_hash` varchar(64)  COLLATE utf8mb4_unicode_ci NOT NULL,
				`positions` varchar(6000)  COLLATE utf8mb4_unicode_ci NOT NULL,
				`user_name` varchar(1100)  COLLATE utf8mb4_unicode_ci NOT NULL,
				`progress` INT NOT NULL,
		                `progress_of` INT NOT NULL,
				`progress_percent` INT NOT NULL,
                                PRIMARY KEY (`skpolltype`, `iduser`),    
				INDEX `iduser` (`iduser`),                                          
				INDEX `user_hash` (`user_hash`)                                          
				)ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;";
		db_runQuery($cn, $q_st);
                if (isUserAdmin()) {
                        $q_st = "INSERT IGNORE INTO `poll`
                                        SELECT DISTINCT  pt.`skpolltype`, usr.`iduser`, usr.`user_hash`, '', res.`rft_columnname_text`, ua_w.`hasanswer` as `progress`,1 as `progress_of`, 0 `progress_percent`
                                        FROM `useranswers` ua_w
                                        INNER JOIN `pollingobjects` po ON po.`skpollingobject` = ua_w.`skpollingobject` 
                                        INNER JOIN `users` usr ON ua_w.`iduser` = usr.`iduser`
                                        INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                                        INNER JOIN `resourcefromtables` as res ON usr.`iduser` = res.`idrecord` AND res.`idtable` = 6 
                                        WHERE  pt.skpolltype = ".$skpolltype."
                                        AND res.idlanguage = ".$contlngid."";

                        if (isset($skproject)) {
                                $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                        } else {
                                $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                        }	 
                        db_runQuery($cn, $q_st);
                } else {     		
                        db_createUsersIDsTempTable($cn, $usr_arr);
                        $q_st = "INSERT IGNORE INTO `poll`
                                        SELECT DISTINCT  pt.`skpolltype`, usr.`iduser`, usr.`user_hash`, '', res.`rft_columnname_text`, ua_w.`hasanswer` as `progress`,1 as `progress_of`, 0 `progress_percent`
                                        FROM `useranswers` ua_w
                                        INNER JOIN `pollingobjects` po ON po.`skpollingobject` = ua_w.`skpollingobject` 
                                        INNER JOIN `users` usr ON ua_w.`iduser` = usr.`iduser`
                                        INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                                        INNER JOIN `rightspollingobjects` u ON po.`skpollingobject` = u.`skpollingobject` AND u.deny_allow = 1
                                        INNER JOIN userids_t ut ON u.`iduser` = ut.`iduser` 
                                        INNER JOIN `resourcefromtables` as res ON usr.`iduser` = res.`idrecord` AND res.`idtable` = 6 
                                        WHERE  pt.skpolltype = ".$skpolltype."
                                        AND res.idlanguage = ".$contlngid."";

                        if (isset($skproject)) {
                                $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                        } else {
                                $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                        }	 
                        db_runQuery($cn, $q_st);

                        $q_st = "INSERT IGNORE INTO `poll`
                                        SELECT DISTINCT  pt.`skpolltype`, usr.`iduser`, usr.`user_hash`, '', res.`rft_columnname_text`, ua_w.`hasanswer` as `progress`,1 as `progress_of`, 0 `progress_percent`
                                        FROM `useranswers` ua_w
                                        INNER JOIN `pollingobjects` po ON po.`skpollingobject` = ua_w.`skpollingobject` 
                                        INNER JOIN `users` usr ON ua_w.`iduser` = usr.`iduser`
                                        INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                                        INNER JOIN `rightsadminpollingobjects` u ON pt.`skpolltype` = u.`skpolltype` AND u.deny_allow = 1 AND CASE WHEN ( u.`skproject` IS NULL or u.`skproject` = 0 ) THEN po.`skbranch` = u.`skbranch` ELSE po.`skproject` = u.`skproject` END
                                        INNER JOIN userids_t ut ON u.`iduser` = ut.`iduser` 
                                        INNER JOIN `resourcefromtables` as res ON usr.`iduser` = res.`idrecord` AND res.`idtable` = 6 
                                        WHERE  pt.skpolltype = ".$skpolltype."
                                        AND res.idlanguage = ".$contlngid."";

                        if (isset($skproject)) {
                                $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)." ";
                        } else {
                                $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)." ";
                        }	 
                        db_runQuery($cn, $q_st);

                }
                
                db_dropTempTable($cn, 'polllimiter');

                $q_st = "    
                CREATE TEMPORARY TABLE `polllimiter` (
                                `iduser` INT NOT NULL,
                                `skpolltype` INT NOT NULL,
                                INDEX `iduser` (`iduser`),
                                INDEX `skpolltype` (`skpolltype`)                                          
                                )ENGINE=MYISAM, COLLATE = utf8mb4_general_ci;";
                db_runQuery($cn, $q_st);
                $q_st = "  INSERT INTO  `polllimiter` 
                                SELECT DISTINCT `iduser`, `skpolltype` FROM `poll`;";
                db_runQuery($cn, $q_st);
                $q_st = "  
                        UPDATE `poll`
                        INNER JOIN (
                                SELECT `useranswers`.iduser, `useranswers`.skpolltype, COUNT(0) as progress_of, SUM(`useranswers`.hasanswer) as progress 
                                FROM `useranswers` 
                                INNER JOIN `polllimiter` ON `useranswers`.iduser = `polllimiter`.iduser AND `useranswers`.skpolltype = `polllimiter`.skpolltype
                                group BY `useranswers`.iduser, `useranswers`.skpolltype
                        ) q ON `poll`.iduser = q.iduser AND `poll`.skpolltype = q.skpolltype
                        SET `poll`.progress_of = q.progress_of,  `poll`.progress = q.progress ;";
                db_runQuery($cn, $q_st);

                $q_st = "UPDATE `poll` SET progress_percent = IFNULL( ROUND(IFNULL(progress,0) * 1.0 / (IFNULL(progress_of,0) * 1.0 / 100.0) , 0) , 0) ;";
                db_runQuery($cn, $q_st);	

		$q_st = "UPDATE `poll` SET `positions` = IFNULL((SELECT GROUP_CONCAT(DISTINCT rft_columnname_text SEPARATOR '<br/> ') FROM 
                                userpositions as upos
                                INNER JOIN branches as br ON br.ismain = 1
                                INNER JOIN objects ON objects.o_guid = upos.o_guid AND objects.skbranch = br.skbranch
                                INNER JOIN `resourcefromtables` as res ON `br`.`skbranch`  = res.`skbranch` AND `idobject` = `idrecord` AND `idtable` = 1 AND idlanguage = ".$contlngid."  
				WHERE `poll`.iduser = upos.iduser GROUP BY idlanguage),'');";
		db_runQuery($cn, $q_st);


		if (isset($finished) ){
			if ($finished == 'yes' ){
					$q_st = "DELETE FROM `poll` WHERE `progress_percent` != 100 ;";
					db_runQuery($cn, $q_st);
			}
			if ($finished == 'no' ){
					$q_st = "DELETE FROM  `poll` WHERE `progress_percent` = 100;";
					db_runQuery($cn, $q_st);
			}
		}
	} 
	catch (Exception $ex) {
			logmessage("Error in db_getPollRespondetsListAdmin : ".$ex->getMessage());
			return NULL;
	}
}

function db_getPollObjectKeys($cn, $idpolltype, $skproject, $skbranch){
        try{
                db_prepareSQLBIGSELECT($cn);
                
                $q_st = "SELECT DISTINCT po.`idpollingobject`, po.`skpollingobject`
                         FROM `pollingobjects` po 
                         INNER JOIN `useranswers` ua ON po.`idpollingobject` = ua.`idpollingobject`  
                         WHERE ua.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."'";
  
                if (isset($skproject)) {
                        $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)."; ";
                } else {
                        $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)."; ";
                }	 
                return db_runQueryAndReturnArray($cn, $q_st);
        } 
        catch (Exception $ex) {
                logmessage("Error db_getPollObjectKeys : ".$ex->getMessage());
                return NULL;
        }
}

function db_setStatusToPoll($cn, $idpolltype, $skproject, $skbranch, $newStatus){
        try{
                db_prepareSQLBIGSELECT($cn);
                
                $q_st = "DROP TEMPORARY TABLE IF EXISTS `poll` ;";
                db_runQuery($cn, $q_st);
                
                $q_st = "    CREATE TEMPORARY TABLE `poll` (
                        `idpollingobject` VARCHAR(250) NULL, 
                        `skpollingobject` INT NOT NULL
                        )ENGINE=MEMORY, COLLATE = utf8mb4_general_ci;";
                db_runQuery($cn, $q_st);
                
                $q_st = "INSERT IGNORE INTO `poll`
                         SELECT DISTINCT po.`idpollingobject`, po.`skpollingobject`
                         FROM `pollingobjects` po 
                         INNER JOIN `useranswers` ua ON po.`idpollingobject` = ua.`idpollingobject`  
                         WHERE ua.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."'";
  
                if (isset($skproject)) {
                        $q_st .= " AND po.skproject = ".mysqli_real_escape_string($cn,$skproject)."; ";
                } else {
                        $q_st .= " AND po.skbranch = ".mysqli_real_escape_string($cn,$skbranch)."; ";
                }	 
                db_runQuery($cn, $q_st);
                $q_st = "   UPDATE `useranswers` ua INNER JOIN `poll` p ON ua.`idpollingobject` = p.`idpollingobject`
                               SET ua.idpollingobjectstatus = ".mysqli_real_escape_string($cn,$newStatus)."
                             WHERE ua.idpolltype = '".mysqli_real_escape_string($cn,$idpolltype)."';";
                db_runQuery($cn, $q_st);

        } 
        catch (Exception $ex) {
                logmessage("Error db_setStatusToPoll : ".$ex->getMessage());
                return NULL;
        }
}

function db_getFirstPollObjectBySearch($cn, $usrarr, $oguid, $rguid, $skbranch){
        $ids = implode(",", $usrarr);;
        $q_st = "
                SELECT p.skpollingobject, pt.skpolltype, p.o_guid, p.r_guid, p.skbranch, p.skproject FROM pollingobjects as p 
                INNER JOIN useranswers as ua ON p.idpollingobject = ua.idpollingobject
                INNER JOIN polltypes as pt ON ua.idpolltype = pt.idpolltype
                INNER JOIN users as u ON ua.user_hash = u.user_hash
                WHERE u.user_isactive = 1
                AND ua.idpollingobjectstatus = 1
                AND u.iduser in (".$ids.")
                AND p.skbranch = ".$skbranch." 
                AND p.o_guid = '".mysqli_real_escape_string($cn,$oguid)."' ";
        if (isset($rguid) && strlen($rguid) >0) {
                $q_st .= " AND p.r_guid = '".mysqli_real_escape_string($cn,$rguid)."' ";

        } 
        $q_st .= "
                ORDER BY objectversiondate_tmst DESC
                LIMIT 1;";
        return db_runQueryAndReturnArray($cn, $q_st);
}

function db_getPollsByObject($cn, $usrarr, $oguid, $rguid){
        $ids = implode(",", $usrarr);
        $q_st = "
                SELECT DISTINCT pt.idpolltype, pt.skpolltype, CASE WHEN p.skproject IS NULL THEN p.skbranch ELSE 0 END as skbranch , p.skproject FROM pollingobjects as p 
                INNER JOIN useranswers as ua ON p.idpollingobject = ua.idpollingobject
                INNER JOIN polltypes as pt ON ua.idpolltype = pt.idpolltype
                INNER JOIN users as u ON ua.user_hash = u.user_hash
                WHERE u.user_isactive = 1
                AND ua.idpollingobjectstatus = 1
                AND u.iduser in (".$ids.")
                AND p.o_guid = '".mysqli_real_escape_string($cn,$oguid)."' ";
        if (isset($rguid) && strlen($rguid) >0) {
                $q_st .= " AND p.r_guid = '".mysqli_real_escape_string($cn,$rguid)."' ";
       
        } 
        $q_st .= "
                ORDER BY objectversiondate_tmst DESC;";
        return db_runQueryAndReturnArray($cn, $q_st);
}

function db_getResourseTextToBranch($cn, $skbranch, $contlngid){
        $query = "SELECT rft_columnname_text FROM `resourcefromtables` WHERE `idtable` = 7 AND `idrecord` = ".$skbranch." AND `idlanguage` = ".$contlngid."; ";
        $ar = db_runQueryAndReturnArray($cn, $query);
        return (isset($ar[0]['rft_columnname_text']) ? $ar[0]['rft_columnname_text'] : "resouse not found");  
}

function db_getResourseTextToProject($cn, $skproject, $contlngid){
        $query = "SELECT rft_columnname_text FROM `resourcefromtables` WHERE `idtable` = 10 AND `idrecord` = ".$skproject." AND `idlanguage` = ".$contlngid."; ";
        $ar = db_runQueryAndReturnArray($cn, $query);
        return (isset($ar[0]['rft_columnname_text']) ? $ar[0]['rft_columnname_text'] : "resouse not found");  
}

function db_getResourseTextToPollType($cn, $skpolltype, $contlngid){
        $query = "SELECT rft_columnname_text FROM `resourcefromtables` WHERE `idtable` = 8 AND `idrecord` = ".$skpolltype." AND `idlanguage` = ".$contlngid."; ";
        $ar = db_runQueryAndReturnArray($cn, $query);
        return (isset($ar[0]['rft_columnname_text']) ? $ar[0]['rft_columnname_text'] : "resouse not found");  
}

function db_isValidToCopyRightsObjects($cn, $id_to){
        db_setNolock($cn);
        $p_id_to = mysqli_real_escape_string($cn, $id_to);
        $query = '     SELECT 1 as res
                        FROM  `objectsprohibitionrightscopy` obcr 
                INNER JOIN `objects` as o USING (idobject) 
                INNER JOIN `objects` as o_in ON o_in.o_ileft between o.o_ileft AND o.o_iright
                WHERE obcr.`obcr_prohibit` = 1 AND o_in.idobject = '.$p_id_to.' ;';
        $result = $cn->query($query);
        if ($result) {
                $ar = $result->fetch_assoc();
                return !(is_array($ar) && sizeof($ar) > 0);
        } else {
                return TRUE;
        }                
}

function db_copyRightsObjects($cn, $skbranch, $id_from, $id_to){
        db_setNolock($cn);
        $p_skbranch = mysqli_real_escape_string($cn, $skbranch);
	if ($id_from==$id_to) return;
        $p_id_from = mysqli_real_escape_string($cn, $id_from);
        $p_id_to = mysqli_real_escape_string($cn, $id_to);
        $query = " INSERT IGNORE INTO rightsobjects 
                        SELECT ro.iduser, ".$p_id_to." as idobject, ro.deny_allow, ro.skbranch, 1 `isCopied`
                        FROM  rightsobjects as ro WHERE ".$p_id_from." > 0 AND ro.idobject = ".$p_id_from." AND ro.skbranch = ".$p_skbranch." AND deny_allow != 0 AND (ro.`isCopied` IS NULL OR ro.`isCopied` = 0);";
       db_runQuery($cn, $query);
}

function db_isPollType($cn, $idpolltype, $usr_arr){
        if (!isset($idpolltype) || strlen($idpolltype) == 0 || $usr_arr === NULL || sizeof($usr_arr) == 0)
                return NULL;

        $ids = implode(",", $usr_arr);
        $query = "SELECT 1 as res FROM useranswers ua
                   WHERE idpolltype = '".mysqli_real_escape_string($cn, $idpolltype)."' 
                     AND idpollingobjectstatus in (0,1)
                     AND `iduser` IN (".$ids.") LIMIT 1;";
        return (sizeof(db_runQueryAndReturnArray($cn, $query)) > 0);
}

function db_getPollingObjectsArchiveCount($cn, $skpollingobject, $iduser = NULL){
        $esc_id = mysqli_real_escape_string($cn,$skpollingobject);
        if (isset($iduser)) {
                //63615
                $query = "SELECT COUNT(0) as cnt  FROM pollingobjectsarchive
                        INNER JOIN  useranswersvaluesarchive ON pollingobjectsarchive.idpollingobjectarchive = useranswersvaluesarchive.idpollingobjectarchive
                        WHERE pollingobjectsarchive.`skpollingobject` = ".$esc_id." 
                        AND pollingobjectsarchive.objectversionhash <> (SELECT objectversionhash FROM `pollingobjects` WHERE `skpollingobject` = ".$esc_id.")
                        AND IFNULL(useranswersvaluesarchive.uav_value,0) != 0
                        AND useranswersvaluesarchive.iduser = ".$iduser.";";
        } else {
                $query = "SELECT COUNT(0) as cnt  FROM pollingobjectsarchive 
                        WHERE `skpollingobject` = ".$esc_id." 
                        AND objectversionhash <> (SELECT objectversionhash FROM `pollingobjects` WHERE `skpollingobject` = ".$esc_id.");";
        }
	$ar = db_runQueryAndReturnArray($cn,$query);
        if (is_array($ar) && sizeof($ar) > 0 && sizeof($ar[0]) > 0){
                return $ar[0]['cnt'];
        } else {
                return 0;
        }
}

//60081
function db_isBrancheComplite($cn, $skbranch){
        $query = "SELECT 1 as res  FROM branches 
                WHERE `skbranch` = ".mysqli_real_escape_string($cn,$skbranch)." 
                AND `iscomplete` = 1 ;";
        $ar = db_runQueryAndReturnArray($cn,$query);
        return (is_array($ar) && sizeof($ar) > 0 && $ar[0]["res"] == 1);
}

function db_isProjectComplite($cn, $skproject){
        $query = "SELECT IFNULL(SUM(IFNULL(branches.iscomplete,0) - 1), -1) as res FROM pollingobjects INNER JOIN branches ON pollingobjects.skbranch = branches.skbranch
                WHERE skproject IS NOT NULL AND skproject = ".mysqli_real_escape_string($cn,$skproject)." ;";
        $ar = db_runQueryAndReturnArray($cn,$query);
        return (is_array($ar) && sizeof($ar) > 0 && $ar[0]["res"] == 0);
}

function db_isUserAssignedToPolls($cn, $iduser)
{
        try {
                $query = "SELECT 1 FROM `useranswers` INNER JOIN `users` ON `users`.`user_hash` = `useranswers`.`user_hash`
                        WHERE `users`.iduser = ".mysqli_real_escape_string($cn,$iduser)."
                        AND user_isactive = 1
                        LIMIT 1;";
                $ar = $cn->query($query)->fetch_assoc();
                logError($cn, $query);
                return (is_array($ar) && sizeof($ar) > 0);
        } catch (Exception $e) {
                return FALSE;
        }
}

// 60922
function db_isUserAdminToPoll($cn, $usr_arr, $skpolltype, $skproject, $skbranch){
        if (isUserAdmin())
                return TRUE;        

        if( !isset($usr_arr) || !isset($skpolltype) || !is_array($usr_arr) || sizeof($usr_arr) < 1){
                return FALSE;
        }
        try {
                $ids = implode(",", $usr_arr);
                $query = "SELECT 1 FROM `rightsadminpollingobjects` INNER JOIN `users` ON `users`.`iduser` = `rightsadminpollingobjects`.`iduser` AND user_isactive = 1
                        WHERE `users`.`iduser` IN (".$ids.")
                        AND `rightsadminpollingobjects`.deny_allow = 0                        
                        AND `rightsadminpollingobjects`.skpolltype = ".mysqli_real_escape_string($cn, $skpolltype)."
                        ".(isset($skproject) ? "AND `rightsadminpollingobjects`.skproject = ".mysqli_real_escape_string($cn, $skproject) : "AND `rightsadminpollingobjects`.skbranch = ".mysqli_real_escape_string($cn, $skbranch) )."
                        LIMIT 1;";
                $ar = $cn->query($query)->fetch_assoc();
                logError($cn, $query);
                if (is_array($ar) && sizeof($ar) > 0){
                        // prohibition
                        return FALSE;
                }
                $query = "SELECT 1 FROM `rightsadminpollingobjects` INNER JOIN `users` ON `users`.`iduser` = `rightsadminpollingobjects`.`iduser` AND user_isactive = 1
                WHERE `users`.`iduser` IN (".$ids.")
                AND `rightsadminpollingobjects`.deny_allow = 1                        
                AND `rightsadminpollingobjects`.skpolltype = ".mysqli_real_escape_string($cn, $skpolltype)."
                ".(isset($skproject) ? "AND `rightsadminpollingobjects`.skproject = ".mysqli_real_escape_string($cn, $skproject) : "AND `rightsadminpollingobjects`.skbranch = ".mysqli_real_escape_string($cn, $skbranch) )."
                LIMIT 1;";
                $ar = $cn->query($query)->fetch_assoc();
                logError($cn, $query);
                return (is_array($ar) && sizeof($ar) > 0);

        } catch (Exception $e) {
                return FALSE;
        }
}

function db_isUserAdminToPollBySKpollingobject($cn, $usr_arr, $skpolltype, $skpollingobject){
        if (isUserAdmin())
                return TRUE;        
                
        if( !isset($usr_arr) || !isset($skpolltype) || !isset($skpollingobject) || !isset($usr_arr) || sizeof($usr_arr) < 1){
                return FALSE;
        }
        try {
                $ids = implode(",", $usr_arr);
                $query = "SELECT 1 FROM `rightsadminpollingobjects` 
                                INNER JOIN `users` ON `users`.`iduser` = `rightsadminpollingobjects`.`iduser` AND user_isactive = 1
                                INNER JOIN `pollingobjects` po ON CASE WHEN ( `rightsadminpollingobjects`.`skproject` IS NULL or `rightsadminpollingobjects`.`skproject` = 0 ) THEN po.`skbranch` = `rightsadminpollingobjects`.`skbranch` ELSE po.`skproject` = `rightsadminpollingobjects`.`skproject` END
                WHERE `users`.`iduser` IN (".$ids.")
                AND `rightsadminpollingobjects`.deny_allow = 1                        
                AND `rightsadminpollingobjects`.skpolltype = ".mysqli_real_escape_string($cn, $skpolltype)."
                AND po.`skpollingobject` = ".mysqli_real_escape_string($cn, $skpollingobject)."
                LIMIT 1;";
                $ar = $cn->query($query)->fetch_assoc();
                logError($cn, $query);
                return (is_array($ar) && sizeof($ar) > 0);

        } catch (Exception $e) {
                return FALSE;
        }
}


function db_getPollsStatusList($cn){
        $q_st = "SELECT DISTINCT idpollingobjectstatus FROM useranswers order by idpollingobjectstatus;";
        return db_runQueryAndReturnArray($cn, $q_st);
}

function db_dropTempTable($cn, $tablename){
        $q_st = "DROP TEMPORARY TABLE IF EXISTS `".$tablename."` ;";
	db_runQuery($cn, $q_st);
}

function db_prepareSQLBIGSELECT($cn){
        $query = "SET SQL_BIG_SELECTS=1;";
        db_runQuery($cn, $query);
        $q_st = "SELECT @@max_heap_table_size as result;";
        if (db_runQueryAndReturnArray($cn, $q_st)[0]['result'] < (2147483648 * 4)){
                $q_st = "set @@tmp_table_size=2147483648 * 4;";
                db_runQuery($cn, $q_st);
        }
        $q_st = "SELECT @@max_heap_table_size as result;";
        if (db_runQueryAndReturnArray($cn, $q_st)[0]['result'] < (2147483648 * 4)){
                $q_st = "set @@max_heap_table_size=2147483648 * 4;";
                db_runQuery($cn, $q_st);
        }
        $q_st = "SELECT @@myisam_sort_buffer_size as result;";
        if (db_runQueryAndReturnArray($cn, $q_st)[0]['result'] < (268435456 * 4)){
                $q_st = "set @@myisam_sort_buffer_size=268435456 * 4;";
                db_runQuery($cn, $q_st);
        }
}

function db_getPollsList($cn, $usr_arr, $contlngid, $calcProgress = TRUE){
        
        db_prepareSQLBIGSELECT($cn);

        db_dropTempTable($cn, 'polls');
        
	$q_st = "CREATE TEMPORARY TABLE `polls` (
		`idpollingobject` varchar(250) NOT NULL,
		`skpollingobject` INT NOT NULL,
		`skbranch` INT NOT NULL,
		`skproject` INT NULL,	
		`idpolltype` varchar(250) NOT NULL,
		`skpolltype` INT NOT NULL,
		`updatedate_tmst` INT NOT NULL,
		`progress` INT NOT NULL,
		`progress_of` INT NOT NULL,
		`source_name` varchar(2500) NOT NULL,
		`polltype_name` varchar(2500) NOT NULL,
		`startdate` VARCHAR(10) NOT NULL,
	        `enddate` VARCHAR(10) NOT NULL,		
		`status` TINYINT(4) NOT NULL,	
		`iduser` INT NOT NULL,
		INDEX `skbranch` (`skbranch`),
		INDEX `skproject` (`skproject`),
		INDEX `idpollingobject` (`idpollingobject`),
		INDEX `skpollingobject` (`skpollingobject`),
		INDEX `idpolltype` (`idpolltype`)
		)ENGINE=MEMORY;";
	db_runQuery($cn, $q_st);

	$ids = implode(",", $usr_arr);

	$q_st = "INSERT IGNORE INTO `polls`
		SELECT DISTINCT po.`idpollingobject` as `idpollingobject`, po.`skpollingobject` as `skpollingobject`, po.`skbranch`, po.`skproject`, pt.`idpolltype`, pt.`skpolltype`, 0 as `updatedate_tmst`, ua_w.`hasanswer` as `progress`,1 as `progress_of`, 
		res.rft_columnname_text as `source_name`, '' as `polltype_name`, prj.`startdate`, prj.`enddate` ,		
		ua_w.`idpollingobjectstatus`  as `status` , u.`iduser` 
		  FROM `pollingobjects` po 
                  INNER JOIN `objects` o ON po.`idobject` = o.`idobject` 
                  INNER JOIN `useranswers` as ua_w ON po.`skpollingobject` = ua_w.`skpollingobject`  
		  INNER JOIN `users` u ON ua_w.`iduser` = u.`iduser` 
		  INNER JOIN `branches` br ON br.`skbranch` = po.`skbranch` AND br.iscomplete = 1
		  INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
		  LEFT JOIN `projects` prj ON po.`skproject` = prj.`skproject`
		  LEFT JOIN `resourcefromtables` as res ON po.`skbranch`  = res.`idrecord` AND res.`idtable` = 7 
		 WHERE ua_w.`idpollingobjectstatus` in (1,0) AND u.`iduser` IN (".$ids.") AND u.user_isactive = 1 AND res.idlanguage = ".$contlngid.";";
	db_runQuery($cn, $q_st);

	$q_st = "DELETE FROM `polls` WHERE `skproject` IS NOT NULL AND `skproject` in 
				(SELECT DISTINCT skproject FROM pollingobjects INNER JOIN branches ON pollingobjects.skbranch = branches.skbranch WHERE skproject IS NOT NULL AND branches.iscomplete != 1);";
	db_runQuery($cn, $q_st);

	$q_st = "UPDATE `polls` SET `skbranch` = 0 WHERE `skproject` IS NOT NULL;";
	db_runQuery($cn, $q_st);

	$q_st = "UPDATE `polls` INNER JOIN `resourcefromtables` ON `skproject` = `idrecord` AND `idtable` = 10 AND idlanguage = ".$contlngid."
			    SET `source_name` = rft_columnname_text
			 WHERE `skproject` IS NOT NULL;";
	db_runQuery($cn, $q_st);

	$q_st = "UPDATE `polls` INNER JOIN `resourcefromtables` ON `skpolltype` = `idrecord` AND `idtable` = 8 AND idlanguage = ".$contlngid."
				SET `polltype_name` = rft_columnname_text
		 	  WHERE `skpolltype` IS NOT NULL;";
        db_runQuery($cn, $q_st);
                
        if ($calcProgress) {
                $q_st = "UPDATE `polls` p INNER JOIN (SELECT `skbranch`, CASE WHEN  MAX(IFNULL(objectversiondate_tmst,0)) > MAX(IFNULL(reportversiondate_tmst,0)) THEN MAX(IFNULL(objectversiondate_tmst,0)) ELSE MAX(IFNULL(reportversiondate_tmst,0)) END AS updatedate_tmst
                                FROM `pollingobjects`
                                GROUP BY `skbranch`) po ON po.`skbranch` = p.`skbranch` 
                        SET p.`updatedate_tmst` = po.updatedate_tmst;";
                db_runQuery($cn, $q_st);

                $q_st = "UPDATE `polls` p INNER JOIN (SELECT `skproject`, CASE WHEN  MAX(IFNULL(objectversiondate_tmst,0)) > MAX(IFNULL(reportversiondate_tmst,0)) THEN MAX(IFNULL(objectversiondate_tmst,0)) ELSE MAX(IFNULL(reportversiondate_tmst,0)) END AS updatedate_tmst
                                FROM `pollingobjects` WHERE `skproject` IS NOT NULL
                                GROUP BY `skproject` ) po ON po.`skproject` = p.`skproject`  
                        SET p.`updatedate_tmst` = po.updatedate_tmst;";
                db_runQuery($cn, $q_st);
        }
        $q_st = "DROP TEMPORARY TABLE IF EXISTS `pollsresult` ;";
        db_runQuery($cn, $q_st);

        $q_st = " CREATE TEMPORARY TABLE `pollsresult` (
        `skbranch` INT NOT NULL,
        `skproject` INT NULL,	
        `idpolltype` varchar(250) NOT NULL,
        `skpolltype` INT NOT NULL,
        `updatedate_tmst` INT NOT NULL,
        `progress` INT NOT NULL,
        `progress_percent` INT NOT NULL,
        `progress_of` INT NOT NULL,
        `source_name` varchar(2500) NOT NULL,
        `polltype_name` varchar(2500) NOT NULL,
        `startdate` VARCHAR(10) NOT NULL,
        `enddate` VARCHAR(10) NOT NULL,		
        `status` TINYINT(4) NOT NULL
        )ENGINE=MEMORY;";
        db_runQuery($cn, $q_st);

        $q_st = "INSERT INTO  `pollsresult` 
                        SELECT `skbranch`, `skproject`, `idpolltype`, `skpolltype`, `updatedate_tmst`, SUM(`progress`), 0 as `progress_persent`, SUM(`progress_of`), `source_name`, `polltype_name`, `startdate`, `enddate`, `status`
                        FROM `polls` GROUP BY `skbranch`, `skproject`, `idpolltype`, `skpolltype`, `updatedate_tmst`, `source_name`, `polltype_name`, `startdate`, `enddate`, `status`;";
        db_runQuery($cn, $q_st);

        $q_st = "UPDATE `pollsresult` SET progress_percent = ROUND(progress * 1.0 / (progress_of * 1.0 / 100.0) , 0) ;";
        db_runQuery($cn, $q_st);

}

function db_getProjectBySkProject($cn, $skproject){
        $query = "SELECT * FROM `projects` WHERE `skproject` = ".mysqli_real_escape_string($cn, $skproject).";";
        return db_runQueryAndReturnArray($cn, $query);     
}

function db_getPollFinishDateBySkroject($cn, $skpolltype, $skproject){
        $query = "SELECT DATE_FORMAT(finishdate,'%d.%m.%Y') as finishdate FROM `pollfinishdate` WHERE `skbranch` = 0 AND `skpolltype` = ".mysqli_real_escape_string($cn, $skpolltype)." AND `skproject` = ".mysqli_real_escape_string($cn, $skproject).";";
        $ar = db_runQueryAndReturnArray($cn,$query);
        if (is_array($ar) && sizeof($ar) > 0 && sizeof($ar[0]) > 0){
                return $ar[0]['finishdate'];
        } else {
                return "";
        }
}

function db_getPollFinishDateBySkBranch($cn, $skpolltype, $skbranch){
        $query = "SELECT DATE_FORMAT(finishdate,'%d.%m.%Y') as finishdate FROM `pollfinishdate` WHERE `skpolltype` = ".mysqli_real_escape_string($cn, $skpolltype)." AND `skproject` IS NULL AND `skbranch` = ".mysqli_real_escape_string($cn, $skbranch).";";
        $ar = db_runQueryAndReturnArray($cn,$query);
        if (is_array($ar) && sizeof($ar) > 0 && sizeof($ar[0]) > 0){
                return $ar[0]['finishdate'];
        } else {
                return "";
        }
}

function db_getPollSKbyIDBranch($cn, $idbranch){
        $query = "SELECT po.skpollingobject, pt.skpolltype 
                FROM pollingobjects po 
                INNER JOIN useranswers ua ON po.idpollingobject = ua.idpollingobject
                INNER JOIN polltypes pt ON ua.idpolltype = pt.idpolltype  
                INNER JOIN branches br ON br.skbranch = po.skbranch
                WHERE ua.idpollingobjectstatus = 1 AND br.idbranch = '".mysqli_real_escape_string($cn, $idbranch)."' LIMIT 1;";
        return db_runQueryAndReturnArray($cn, $query);     
}

function db_ifPollobjectHaveArchiveBySkroject($cn, $skpollingobject){
        $query = "SELECT 1 as res FROM pollingobjectsarchive WHERE skpollingobject = ".mysqli_real_escape_string($cn, $skpollingobject)." LIMIT 1;";
        $ar = db_runQueryAndReturnArray($cn,$query);
        if (is_array($ar) && sizeof($ar) > 0 && sizeof($ar[0]) > 0){
                return $ar[0]['res'] == 1;
        } else {
                return false;
        }
}

function db_getLangsCountFromResources($cn){
        $query = "SELECT DISTINCT idlanguage FROM resourcefromtables;";
        $ar = db_runQueryAndReturnArray($cn,$query);
        if (is_array($ar)){
                return sizeof($ar);
        } else {
                return 0;
        }
}
?>