<?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_insertMailInQueue($cn, $idmaitype, $mq_recipients, $mq_replyto, $mq_subject, $mq_body, $priority = 10)
{
        $query  = "INSERT IGNORE INTO `mailqueue` (`idmaitype`, `mq_recipients`, `mq_replyto`, `mq_subject`, `mq_body`) 
                        VALUES ('".mysqli_real_escape_string($cn,$idmaitype)."', 
                                '".mysqli_real_escape_string($cn,$mq_recipients)."', 
                                '".mysqli_real_escape_string($cn,$mq_replyto)."', 
                                '".mysqli_real_escape_string($cn,$mq_subject)."', 
                                '".mysqli_real_escape_string($cn,$mq_body)."');";
        return db_runQuery($cn, $query, 'db_insertMailInQueue');
}

function db_getMailQueue($cn, $idmaitype = NULL, $limit = 1000)
{
        $query  = "SELECT * FROM `mailqueue` ".(($idmaitype && $idmaitype !== NULL) ? " 
                           WHERE idmaitype = ".mysqli_real_escape_string($cn,$idmaitype) : "")." 
                           ORDER BY `priority` ASC 
                           LIMIT ".(($limit && $limit !== NULL) ? mysqli_real_escape_string($cn,$limit) : "1000").";";
        return db_runQueryAndReturnArray($cn, $query, 'db_getMailQueue');
}

function db_getMailQueueType($cn, $type)
{
        if ($type === NULL) {
                return $type;
        } else {
                $query  = "SELECT `idmaitype` FROM `maitypes` WHERE `mt_name` like '".mysqli_real_escape_string($cn,$type)."'";
                $arr = db_runQueryAndReturnArray($cn, $query, 'db_getMailQueueType');
                if (sizeof($arr) > 0){
                        return $arr[0]['idmaitype'];
                } else {
                        return NULL;
                }
        }
}

function db_deleteMailFromQueue($cn, $idmailqueue)
{
        $query  = "DELETE FROM `mailqueue` WHERE idmailqueue = ".mysqli_real_escape_string($cn,$idmailqueue).";";
        return db_runQuery($cn, $query, 'db_deleteMailFromQueue');
}

function db_getUpdateMailQueuePriority($cn, $idmailqueue, $priorityStep = 10)
{
        $query  = "UPDATE `mailqueue` SET `priority` =  `priority` + ".mysqli_real_escape_string($cn,$priorityStep)."  WHERE idmailqueue = ".mysqli_real_escape_string($cn,$idmailqueue).";";
        return db_runQuery($cn, $query, 'db_getUpdateMailQueuePriority');
}

function db_createMailQueue_NewPoll($cn, $idpolltype, $idbranch, $idproject){
        if (!isset($idpolltype) || strlen($idpolltype) == 0 )
                return NULL;

        $deflng = db_getDefaultLangID($cn);
        $deflngInterface = db_getLanguageID($cn, getSetting('portal','defaultLanguage','en'), -1);
        $query = "
                SELECT DISTINCT pt.skpolltype, IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) as user_idlanguagecontent, lng.lang_short, CASE WHEN IFNULL(po.skproject,0) > 0 THEN 0 ELSE po.skbranch END as skbranch, IFNULL(po.skproject,0) as skproject, u.iduser, u.user_email, resuser.rft_columnname_text as user_name, respoll.rft_columnname_text as polltype_name 
                FROM users u
                        INNER JOIN useranswers ua ON u.iduser = ua.iduser
                        INNER JOIN polltypes pt ON pt.skpolltype = ua.skpolltype
                        INNER JOIN pollingobjects po ON po.skpollingobject = ua.skpollingobject
                        INNER JOIN languages lng ON lng.idlanguage = IF(IFNULL(u.user_idlanguageinterface,0) = 0, ".$deflngInterface.", u.user_idlanguageinterface) 
                        LEFT JOIN resourcefromtables respoll ON respoll.idtable = 8 AND respoll.idrecord = pt.skpolltype AND respoll.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                        LEFT JOIN resourcefromtables resuser ON resuser.idtable = 6 AND resuser.idrecord = u.iduser AND resuser.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                WHERE  u.user_isactive = 1 AND ua.hasanswer = 0 AND pt.idpolltype = '".mysqli_real_escape_string($cn, $idpolltype)."' 
                AND ".(isset($idproject) ? " po.skproject in (SELECT skproject FROM projects WHERE idproject = '".mysqli_real_escape_string($cn, $idproject)."') " : " po.skbranch in (SELECT skbranch FROM branches WHERE idbranch = '".mysqli_real_escape_string($cn, $idbranch)."') " ).";        ";
        $ar = db_runQueryAndReturnArray($cn, $query, 'db_createMailQueue_NewPoll');
        foreach ($ar as $mail) {
                // 62378
                $finishdate = "";
                if ($mail[ "skbranch" ] == 0 ) {
                        $finishdate = db_getPollFinishDateBySkroject($cn, $mail[ "skpolltype"], $mail[ "skproject" ]);
                } else {
                        $finishdate = db_getPollFinishDateBySkBranch($cn, $mail[ "skpolltype"], $mail[ "skbranch" ]);
                }
                $url = getSetting('portal','url',substr(getCurPageURLWO(array("secureservice", "service")),0, -1)).'/poll.php?&idpolltype='.$idpolltype."&".($mail["skproject"] != 0 ? 'skproject='.$mail["skproject"] : 'skbranch='.$mail["skbranch"]);
                $userlang = $mail['lang_short'];
                createMailNewPoll($mail['user_email'], getResourceByLang('email.newpoll.subject', $userlang), buildFullPollName($cn, $mail), $url, $userlang, $mail['user_name'], $finishdate);
        }
        return $ar;       
}


