<?php

define("TOTAL_SQL", "(SELECT ifnull(SUM(webPoints),0) FROM " . DB_WARPIT_WEBCATI_BASE . "._wwwPoints as points JOIN " . DB_WARPIT_WEBCATI_BASE . "._wwwPurpose as purpose ON points.id_purpose = purpose.id WHERE purpose.negative  > 0 AND points.id_user = a.id)");

define("USED_SQL", "(SELECT ifnull(SUM(webPoints),0) FROM " . DB_WARPIT_WEBCATI_BASE . "._wwwPoints as points JOIN " . DB_WARPIT_WEBCATI_BASE . "._wwwPurpose as purpose ON points.id_purpose = purpose.id WHERE purpose.negative  < 0 AND points.id_user = a.id)");

define("GAINED_SQL", "(SELECT ifnull(SUM(webPoints * negative),0) FROM " . DB_WARPIT_WEBCATI_BASE . "._wwwPoints as points JOIN " . DB_WARPIT_WEBCATI_BASE . "._wwwPurpose as purpose ON points.id_purpose = purpose.id WHERE points.id_user = a.id)");


class WebPoints
{
    private $db;
    
    private $pointsTable;
    
    private $purposeTable;
    
    private $config;
    
	/*
	 * Tole sem dodal na tak način zato, da se bo vedelo na kateri class se te konstane navezujejo
	 * 
	 * */
	const gainedSQL = GAINED_SQL;
	
	const totalSQL = TOTAL_SQL;
	
	const usedSQL = USED_SQL;
	
    public function __construct($db)
    {
		
        $this->db = $db;
        $this->pointsTable = DB_WARPIT_WEBCATI_BASE . "._wwwPoints";
        $this->purposeTable = DB_WARPIT_WEBCATI_BASE . "._wwwPurpose";
        
        configLoader::$db = $db;

        $this->db->ChangeDatabase(DB_WARPIT_MAIN);
        $this->config = configLoader::loadConfig('incentive');
        
    }
    
    ###################### PUBLICS ######################
    
    public function getUsersWithPoints($where=null, $limit = null,$sort = null, $id_group = null)
    {
      /*  $sql = "SELECT * FROM(SELECT a.email, a.id, SUM(b.webPoints * c.negative) as points 
                            FROM " . DB_WARPIT_USERSPOOL . "._Interviewer AS a"; 
        if(!is_null($id_group))
        {
             $sql .= " JOIN " . DB_WARPIT_USERSPOOL . "._group_link AS d ON a.id = d.id ";     
             $sql .= " LEFT ";
        }
        $sql .= " JOIN " . $this->pointsTable . " AS b ON a.id=b.id_user"; 
        $sql .= " LEFT JOIN " . $this->purposeTable . " AS c ON b.id_purpose = c.id WHERE b.deleted = 0 GROUP BY a.email) as a";
        */
        
        $sql = "SELECT * FROM (SELECT a.email, a.id, SUM(b.webpoints * c.negative) as points
                    FROM " . DB_WARPIT_USERSPOOL . "._Interviewer AS a "; 
         
        if(!is_null($id_group))
            $sql .= " JOIN " . DB_WARPIT_USERSPOOL . "._group_link AS d ON a.id = d.id AND d.id_group = " . intval($id_group) . " LEFT ";
        
        $sql .= " JOIN " . DB_WARPIT_WEBCATI_BASE . "._wwwPoints AS b ON a.id=b.id_user  AND b.deleted = 0
                    LEFT JOIN " . DB_WARPIT_WEBCATI_BASE . "._wwwPurpose AS c ON b.id_purpose = c.id 
                    GROUP BY a.email) as a";                    
                
        if($where)
            $sql .= " WHERE " . $where;
        
        if(strlen($sort) > 0) $sql .= $sort.  " ";
        $results = $this->getNumResults($sql);
        if(strlen($limit) > 0)
            $sql .= $limit;
        
