<?php

/**
 * Class that operate on table 'relusergroupproperties'. Database Mysql.
 *
 * @author: http://phpdao.com
 * @date: 2013-04-06 10:57
 */
class RelusergrouppropertiesMySqlExtDAO extends RelusergrouppropertiesMySqlDAO {

    public function queryAllwithUsergroupid($id) {//usergroupadmin.isallowed=0 is allowed ,1 not allowed=>case with value = 1 will be deleted in edit
        $sql = 'SELECT properties.propertyid,
                relusergroupproperties.relusergrouppropertiesid,
                relusergroupproperties.propertyvalue,
                relusergroupproperties.theorder,
                properties.propertyname , properties.propertyparent,
                usergroupadmin.isallowed

                FROM (select * from relusergroupproperties where relusergroupproperties.usergroupid=' . $id . ') as relusergroupproperties
                right join properties	on relusergroupproperties.propertyid = properties.propertyid
                left join usergroupadmin on usergroupadmin.propertyid = properties.propertyid

                ORDER BY case when properties.propertyparent=0 then properties.propertyid else properties.propertyparent end * 1000 + properties.propertyid ASC'
        //to order by parent then child?
        //This works nicely as long as the table is in alfabetical order, and you don't have more than 999 rows
        ;
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllwithUsergroupidAndValue($id) {
        $sql = 'SELECT relusergroupproperties.propertyid, relusergroupproperties.propertyvalue, properties.propertyname, properties.propertyurl FROM relusergroupproperties join properties
		on relusergroupproperties.propertyid = properties.propertyid
		where relusergroupproperties.usergroupid=' . $id . ' and propertyvalue=0 and properties.propertyparent=0 order by relusergroupproperties.theorder,relusergroupproperties.propertyid ASC';
        //print($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function queryAllwithUsergroupidAndPropertyparent($id, $parent) {
        $sql = 'SELECT relusergroupproperties.propertyid, relusergroupproperties.propertyvalue, properties.propertyname, properties.propertyurl  FROM relusergroupproperties join properties
		on relusergroupproperties.propertyid = properties.propertyid
		where relusergroupproperties.usergroupid=' . $id . ' and propertyvalue=0 and properties.propertyparent=' . $parent . ' order by relusergroupproperties.theorder,relusergroupproperties.propertyid ASC';
        //print($sql);
        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function loadWithUsergroupidAndPropertyid($usergroupid, $propertyid) {
        $sql = 'SELECT relusergroupproperties.propertyid, relusergroupproperties.propertyvalue
                FROM relusergroupproperties
		where relusergroupproperties.usergroupid=' . $usergroupid . ' and relusergroupproperties.propertyid=' . $propertyid . ' limit 1';
        //print($sql . "<br/>");
        $sqlQuery = new SqlQuery($sql);
        return $this->getRow($sqlQuery);
    }

    public function queryAllwithUsergroupidAndURL($id, $url) {
        $sql = 'SELECT relusergroupproperties.propertyid, relusergroupproperties.propertyvalue, properties.propertyname, properties.propertyurl FROM relusergroupproperties join properties
		on relusergroupproperties.propertyid = properties.propertyid
		where relusergroupproperties.usergroupid=' . $id . ' and propertyvalue=0 and properties.propertyurl="' . $url . '"';

        $sqlQuery = new SqlQuery($sql);
        return $this->getList($sqlQuery);
    }

    public function insertBulk($insert_sql) {
        $sql = $insert_sql;
        $sqlQuery = new SqlQuery($sql);
        $affected_rows = $this->executeUpdate($sqlQuery);
        return $affected_rows;
    }

    public function insertSpecial() {
        $sql = 'INSERT IGNORE INTO relusergroupproperties (usergroupid, propertyid, propertyvalue, theorder)
                SELECT -1,propertyid , 0,0
                from properties';
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

    public function setAllControllersAllowedForGroup($groupId) {
        $sql = 'UPDATE relusergroupproperties SET propertyvalue = 0 where usergroupid = ' . $groupId;
        $sqlQuery = new SqlQuery($sql);
        return $this->executeUpdate($sqlQuery);
    }

}

?>