function db_createMailQueue_UncompletedPolls($cn, $idpolltype, $skproject, $skbranch, $usr_arr ){
        if (!isset($idpolltype) || strlen($idpolltype) == 0 )
                return NULL;

        $deflng = db_getDefaultLangID($cn);
        $deflngInterface = db_getLanguageID($cn, getSetting('portal','defaultLanguage','en'), -1);
        
        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,
                                `user_name` varchar(1100)  COLLATE utf8mb4_unicode_ci NOT NULL,
                                `user_email` varchar(256)  COLLATE utf8mb4_unicode_ci NOT NULL,
                                `lang_short` varchar(64)  COLLATE utf8mb4_unicode_ci NOT NULL,
                                `user_idlanguagecontent` INT NOT NULL,
                                `lang_short_interface` varchar(64)  COLLATE utf8mb4_unicode_ci NOT NULL,
                                `user_idlanguageinterface` INT 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`, usr.user_email, lng.lang_short, IF(IFNULL(usr.user_idlanguagecontent,0) = 0, ".$deflngInterface.", usr.user_idlanguagecontent) as user_idlanguagecontent, lngi.lang_short, IF(IFNULL(usr.user_idlanguageinterface,0) = 0, ".$deflngInterface.", usr.user_idlanguageinterface) as user_idlanguageinterface, 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 languages lng ON lng.idlanguage = IF(IFNULL(usr.user_idlanguagecontent,0) = 0, ".$deflngInterface.", usr.user_idlanguagecontent) 
                                        INNER JOIN languages lngi ON lngi.idlanguage = IF(IFNULL(usr.user_idlanguageinterface,0) = 0, ".$deflngInterface.", usr.user_idlanguageinterface) 
                                        INNER JOIN `polltypes` pt ON ua_w.`skpolltype` = pt.`skpolltype`
                                        LEFT JOIN `resourcefromtables` as res ON usr.`iduser` = res.`idrecord` AND res.`idtable` = 6 
                                        WHERE  pt.skpolltype = ".$skpolltype."
                                        AND res.idlanguage = IF(IFNULL(usr.user_idlanguagecontent,0) = 0, ".$deflngInterface.", usr.user_idlanguagecontent) ";

                        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`, usr.user_email, lng.lang_short, IF(IFNULL(usr.user_idlanguagecontent,0) = 0, ".$deflngInterface.", usr.user_idlanguagecontent) as user_idlanguagecontent, lngi.lang_short, IF(IFNULL(usr.user_idlanguageinterface,0) = 0, ".$deflngInterface.", usr.user_idlanguageinterface) as user_idlanguageinterface, 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 languages lng ON lng.idlanguage = IF(IFNULL(usr.user_idlanguagecontent,0) = 0, ".$deflngInterface.", usr.user_idlanguagecontent) 
                                        INNER JOIN languages lngi ON lngi.idlanguage = IF(IFNULL(usr.user_idlanguageinterface,0) = 0, ".$deflngInterface.", usr.user_idlanguageinterface) 
                                        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` 
                                        LEFT JOIN `resourcefromtables` as res ON usr.`iduser` = res.`idrecord` AND res.`idtable` = 6 
                                        WHERE  pt.skpolltype = ".$skpolltype."
                                        AND res.idlanguage = IF(IFNULL(usr.user_idlanguagecontent,0) = 0, ".$deflngInterface.", usr.user_idlanguagecontent) ";

                        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`, usr.user_email, lng.lang_short, IF(IFNULL(usr.user_idlanguagecontent,0) = 0, ".$deflngInterface.", usr.user_idlanguagecontent) as user_idlanguagecontent, lngi.lang_short, IF(IFNULL(usr.user_idlanguageinterface,0) = 0, ".$deflngInterface.", usr.user_idlanguageinterface) as user_idlanguageinterface, 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 languages lng ON lng.idlanguage = IF(IFNULL(usr.user_idlanguagecontent,0) = 0, ".$deflngInterface.", usr.user_idlanguagecontent) 
                                        INNER JOIN languages lngi ON lngi.idlanguage = IF(IFNULL(usr.user_idlanguageinterface,0) = 0, ".$deflngInterface.", usr.user_idlanguageinterface) 
                                        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` 
                                        LEFT JOIN `resourcefromtables` as res ON usr.`iduser` = res.`idrecord` AND res.`idtable` = 6 
                                        WHERE  pt.skpolltype = ".$skpolltype."
                                        AND res.idlanguage = IF(IFNULL(usr.user_idlanguagecontent,0) = 0, ".$deflngInterface.", usr.user_idlanguagecontent) ";

                        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 = "DELETE FROM `poll` WHERE `progress_percent` = 100;";
                db_runQuery($cn, $q_st);
                
                $query = "SELECT * FROM `poll`;";
                $ar = db_runQueryAndReturnArray($cn, $query, 'db_createMailQueue_UncompletedPolls');
                $finishdate = "";
                if (isset($skproject)) {
                        $finishdate = db_getPollFinishDateBySkroject($cn, $skpolltype, $skproject);
                } else {
                        $finishdate = db_getPollFinishDateBySkBranch($cn, $skpolltype, $skbranch);
                }
                foreach ($ar as $mail) {
                        $pollName = db_getPolltypeName ($cn, $idpolltype, $mail['user_idlanguagecontent']).': '.(isset($skproject) ? db_getProjectName($cn, $skproject, $mail['user_idlanguagecontent']) : db_getBranchName($cn, $skbranch, $mail['user_idlanguagecontent'])); 
                        $url = getSetting('portal','url',substr(getCurPageURLWO('service'),0,-1)).'/poll.php?&idpolltype='.$idpolltype."&".(isset($skproject) ? 'skproject='.$skproject : 'skbranch='.$skbranch);
                        $userlang = $mail['lang_short_interface'];
                        createMailUncompletedPoll($mail['user_email'], getResourceByLang('email.uncompletedpoll.subject', $userlang), $pollName, $url, $userlang, $mail['user_name'], $finishdate);
                }
        
        } 
        catch (Exception $ex) {
                        logmessage("Error in db_createMailQueue_UncompletedPolls : ".$ex->getMessage());
                        return NULL;
        }

}

function db_createMailQueue_DeletePoll($cn, $idpolltype, $skbranch, $skproject){
        if (!isset($idpolltype) || strlen($idpolltype) == 0 )
                return NULL;

        $deflng = db_getDefaultLangID($cn);
        $deflngInterface = db_getLanguageID($cn, getSetting('portal','defaultLanguage','en'), -1);
        $query = "
                SELECT DISTINCT  lng.lang_short, u.iduser, u.user_email, resuser.rft_columnname_text as user_name, respoll.rft_columnname_text as polltype_name, IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) as user_idlanguagecontent
                FROM users u
                        INNER JOIN useranswers ua ON u.iduser = ua.iduser
                        INNER JOIN polltypes pt ON pt.skpolltype = ua.skpolltype
                        INNER JOIN pollingobjects po ON po.skpollingobject = ua.skpollingobject
                        INNER JOIN languages lng ON lng.idlanguage = IF(IFNULL(u.user_idlanguageinterface,0) = 0, ".$deflngInterface.", u.user_idlanguageinterface) 
                        LEFT JOIN resourcefromtables respoll ON respoll.idtable = 8 AND respoll.idrecord = pt.skpolltype AND respoll.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent)
                        LEFT JOIN resourcefromtables resuser ON resuser.idtable = 6 AND resuser.idrecord = u.iduser AND resuser.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                        WHERE  u.user_isactive = 1 ".
                        ($skproject === NULL ? " AND po.skbranch = ".mysqli_real_escape_string($cn, $skbranch) : " AND po.skproject = ".mysqli_real_escape_string($cn, $skproject)).
                        " AND pt.idpolltype = '".mysqli_real_escape_string($cn, $idpolltype)."';        ";
        $ar = db_runQueryAndReturnArray($cn, $query, 'db_createMailQueue_DeletePoll');
        foreach ($ar as $mail) {
                $userlang = $mail['lang_short'];
                $subject = $mail['polltype_name'].': '.($skproject === NULL ? db_getBranchName($cn, $skbranch, $mail["user_idlanguagecontent"]) : db_getProjectName($cn, $skproject, $mail["user_idlanguagecontent"])).'';
                createMailDeletePoll($mail['user_email'], getResourceByLang('email.deletepoll.subject', $userlang), $subject, '', $userlang, $mail['user_name']);
        }
        return $ar;       
}

function db_createMailQueue_HidePoll($cn, $idpolltype, $skbranch, $skproject){
        if (!isset($idpolltype) || strlen($idpolltype) == 0 )
                return NULL;

        $deflng = db_getDefaultLangID($cn);
        $deflngInterface = db_getLanguageID($cn, getSetting('portal','defaultLanguage','en'), -1);
        $query = "
                SELECT DISTINCT  lng.lang_short, u.iduser, u.user_email, resuser.rft_columnname_text as user_name, respoll.rft_columnname_text as polltype_name, IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) as user_idlanguagecontent
                FROM users u
                        INNER JOIN useranswers ua ON u.iduser = ua.iduser
                        INNER JOIN polltypes pt ON pt.skpolltype = ua.skpolltype
                        INNER JOIN pollingobjects po ON po.skpollingobject = ua.skpollingobject
                        INNER JOIN languages lng ON lng.idlanguage = IF(IFNULL(u.user_idlanguageinterface,0) = 0, ".$deflngInterface.", u.user_idlanguageinterface) 
                        LEFT JOIN resourcefromtables respoll ON respoll.idtable = 8 AND respoll.idrecord = pt.skpolltype AND respoll.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent)
                        LEFT JOIN resourcefromtables resuser ON resuser.idtable = 6 AND resuser.idrecord = u.iduser AND resuser.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                        WHERE  u.user_isactive = 1 ".
                        ($skproject === NULL ? " AND po.skbranch = ".mysqli_real_escape_string($cn, $skbranch) : " AND po.skproject = ".mysqli_real_escape_string($cn, $skproject)).
                        " AND pt.idpolltype = '".mysqli_real_escape_string($cn, $idpolltype)."';        ";
        $ar = db_runQueryAndReturnArray($cn, $query, 'db_createMailQueue_HidePoll');
        foreach ($ar as $mail) {
                $userlang = $mail['lang_short'];
                $subject = $mail['polltype_name'].' : '.($skproject === NULL ? db_getBranchName($cn, $skbranch, $mail["user_idlanguagecontent"]) : db_getProjectName($cn, $skproject, $mail["user_idlanguagecontent"])).'';
                createMailHidePoll($mail['user_email'], getResourceByLang('email.hidenpoll.subject', $userlang), $subject, '', $userlang, $mail['user_name']);
        }
        return $ar;       
}

function db_createMailQueue_ContinuePoll($cn, $idpolltype, $skbranch, $skproject){
        if (!isset($idpolltype) || strlen($idpolltype) == 0 )
                return NULL;

        $deflng = db_getDefaultLangID($cn);
        $deflngInterface = db_getLanguageID($cn, getSetting('portal','defaultLanguage','en'), -1);
        $query = "
                SELECT DISTINCT  pt.skpolltype, lng.lang_short, CASE WHEN IFNULL(po.skproject,0) > 0 THEN 0 ELSE po.skbranch END as skbranch, IFNULL(po.skproject,0) as skproject, u.iduser, u.user_email, resuser.rft_columnname_text as user_name, respoll.rft_columnname_text as polltype_name, IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) as user_idlanguagecontent
                FROM users u
                        INNER JOIN useranswers ua ON u.iduser = ua.iduser
                        INNER JOIN polltypes pt ON pt.skpolltype = ua.skpolltype
                        INNER JOIN pollingobjects po ON po.skpollingobject = ua.skpollingobject
                        INNER JOIN languages lng ON lng.idlanguage = IF(IFNULL(u.user_idlanguageinterface,0) = 0, ".$deflngInterface.", u.user_idlanguageinterface) 
                        LEFT JOIN resourcefromtables respoll ON respoll.idtable = 8 AND respoll.idrecord = pt.skpolltype AND respoll.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent)
                        LEFT JOIN resourcefromtables resuser ON resuser.idtable = 6 AND resuser.idrecord = u.iduser AND resuser.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                        WHERE  u.user_isactive = 1 ".
                        ($skproject === NULL ? " AND po.skbranch = ".mysqli_real_escape_string($cn, $skbranch) : " AND po.skproject = ".mysqli_real_escape_string($cn, $skproject)).
                        " AND pt.idpolltype = '".mysqli_real_escape_string($cn, $idpolltype)."';        ";
        $ar = db_runQueryAndReturnArray($cn, $query, 'db_createMailQueue_ContinuePoll');
        foreach ($ar as $mail) {
                // 62378
                $finishdate = "";
                if ($mail[ "skbranch" ] == 0 ) {
                        $finishdate = db_getPollFinishDateBySkroject($cn, $mail[ "skpolltype"], $mail[ "skproject" ]);
                } else {
                        $finishdate = db_getPollFinishDateBySkBranch($cn, $mail[ "skpolltype"], $mail[ "skbranch" ]);
                }
                $userlang = $mail['lang_short'];
                $subject = $mail['polltype_name'].': '.($skproject === NULL ? db_getBranchName($cn, $skbranch, $mail["user_idlanguagecontent"]) : db_getProjectName($cn, $skproject, $mail["user_idlanguagecontent"])).'';
                $url = getSetting('portal','url',substr(getCurPageURLWO(array("secureservice", "service")),0, -1)).'/poll.php?&idpolltype='.$idpolltype."&".($mail["skproject"] != 0 ? 'skproject='.$mail["skproject"] : 'skbranch='.$mail["skbranch"]);
                createMailContinuePoll($mail['user_email'], getResourceByLang('email.continuepoll.subject', $userlang), $subject, $url, $userlang, $mail['user_name'], $finishdate);
        }
        return $ar;       
}


function db_createMailQueue_StopPoll($cn, $idpolltype, $skbranch, $skproject){
        if (!isset($idpolltype) || strlen($idpolltype) == 0 )
                return NULL;

        $deflng = db_getDefaultLangID($cn);
        $deflngInterface = db_getLanguageID($cn, getSetting('portal','defaultLanguage','en'), -1);
        $query = "
                SELECT DISTINCT  lng.lang_short, u.iduser, u.user_email, resuser.rft_columnname_text as user_name, respoll.rft_columnname_text as polltype_name, IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) as user_idlanguagecontent
                FROM users u
                        INNER JOIN useranswers ua ON u.iduser = ua.iduser
                        INNER JOIN polltypes pt ON pt.skpolltype = ua.skpolltype
                        INNER JOIN pollingobjects po ON po.skpollingobject = ua.skpollingobject
                        INNER JOIN languages lng ON lng.idlanguage = IF(IFNULL(u.user_idlanguageinterface,0) = 0, ".$deflngInterface.", u.user_idlanguageinterface) 
                        LEFT JOIN resourcefromtables respoll ON respoll.idtable = 8 AND respoll.idrecord = pt.skpolltype AND respoll.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent)
                        LEFT JOIN resourcefromtables resuser ON resuser.idtable = 6 AND resuser.idrecord = u.iduser AND resuser.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                        WHERE  u.user_isactive = 1 ".
                        ($skproject === NULL ? " AND po.skbranch = ".mysqli_real_escape_string($cn, $skbranch) : " AND po.skproject = ".mysqli_real_escape_string($cn, $skproject)).
                        " AND pt.idpolltype = '".mysqli_real_escape_string($cn, $idpolltype)."';        ";
                $ar = db_runQueryAndReturnArray($cn, $query, 'db_createMailQueue_StopPoll');
        foreach ($ar as $mail) {
                $userlang = $mail['lang_short'];
                $subject = $mail['polltype_name'].': '.($skproject === NULL ? db_getBranchName($cn, $skbranch, $mail["user_idlanguagecontent"]) : db_getProjectName($cn, $skproject, $mail["user_idlanguagecontent"])).'';
                createMailStopPoll($mail['user_email'], getResourceByLang('email.stoppoll.subject', $userlang), $subject, '', $userlang, $mail['user_name']);
        }
        return $ar;       
}


function db_createMailQueue_NewPollAnalyst($cn, $idpolltype, $idbranch, $idproject, $uhash){
        if (!isset($idpolltype) || strlen($idpolltype) == 0 )
                return NULL;

        $deflng = db_getDefaultLangID($cn);
        $deflngInterface = db_getLanguageID($cn, getSetting('portal','defaultLanguage','en'), -1);
        $query = "
                SELECT DISTINCT  IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) as user_idlanguagecontent, lng.lang_short, CASE WHEN IFNULL(po.skproject,0) > 0 THEN 0 ELSE po.skbranch END as skbranch, po.skproject, u.iduser, u.user_email, resuser.rft_columnname_text as user_name, respoll.rft_columnname_text as polltype_name 
                FROM users u CROSS JOIN useranswers ua 
                        INNER JOIN polltypes pt USING (skpolltype) 
                        INNER JOIN pollingobjects po USING (skpollingobject) 
                        INNER JOIN rightspollingobjects rpo ON u.iduser = rpo.iduser AND po.skpollingobject = rpo.skpollingobject AND rpo.deny_allow = 1
                        INNER JOIN languages lng ON lng.idlanguage = IF(IFNULL(u.user_idlanguageinterface,0) = 0, ".$deflngInterface.", u.user_idlanguageinterface) 
                        LEFT JOIN resourcefromtables respoll ON respoll.idtable = 8 AND respoll.idrecord = pt.skpolltype AND respoll.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent)
                        LEFT JOIN resourcefromtables resuser ON resuser.idtable = 6 AND resuser.idrecord = u.iduser AND resuser.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                WHERE  u.user_isactive = 1 AND  u.user_hash = '".mysqli_real_escape_string($cn, $uhash)."' AND pt.idpolltype = '".mysqli_real_escape_string($cn, $idpolltype)."'
                AND ".(isset($idproject) ? " po.skproject in (SELECT skproject FROM projects WHERE idproject = '".mysqli_real_escape_string($cn, $idproject)."') " : " po.skbranch in (SELECT skbranch FROM branches WHERE idbranch = '".mysqli_real_escape_string($cn, $idbranch)."') " ).";        ";
        $ar = db_runQueryAndReturnArray($cn, $query, 'db_createMailQueue_NewPollAnalyst');
        foreach ($ar as $mail) {
                $url = getSetting('portal','url',substr(getCurPageURLWO(array("secureservice", "service")),0, -1)).'/adminpoll.php?&idpolltype='.$idpolltype."&".($mail["skbranch"] == 0 ? 'skproject='.$mail["skproject"] : 'skbranch='.$mail["skbranch"]);
                $userlang = $mail['lang_short'];
                createMailNewPollAnalyst($mail['user_email'], getResourceByLang('email.newpollanalyst.subject', $userlang), buildFullPollName($cn, $mail), $url, $userlang, $mail['user_name']);
        }
        return $ar;       
}

function db_createMailQueue_pollContainsErrorsAnalyst($cn, $idpolltype, $idbranch, $idproject, $uhash){
        if (!isset($idpolltype) || strlen($idpolltype) == 0 )
                return NULL;

        $deflng = db_getDefaultLangID($cn);
        $deflngInterface = db_getLanguageID($cn, getSetting('portal','defaultLanguage','en'), -1);
        $query = "
                SELECT DISTINCT  IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) as user_idlanguagecontent, lng.lang_short, CASE WHEN IFNULL(po.skproject,0) > 0 THEN 0 ELSE po.skbranch END as skbranch, po.skproject, u.iduser, u.user_email, resuser.rft_columnname_text as user_name, respoll.rft_columnname_text as polltype_name 
                FROM users u CROSS JOIN useranswers ua 
                        INNER JOIN polltypes pt USING (skpolltype) 
                        INNER JOIN pollingobjects po USING (skpollingobject) 
                        INNER JOIN rightspollingobjects rpo ON u.iduser = rpo.iduser AND po.skpollingobject = rpo.skpollingobject AND rpo.deny_allow = 1
                        INNER JOIN languages lng ON lng.idlanguage = IF(IFNULL(u.user_idlanguageinterface,0) = 0, ".$deflngInterface.", u.user_idlanguageinterface) 
                        LEFT JOIN resourcefromtables respoll ON respoll.idtable = 8 AND respoll.idrecord = pt.skpolltype AND respoll.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent)
                        LEFT JOIN resourcefromtables resuser ON resuser.idtable = 6 AND resuser.idrecord = u.iduser AND resuser.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                WHERE  u.user_isactive = 1 AND  u.user_hash = '".mysqli_real_escape_string($cn, $uhash)."' AND pt.idpolltype = '".mysqli_real_escape_string($cn, $idpolltype)."'        
                AND ".(isset($idproject) ? " po.skproject in (SELECT skproject FROM projects WHERE idproject = '".mysqli_real_escape_string($cn, $idproject)."') " : " po.skbranch in (SELECT skbranch FROM branches WHERE idbranch = '".mysqli_real_escape_string($cn, $idbranch)."') " ).";        ";
        $ar = db_runQueryAndReturnArray($cn, $query, 'db_createMailQueue_pollContainsErrorsAnalyst');
        foreach ($ar as $mail) {
                $url = getSetting('portal','url',substr(getCurPageURLWO(array("secureservice", "service")),0, -1)).'/adminpoll.php?&idpolltype='.$idpolltype."&".($mail["skbranch"] == 0 ? 'skproject='.$mail["skproject"] : 'skbranch='.$mail["skbranch"]);
                $userlang = $mail['lang_short'];
                createMailPollContainsErrorsAnalyst($mail['user_email'], getResourceByLang('email.pollcontainserrorsanalyst.subject', $userlang), buildFullPollName($cn, $mail), $url, $userlang, $mail['user_name']);
        }
        return $ar;       
}

function db_createMailQueue_pollContainsErrorsAnalystAdmin($cn, $idpolltype, $idbranch, $idproject){
        if (!isset($idpolltype) || strlen($idpolltype) == 0 )
                return NULL;

        $deflng = db_getDefaultLangID($cn);
        $deflngInterface = db_getLanguageID($cn, getSetting('portal','defaultLanguage','en'), -1);
        $query = "
                SELECT DISTINCT  IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) as user_idlanguagecontent, lng.lang_short, CASE WHEN IFNULL(po.skproject,0) > 0 THEN 0 ELSE po.skbranch END as skbranch, po.skproject, u.iduser, u.user_email, resuser.rft_columnname_text as user_name, respoll.rft_columnname_text as polltype_name 
                FROM users u CROSS JOIN useranswers ua
                        INNER JOIN polltypes pt USING (skpolltype) 
                        INNER JOIN pollingobjects po USING (skpollingobject) 
                        INNER JOIN languages lng ON lng.idlanguage = IF(IFNULL(u.user_idlanguageinterface,0) = 0, ".$deflngInterface.", u.user_idlanguageinterface) 
                        LEFT JOIN resourcefromtables respoll ON respoll.idtable = 8 AND respoll.idrecord = pt.skpolltype AND respoll.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                        LEFT JOIN resourcefromtables resuser ON resuser.idtable = 6 AND resuser.idrecord = u.iduser AND resuser.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                WHERE  u.user_isactive = 1 AND user_isadmin = 1 AND pt.idpolltype = '".mysqli_real_escape_string($cn, $idpolltype)."'
                AND ".(isset($idproject) ? " po.skproject in (SELECT skproject FROM projects WHERE idproject = '".mysqli_real_escape_string($cn, $idproject)."') " : " po.skbranch in (SELECT skbranch FROM branches WHERE idbranch = '".mysqli_real_escape_string($cn, $idbranch)."') " ).";        ";
        $ar = db_runQueryAndReturnArray($cn, $query, 'db_createMailQueue_pollContainsErrorsAnalystAdmin');
        foreach ($ar as $mail) {
                $url = getSetting('portal','url',substr(getCurPageURLWO(array("secureservice", "service")),0, -1)).'/adminpoll.php?&idpolltype='.$idpolltype."&".($mail["skbranch"] == 0 ? 'skproject='.$mail["skproject"] : 'skbranch='.$mail["skbranch"]);
                $userlang = $mail['lang_short'];
                createMailPollContainsErrorsAnalystAdmin($mail['user_email'], getResourceByLang('email.pollcontainserrorsanalystadmin.subject', $userlang), buildFullPollName($cn, $mail), $url, $userlang, $mail['user_name']);
        }
        return $ar;       
}

function db_createMailQueue_NewPollAdmin($cn, $idpolltype, $idbranch, $idproject){
        if (!isset($idpolltype) || strlen($idpolltype) == 0 )
                return NULL;

        $deflng = db_getDefaultLangID($cn);
        $deflngInterface = db_getLanguageID($cn, getSetting('portal','defaultLanguage','en'), -1);
        $query = "
                SELECT DISTINCT IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) as user_idlanguagecontent, lng.lang_short, CASE WHEN IFNULL(po.skproject,0) > 0 THEN 0 ELSE po.skbranch END as skbranch, po.skproject, u.iduser, u.user_email, resuser.rft_columnname_text as user_name, respoll.rft_columnname_text as polltype_name 
                FROM users u CROSS JOIN useranswers ua
                        INNER JOIN polltypes pt USING (skpolltype) 
                        INNER JOIN pollingobjects po USING (skpollingobject) 
                        INNER JOIN languages lng ON lng.idlanguage = IF(IFNULL(u.user_idlanguageinterface,0) = 0, ".$deflngInterface.", u.user_idlanguageinterface) 
                        LEFT JOIN resourcefromtables respoll ON respoll.idtable = 8 AND respoll.idrecord = pt.skpolltype AND respoll.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                        LEFT JOIN resourcefromtables resuser ON resuser.idtable = 6 AND resuser.idrecord = u.iduser AND resuser.idlanguage = IF(IFNULL(u.user_idlanguagecontent,0) = 0, ".$deflng.", u.user_idlanguagecontent) 
                WHERE  u.user_isactive = 1 AND user_isadmin = 1 AND pt.idpolltype = '".mysqli_real_escape_string($cn, $idpolltype)."'
                AND ".(isset($idproject) ? " po.skproject in (SELECT skproject FROM projects WHERE idproject = '".mysqli_real_escape_string($cn, $idproject)."') " : " po.skbranch in (SELECT skbranch FROM branches WHERE idbranch = '".mysqli_real_escape_string($cn, $idbranch)."') " ).";        ";
        $ar = db_runQueryAndReturnArray($cn, $query, 'db_createMailQueue_NewPollAdmin');
        foreach ($ar as $mail) {
                $url = getSetting('portal','url',substr(getCurPageURLWO(array("secureservice", "service")),0, -1)).'/adminpoll.php?&idpolltype='.$idpolltype."&".($mail["skbranch"] == 0 ? 'skproject='.$mail["skproject"] : 'skbranch='.$mail["skbranch"]);
                $userlang = $mail['lang_short'];
                createMailNewPollAdmin($mail['user_email'], getResourceByLang('email.newpolladmin.subject', $userlang), buildFullPollName($cn, $mail), $url, $userlang, $mail['user_name']);
        }
        return $ar;       
}

function buildFullPollName($cn, $mail){
        return $mail['polltype_name'].': '.(($mail['skbranch'] == 0) ? db_getProjectName($cn, $mail['skproject'], $mail["user_idlanguagecontent"] ) : db_getBranchName($cn, $mail['skbranch'], $mail["user_idlanguagecontent"]));
}

?>