		//print $sql; exit;
        $rtnData = $this->returnResults($sql, $results, FALSE);
        $rtnData['where'] = $where;
        return json_encode($rtnData);
        
    }
    
    
    public function getUsersWithoutPoints($where=null, $limit=null, $default_state_id = 7)
    {
        $sql = "SELECT * FROM (SELECT a.id, a.email, 0 AS points, b.webPoints
                    FROM " . DB_WARPIT_USERSPOOL . "._Interviewer AS a
                    LEFT JOIN " . $this->pointsTable . " AS b ON a.id = b.id_user
                    WHERE (
                        id_purpose <> " . intval($default_state_id) . "
                        OR b.id IS NULL
                    )
                    GROUP BY a.email) as a";
                
        if($where)
            $sql .= " WHERE " . $where;
            
        $results = $this->getNumResults($sql);
        if(strlen($limit) > 0)
            $sql .= $limit;
        
        return $this->returnResults($sql, $results, TRUE);      
    }
	
	public function getPoints($where)
	{
		$sql = "SELECT a.id_user, SUM(a.webPoints * b.negative) as points
                FROM " . $this->pointsTable . " as a  
                JOIN " . $this->purposeTable . " as b
                ON a.id_purpose = b.id";
		
		if(is_array($where))
			$sql .= implode(" AND ", $where);		
		else
			$sql .= $where;
		
		if(!$res = $this->db->SQLexecute($sql))
            die($this->db->JsonError());
        $row = $this->db->fetchAssoc($res);
       
        return intval($row['points']);
		
	}
    
    public function getTotalPoints($userId)
    {
        // prvi sql je bil namenjen izračunu točk za star sistem. Uporabljal se je pri pošiljanju zbranih točk uporabnika  preko emaila.
       // $sql = "SELECT SUM(webPoints) as points FROM Warpit_Webcati_base._wwwPoints WHERE id_user = " . intval($userId) . " AND deleted = 0";
        
        return $this->getPoints(" WHERE b.negative > 0 and a.deleted = 0 AND a.id_user = " . intval($userId));
        
    }
	
    public function getGainedPoints($userId)
    {
     	return $this->getPoints(" WHERE a.deleted = 0 and a.id_user = " . intval($userId));
    }
    public function getUsedPoints($userId)
    {
        return $this->getPoints(" WHERE b.negative < 0 and a.deleted = 0 AND a.id_user = " . intval($userId)) * -1;
    }
    
    public function getPointsReport($userId, $limit = "", $sort = "")
    {
        $sql = "SELECT a.id as id, a.webPoints * b.negative as points, b.label, a.modificationDate, IF(LENGTH(c.naziv_proj)>0 ,c.naziv_proj, c.proj_name) as naziv_proj FROM " . $this->pointsTable . " AS a 
                LEFT JOIN " . $this->purposeTable . " AS b ON a.id_purpose = b.id
                LEFT JOIN " . DB_WARPIT_WEBCATI_BASE . "._Projects AS c ON a.id_project = c.id
                WHERE a.id_user = " . $userId . " AND a.deleted = 0";
                
		if(strlen($sort) > 0) $sql .= $sort;
		if(strlen($limit) > 0) $sql .= $limit;
		
                print "/* $sql*/";
                
        return $this->returnResults($sql, null, TRUE);
    }
    
  /*  public function recalculatePointsForProject($id_project)
    {
        $sql = "SELECT proj_name FROM  ".DB_WARPIT_WEBCATI_BASE."._Projects WHERE id = " . $id_project;
        $res = $this->db->SQLexecute($sql);
        $zad = $this->db->fetchAssoc($res);
        $projname = $zad['proj_name'];
        
        $sql = "DELETE FROM " . $this->pointsTable . "  WHERE id_project = " . $id_project;
        $this->db->SQLexecute($sql);
        
        $sql = "SELECT * FROM ".DB_WARPIT_WEBCATI_BASE."._wwwPointsStructure WHERE id_project = " . $id_project . ". AND enabled = 1 ORDER BY priority";
        $webPoints = $this->db->SQLexecute($sql);
        
        while($wpRow = $this->db->fetchAssoc($webPoints))
        {
            $points = $wpRow['setpoints'];
            $cnd = $wpRow['sql_condition'];
            
            $sql = "INSERT IGNORE INTO " . $this->pointsTable . " (id_project,id_user,webPoints, modificationDate, id_purpose) 
                        SELECT " . $id_project . ", user_id, " . $points . ", UNIX_TIMESTAMP(), " . intval($this->config['questioner_purpose_id']) . " FROM ".DB_WARPIT_WEBCATI_BASE . "." . $projname . "_structure WHERE (" . $cnd . ")";
                //    print "/*" . $sql . "<br>";
            $this->db->SQLexecute($sql);
            
        }
        
        $rtnData['success'] = TRUE;
        $rtnData['STATMENT'] = $sql;
        return json_encode($rtnData); 
    }
   */
   
    //20151013 Tanis I have add id_record so I can call this function also from engine
    public function recalculatePointsForProject($id_project, $id_record = null)
    {
        $sql = "SELECT proj_name FROM  ".DB_WARPIT_WEBCATI_BASE."._Projects WHERE id = " . $id_project;
        $res = $this->db->SQLexecute($sql);
        $zad = $this->db->fetchAssoc($res);
        $projname = $zad['proj_name'];
        
		//20151013 Tanis I have add id_record so I can call this function also from engine
		if(is_null($id_record))
		{
			$sql = "DELETE FROM " . $this->pointsTable . "  WHERE id_project = " . $id_project;
		//	print $sql;
        	$this->db->SQLexecute($sql);	
		}
        
        
        $sql = "SELECT * FROM ".DB_WARPIT_WEBCATI_BASE."._wwwPointsStructure WHERE id_project = " . $id_project . ". AND enabled = 1 ORDER BY priority";
        $webPoints = $this->db->SQLexecute($sql);
        
        while($wpRow = $this->db->fetchAssoc($webPoints))
        {
            $points = $wpRow['setpoints'];
            $cnd = $wpRow['sql_condition'];
            
            $sql = "INSERT IGNORE INTO " . $this->pointsTable . " (id_project,id_user,webPoints, modificationDate, id_purpose) 
                        SELECT " . $id_project . ", user_id, " . $points . ", UNIX_TIMESTAMP(), " . intval($this->config['questioner_purpose_id']) . " FROM ".DB_WARPIT_WEBCATI_BASE . "." . $projname . "_structure WHERE (" . $cnd . ")";
            //20151013 Tanis I have add id_record so I can call this function also from engine
            if(!is_null($id_record))
				$sql .= " AND id = " . $id_record;
            //20151013 Tanis END
               //     print "/*" . $sql . "<br>*/";
            $this->db->SQLexecute($sql);
            
        }
        
        $rtnData['success'] = TRUE;
        $rtnData['STATMENT'] = $sql;
        return json_encode($rtnData); 
    }
    
    public function addPointsToUser($userIds, $purpose, $numberOfPoints, $project_id = 0)
    {
        
      //  $tmpSql[] = " id_user = " . intval($userId);
        $tmpSql[] = " webPoints = " . intval($numberOfPoints);
        $tmpSql[] = ' modificationDate = UNIX_TIMESTAMP()';
        $tmpSql[] = " id_project = " . $project_id;
        $id_purpose;
        
        //if $purpose is numeric means we got id. So we can insert it directly
        if(is_numeric($purpose))
            $id_purpose = intval($purpose);                
        else 
        {
            //if we get string we first have to check if this purpose already exist. If exist we just get id of it otherwise we insert new purpose
            $id_purpose = $this->db->get($this->purposeTable, "label = '" . trim($purpose) . "'", id, DB_WARPIT_WEBCATI_BASE);   
            if($id_purpose)
                $id_purpose = intval($id_purpose[0]);
            else 
            {
                $sql = "INSERT INTO " . $this->purposeTable . " SET label = '" . mysql_real_escape_string(trim($purpose)) . "', negative = 1";  
                if(!$res = $this->db->SQLexecute($sql))
                    die($this->db->JsonError());
                
                $id_purpose = intval($this->db->GetLastInsertId());
            }
        }
        $tmpSql[] = " id_purpose = " . intval($id_purpose);
        
        
        foreach($userIds as $userId)
        {
            $sql = "INSERT INTO " . $this->pointsTable . " SET " . implode(',', $tmpSql). ", id_user = " . $userId;
         //   print $sql;
            if(!$res = $this->db->SQLexecute($sql))
                die($this->db->JsonError());    
        }
        
        
        $rtnData['success'] = TRUE;
        $rtnData['STATMENT'] = $sql;
        return json_encode($rtnData); 
        
    }

    public function getPurposesList()
    {
        $sql = "SELECT *, if(negative = -1,0,1) as negative FROM " . $this->purposeTable . " WHERE deleted = 0 AND hidden = 0";
        //var_dump($this->returnResults($sql, null, true));
        return $this->returnResults($sql, null, true);
    }
    
    public function removePurpose($ids)
    {
        $sql = "UPDATE " . $this->purposeTable . " SET deleted = 1 WHERE id IN(" . $ids . ")";
        if(!$res = $this->db->SQLexecute($sql))
            return $this->db->JsonError();
        
        $rtnData['success'] = TRUE;
        $rtnData['STATMENT'] = $sql;
        return json_encode($rtnData); 
    }
    
    public function removePoints($ids)
    {
        $sql = "UPDATE " . $this->pointsTable . " SET deleted = 1 WHERE id IN(" . $ids . ")";
        if(!$res = $this->db->SQLexecute($sql))
            die($this->db->JsonError());
        
        $rtnData['success'] = TRUE;
        $rtnData['STATMENT'] = $sql;
        return json_encode($rtnData); 
    }

    public function getAvaibleCoupons($id_user, $id_project)
    {
        $points = $this->getPointsByUserId($id_user);
        return $this->selectCoupons(" WHERE id_user = 0 AND incentive_points <= " . $points . " AND id_incentive_group = " . intval($id_project) . " GROUP BY incentive_points", TRUE);        
    }
    
    public function useCoupon($id_user, $coupon)
    {
        $delPart = "";
        if(intval($coupon['delete_after_download']) == 1)
            $delPart = " ,incentive_code = ''";
        
        $sql = "UPDATE " . DB_WARPIT_USERSPOOL . "._IncentiveCoupons SET id_user = " . $id_user . ", endTime = NOW() " . $delPart . " WHERE id = " . $coupon['coupon_id'];
        if(!$res = $this->db->SQLexecute($sql))
            die($this->db->JsonError());
        
    }
    
    public function getCouponsForPoints($id_project, $points)
    {
        return $this->selectCoupons("*, a.id as coupon_id","  LEFT JOIN " . DB_WARPIT_USERSPOOL . "._IncentiveProperties as b ON a.id_incentive_group = b.id 
                                             WHERE a.id_user = 0 AND a.incentive_points = " . $points . " 
                                             AND a.id_incentive_group = " . intval($id_project) . " 
                                             ORDER BY a.id ASC LIMIT 1", FALSE);
    }
    
    public function getCouponForUser($id_user, $id_project)
    {
       // $id_user = 1;
        $points = $this->getPointsByUserId($id_user);
        $label = $this->db->get('_IncentiveProperties',$id_project,'project_label',DB_WARPIT_USERSPOOL);
       // var_dump($id_project);
        $coupons = $this->selectCoupons("*"," LEFT JOIN " . DB_WARPIT_USERSPOOL . "._IncentiveProperties as b ON a.id_incentive_group = b.id 
                                                WHERE a.id_user = 0 AND a.incentive_points <= " . $points . " 
                                                AND a.id_incentive_group = " . intval($id_project) . "
                                                AND NOW() BETWEEN b.start_date AND b.end_date 
                                                AND allow_download = 1
                                                GROUP BY a.incentive_points", 
                                                FALSE);
      
        $result = array();
        
        foreach($coupons['rows'] as $coupon)
            $result[] = array($coupon['incentive_points'], $this->parseLabel($label, $coupon['incentive_points']));
        
        return $result;
    }

    ###################### PRIVATES ######################

    private function parseLabel($label, $ponts)
    {
        $exploded = explode('|', $label,2);
        return $exploded[0] . " " . $ponts . " " . $exploded[1];
    }
    
    private function returnResults($sql, $results = null, $json = null)
    {
        if(!$res = $this->db->SQLexecute($sql))
            die($this->db->JsonError());
        
        $rows = array();
        while($row = $this->db->fetchAssoc($res))
            $rows[] = $row;
        
        $rtnData['success'] = TRUE;
        $rtnData['STATMENT'] = $sql;
        $rtnData['results'] = $results;
        $rtnData['rows'] = $rows;
        if($json)
            return json_encode($rtnData);
        else 
            return $rtnData;            
    }
    
    private function getNumResults($sql)
    {
        if(!$rsCount = $this->db->SQLexecute($sql))
            return false;

        return $this->db->NumRows($rsCount);
    }
    
    private function selectCoupons($columns, $where, $json)
    {
        $sql = "SELECT " . $columns ."
                FROM " . DB_WARPIT_USERSPOOL . "._IncentiveCoupons AS a" . $where;
				
                
        return $this->returnResults($sql, null, $json);
    }
}
?>