<?php
/**
 * Created by PhpStorm.
 * User: Jure
 * Date: 25.6.2014
 * Time: 17:17
 */
include_once ("_dirinfo.php");
include_once (PATH_TO_ROOT."init.php");
include_once (PATH_TO_ROOT."config.php");
include_once ("../../../../webuserspool/UsersPoolinit.php");
include_once ("../../../../_ajaxManipulators/_class/class.gridData.php");


class NfieldInterviewers extends GridDataManipulator
{

    function selectAction()
    {
        if($_POST['group_id'] != 0)
            $this->addWhere( "id_group = ".$_POST['group_id'] );
        if(isset($_POST['template_id']))
        {
            //we call the function which prepares the $_POST paramaters to create the appropriate SQL
            $this->createTemplateSQL();
        }
        if(is_array($_POST['filter']))
            foreach ($_POST['filter'] as &$filter) {
                if($filter['field'] == 'id') $filter['field'] = "a.id";
            }

        $result =  parent::selectAction();
        $this->db->SQLexecute("DROP TEMPORARY TABLE IF EXISTS queryMachineTmp");
        $this->db->SQLexecute("DROP TEMPORARY TABLE IF EXISTS queryMachineProjTmp");
        $addOn = $_POST['addOn'];
        if($addOn )
        {
            $group = $_POST['toGroup'];

            while ($row = $this->db->fetchArray( $this->rsCount) )
            {
                $user = $row['id'];
                if($_POST['database'] == DB_WARPIT_WEBCATI_BASE)
                    //if we're working with cati users, we have to add id_type...and check if any admins are added!
                {
                    if(substr($user,0,5) == 'admin')
                    {
                        $idType = 1;
                        $user = substr($user,6);
                    }
                    else
                        $idType = 0;
                    $val[] = "($group , $user,$idType )";

                }
                else
                    $val[] = "($group , $user )";

                $delVal[] = $user;

            }

            if($val)
            {
                if($addOn=='copy' || $addOn == 'move' )
                {
                    $sql = "INSERT IGNORE INTO _group_link VALUES " . implode(',',$val);

                    If (!$this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: '.$sql." }";}
                }
                if($addOn == 'move' || $addOn == 'removeUsers')
                {
                    $sql = "DELETE FROM _group_link WHERE id in (".implode(',',$delVal).") AND id_group = ".$_POST['group_id'];
                    If (!$this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: '.$sql." }";}
                }
                if($addOn == 'deleteUsers')
                {
                    $sql = "DELETE FROM ".WUP_INTERVIEWER." WHERE id in (".implode(',',$delVal).") ";
                    If (!$this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: '.$sql." }";}
                    $sql = "DELETE FROM _group_link WHERE id in (".implode(',',$delVal).") ";
                    If (!$this->db->SQLexecute($sql)) {	return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: '.$sql." }";}
                }
            }

        }

        return $result;
    }

    function insertAction(){


        $language = 46;
        $sql = 'INSERT IGNORE INTO _Interviewer (username,FirstName,Surname,email,language)
         VALUE("UserName","Name","LastName","/",'.$language.")";
        $result = $this->db->SQLexecute($sql);

        if (!$result) {
            return "{success:false,error:'Get Capi tables id'}";
        }
        $lastID = $this->db->GetLastInsertId();

        $groupID = 56;
        $sql = "INSERT IGNORE INTO _group_link(id_group,id) VALUE(" .$groupID . ',' . $lastID . ")";
        if (!$this->db->SQLexecute($sql)) {
            return "{success:false,error:'Group link saving error'}";
        }
        return "{success:true,message:'Insert was success'}";
    }



    function updateAction()
    {
        // condition to check if email is allready existing in table

        if($_POST['field'] == 'email')
        {
            $_POST['value'] = trim($_POST['value']);
            $_POST['newValue'] = true;
            // if we insert in field email as empty, program will save NULL in database
            // because NULL is not checking for uniqe keys by database
            if ($_POST['value'] == '') {
                $sql = " UPDATE $this->table SET email = NULL WHERE id = ".$_POST['id'];
                If (!$res = $this->db->SQLexecute($sql))  return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: '.$sql." }";
                return '{success:true}';

            }

            // checking for id of duplicated email
            $sql = "SELECT id FROM $this->table WHERE id!={$_POST['id']} AND email = '".$_POST['value']."'";
            If (!$res = $this->db->SQLexecute($sql)) {return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: '.$sql." }";}
            if($row = $this->db->fetchAssoc($res) )
            {
                $dupId = $row['id'];
                // set sql query to get all groups of duplicated email
                $sql = "SELECT a.idLab FROM ".WUP_GROUPS." a INNER JOIN ".WUP_GROUP_LINK." b ON a.id = b.id_group WHERE b.id = $dupId";
                If (!$res = $this->db->SQLexecute($sql)) {return '{success:false,error:"'.$this->db->GetError().'", STATEMENT: '.$sql." }";}
                $names = array();
                while($row2 = $this->db->fetchAssoc($res))
                {
                    $names[] = $row2['idLab'];
                }

                return "{success:false, error:'mailDuplicate',dupId: $dupId , groups : ".json_encode($names) ." }";
            }
        }

        if($_POST['gridType'] == 'usersPool')
        {
            if(substr($_POST['id'],0,5) == 'admin')
            {
                $_POST['id'] = substr($_POST['id'],6, (strlen($_POST['id']) - 6) );
                if($_POST['id'] == $_SESSION['UserInfo']['id']) // if the user wants to change his own data
                {
                    $_POST['database'] = DB_WARPIT_MAIN;
                    if($_POST['field'] == 'password')
                        $_POST['value'] = md5($_POST['value']);
                    $_POST['RMtable'] = '_user';
                    if($_POST['database'])
                        $this->db->ChangeDatabase($_POST['database']);
                }
                else
                    return "{success:false,error:'editAdminNotAllowed'}";
            }
            if($_POST['field'] == 'SIPPhoneID' && $_POST['value'] != ''){
                $sipId = $_POST['value'];

                $sql = "SELECT Firstname, Surname FROM ".DB_WARPIT_WEBCATI_BASE."._Interviewer WHERE SIPPhoneID='$sipId'";
                $res = $this->db->SQLexecute($sql);
                if($this->db->NumRows($res) > 0) //check if any other user already has this SIPusername
                {
                    $row = $this->db->fetchAssoc($res);
                    return "{success:false,error:'sipuserexists',sip:'$sipId',user:'".$row['Firstname']." ".$row['Surname']."'}";
                }

                $sql = "SELECT Firstname, Surname FROM ".DB_WARPIT_MAIN."._user WHERE SIPPhoneID='$sipId'";
                $res = $this->db->SQLexecute($sql);
                if($this->db->NumRows($res) > 0) //check if any other user already has this SIPusername
                {
                    $row = $this->db->fetchAssoc($res);
                    return "{success:false,error:'sipuserexists',sip:'$sipId',user:'".$row['Firstname']." ".$row['Surname']."'}";
                }
                else
                {

                }
            }
        }

        return parent::updateAction();

    }

    function exportAction()
    {
        if ($_REQUEST['group_id'] != 0)
            $this->addWhere("id_group = " . $_POST['group_id']);
        if (isset($_POST['template_id'])) {
            //we call the function which prepares the $_POST paramaters to create the appropriate SQL
            $this->createTemplateSQL();
        }
        return parent::exportAction();
    }

    function getAllAction()
    {

        $sql = "SELECT * FROM _Interviewer";
        $res = $this->db->SQLexecute($sql);
        while ($row = $this->db->fetchAssoc($res)) {
            $this->arr[] = $row;
        }
        $allData = array(
            'success' => 'true',
            'results' => sizeof($this->arr),
            'rows' => $this->arr
        );
        return json_encode($allData);
        // return parent::selectAction();
    }

    function getAssignOnProjectAction()
    {
        $project_id = $_POST['project_id'];
        $projectSamplePointTableName = $_POST['projectSampleTableName'];
        $sql = "SELECT i.* FROM _Interviewer i, _www_ids w, ".$projectSamplePointTableName." as p WHERE i.id = w.id_user AND p.user_id = i.id AND w.id_project = ".$project_id."";
        //$sql = 'SELECT i.* FROM _Interviewer i,_www_ids w WHERE i.id = w.id_user AND id_project = ' . $project_id;
        $res = $this->db->SQLexecute($sql);
        while ($row = $this->db->fetchAssoc($res)) {
            $this->arr[] = $row;
        }
        $allData = array(
            'success' => 'true',
            'results' => sizeof($this->arr),
            'rows' => $this->arr
        );
        return json_encode($allData);
        // return parent::selectAction();
    }


    function createTemplateSQL()
    {
        $id = $_POST['template_id'];
        $queryTable = '_QueryMachineConstruction';
        //here is where hell happens!!!
        //we have to go through all stored query fields and build such a devilish query that heaven shall burn!!!
        $sql = "SELECT a.*,b.field_type FROM $queryTable a LEFT JOIN _InterviewerStructure b ON b.field_name = a.field_id  WHERE id_template = $id ORDER BY `orderBy` ASC";
        If (!$this->rs = $this->db->SQLexecute($sql)) {	echo '{success:false,error:"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';exit;}

        $cnt = 0;
        $sqlSel = 'SELECT *';
        $sqlFrom = ' FROM _Interviewer a';
        //	$sqlWhere = ' WHERE';
        while($row = $this->db->fetchAssoc($this->rs)){
            if( $cnt != 0 )
            {
                $sqlWhere .= ' '.$row['conditionField'];
            }
            $sqlWhere .= $row['openBracket'];
            if($row['field_id'] == 'Groups' )
            {
                //if multiple statements for groups is avaliable we must create a tmp table, with one field for each group
                $val = $row['valueField'];
                $createTmpGroup[] = $val;

                if($row['operation'] == '=' )
                    $sqlWhere .= " group$val = ".$val;
                else
                {
                    $sqlWhere .= " group$val IS NULL " ;
                }

            }
            else if($row['field_id'] == 'Projects' )
            {
                //if multiple statements for projects is avaliable we must create a tmp table, with one field for each projet
                $val = $row['valueField'];
                $createTmpProject[] = $val;

                if($row['operation'] == '=' )
                    $sqlWhere .= " project$val = ".$val;
                else
                {
                    $sqlWhere .= " project$val IS NULL " ;
                }

            }
            else if($row['field_id'] == 'Gained')
            {
                $sqlTmp = ' (SELECT ifnull(SUM(webPoints),0) FROM '.DB_WARPIT_WEBCATI_BASE.'._www_interviewer_statistics WHERE id_user = a.id)';

                $sqlSel .=", $sqlTmp AS gained_tmp";
                $sqlWhere .= $sqlTmp.$row['operation'].$row['valueField'];
            }
            else if($row['field_id'] == 'Used')
            {
                $sqlTmp = ' (SELECT ifnull(-SUM(webPoints),0) FROM '.DB_WARPIT_WEBCATI_BASE.'._wwwPoints WHERE id_user = a.id)';
                $sqlSel .=", $sqlTmp AS used_tmp";
                $sqlWhere .= $sqlTmp.$row['operation'].$row['valueField'];
            }
            else if($row['field_id'] == 'Total')
            {
                //$sqlTmp = ' (SELECT ifnull(SUM(webPoints),0) FROM '.DB_WARPIT_WEBCATI_BASE.'._www_interviewer_statistics WHERE id_user = a.id) + (SELECT ifnull(SUM(webPoints),0) FROM '.DB_WARPIT_WEBCATI_BASE.'._wwwPoints WHERE id_user = a.id)';
                $sqlTmp = ' (SELECT ifnull(SUM(webPoints),0) FROM '.DB_WARPIT_WEBCATI_BASE.'._wwwPoints WHERE id_user = a.id) ';

                $sqlSel .=", $sqlTmp AS totalPoints_tmp";
                $sqlWhere .= $sqlTmp.$row['operation'].$row['valueField'];
            }
            else
            {
                //if checkboxes are selected, and checkboxes have more than 1 input_type we must check the corresponding field
                if($row['field_type'] == 2)
                    $row['field_id'].='_'.$row['valueField'];
                $sqlWhere .= ' '.$row['field_id'].' '.$row['operation'];
                if($row['operation'] == 'LIKE')
                    $sqlWhere  .= ' \'%'.$row['valueField'].'%\'';
                else
                    $sqlWhere  .= ' \''.$row['valueField'].'\'';
            }

            $sqlWhere .= $row['closeBracket'];
            $cnt++;
            //	$sqlStr = $sqlSel . $sqlWhere;
//				$this->arr[] = $row;
        }
        if(is_array($createTmpGroup))
        {
            $crSelect = "SELECT a.id as int_id";
            $crFrom = " FROM _Interviewer a";
            $sqlFrom .= ' INNER JOIN queryMachineTmp ON id = int_id';

            for ($i=0;$i<count($createTmpGroup);$i++)
            {
                $val = $createTmpGroup[$i];
                $crSelect .= ",a$i.id_group as group$val";
                $crFrom .= " LEFT JOIN _group_link a$i ON a.id = a$i.id AND a$i.id_group = $val";
            }
            $this->db->SQLexecute("DROP TEMPORARY TABLE IF EXISTS queryMachineTmp");
            $sql = "CREATE TEMPORARY TABLE queryMachineTmp ".$crSelect.$crFrom;
            If (!$this->db->SQLexecute($sql)){echo '{"success":false,"error":"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
        }
        //LET's create the tmp project table
        if(is_array($createTmpProject))
        {
            $crSelect = "SELECT a.id as intP_id";
            $crFrom = " FROM _Interviewer a";
            $sqlFrom .= ' INNER JOIN queryMachineProjTmp ON id = intP_id';

            for ($i=0;$i<count($createTmpProject);$i++)
            {
                $val = $createTmpProject[$i];
                $crSelect .= ",a".$i.".id_project as project".$val;
                $crFrom .= " LEFT JOIN ".DB_WARPIT_WEBCATI_BASE."._www_ids a$i ON a.id = a$i.id_user AND a$i.id_project = $val";
            }
            $this->db->SQLexecute("DROP TEMPORARY TABLE IF EXISTS queryMachineProjTmp");
            $sql = "CREATE TEMPORARY TABLE queryMachineProjTmp ".$crSelect.$crFrom;
            If (!$this->db->SQLexecute($sql)){echo '{"success":false,"error":"'.$this->db->GetError().'", STATEMENT: "'.$sql.'"}';}
        }

        $_POST['SQL'] = $sqlSel.$sqlFrom;
        $_POST['SQLwhere'] = $sqlWhere;
    }


}

$db->ChangeDatabase($_REQUEST['database']);


$manipulator = new NfieldInterviewers($db, $_REQUEST['RMtable']);

$comm = $_REQUEST['action'] . 'Action';
if (method_exists($manipulator, $comm))
    echo $manipulator->$comm();
else {

    Echo '{success:false,error:"Unknown action!"}';

}


?>
