Account.php

<?php

namespace Aiir\Apps\SocialPublisher\Models;

class Group
{
    private $id;
    private $clientId;
    private $name;

    private $accounts = [];
    private $accountCount;


    public function __construct($data)
    {
        $this->id = (int) $data['id'];
        $this->clientId = (int) $data['client_id'];
        $this->name = (string) $data['name'];

        $this->accounts = (array) $data['accounts'];
        $this->accountCount = (int) $data['account_count'];
    }


    public function setId(int $id)
    {
        $this->id = $id;
    }


    public function getId()
    {
        return $this->id;
    }


    public function getClientId()
    {
        return $this->clientId;
    }


    public function getName()
    {
        return $this->name;
    }


    public function setName(string $name)
    {
        $this->name = $name;
    }


    public function getAccountCount()
    {
       return $this->accountCount;
    }


    public function setAccounts(array $accounts)
    {
        $this->accounts = $accounts;
    }


    public function getAccounts()
    {
        return $this->accounts;
    }

}

AccountDAO.php

<?php

namespace Aiir\Apps\SocialPublisher;

use Aiir\Apps\SocialPublisher\Models\Account;
use Aiir\Apps\SocialPublisher\Models\Group;

class AccountDAO
{
    private $db;


    /**
     * GroupDAO constructor.
     * @param $db
     */
    public function __construct($db)
    {
        $this->db = $db;
    }


    /**
     * Returns array of Account objects within scope of client
     * @param $clientId
     * @return array
     */
    public function getAllClientAccounts($clientId)
    {
        $accounts = $this->db->query('SELECT * FROM account
                                      WHERE client_id = :client_id
                                      ORDER BY account.name',
            [
                'client_id' => $clientId
            ]);

        $accounts = $this->getAccountObjects($accounts);

        return $accounts;
    }


    /**
     * Add a Group to accounts
     * @param Group $group
     * @return bool
     */

    public function addAccountsToGroup(Group $group)
    {
        $params = [
            'group_id' => $group->getId()
        ];

        $rows = [];

        foreach ($group->getAccounts() as $account) {
            $params['account_' . $account] = $account;
            $rows[] = '(:group_id, :account_' . $account . ')';
        }

        $queryRows = implode(',', $rows);

        $this->db->query('INSERT INTO accgroup_account
                          (group_id, account_id)
                          VALUES ' . $queryRows,
            $params);

        return true;
    }


    /**
     * Deletes Accounts from Groups
     * @param Group $group
     * @return mixed
     */
    public function deleteAccountsFromGroup(Group $group)
    {
        $this->db->query('DELETE FROM accgroup_account
                                    WHERE group_id = :group_id',
            [
                'group_id' => $group->getId()

            ]);

        return true;
    }


    /**
     * Takes an array and returns Account Objects
     * @param array $accounts
     * @return array
     */
    private function getAccountObjects(array $accounts)
    {
        $accountObjects = [];
        foreach ($accounts as $account) {
            $accountObjects[] = new Account($account);
        }

        return $accountObjects;
    }
}

delete_group.php

<?php

/*
 *  Set-up
 */

include_once "../../../common/init.inc.php";

list($myInfo, $siteInfo) = initUser(false);
requirePermissions("siteadmin");

use Aiir\Database\Database;
use Aiir\Apps\SocialPublisher\GroupDAO;

$db = new Database(DB_GLOBAL_AWS_IP);
$db->useDatabase('social');

$groupDAO = new GroupDAO($db);


/*
 *  Delete social group
 */

$result = $groupDAO->deleteGroup($_POST['id'], $siteInfo['clientId']);


/*
 *  Return response from action
 */

header('Content-Type: application/json');
echo json_encode($result);

GroupDAO.php

<?php

namespace Aiir\Apps\SocialPublisher;

use Aiir\Database\Database;
use Aiir\Apps\SocialPublisher\Models\Group;


class GroupDAO
{
    private $db;

    /* Dependency injection */
    public $accountDAO;


    /**
     * GroupDAO constructor.
     * @param Database $db
     */
    public function __construct(Database $db)
    {
        $this->db = $db;
    }


    /**
     * Inject the AccountDAO to access required methods
     * @param AccountDAO $accountDAO
     */
    public function setAccountDAO(AccountDAO $accountDAO)
    {
        $this->accountDAO = $accountDAO;
    }


    /**
     * Returns array of Group objects within scope of client
     * @return array
     */
    public function getAllClientGroups($clientId)
    {
        $groups = $this->db->query('SELECT accgroup.*, COUNT(account_id) AS account_count  FROM accgroup
                                    INNER JOIN accgroup_account
                                        ON accgroup.id = accgroup_account.group_id
                                    WHERE client_id = :client_id
                                    GROUP BY accgroup.id
                                    ORDER BY accgroup.name',
            [
                'client_id' => $clientId
            ]);

        $groups = $this->getGroupObjects($groups);

        return $groups;
    }


    /**
     * Returns a Group object by ID
     * @param int $id
     * @return mixed
     */
    public function getGroupById(int $id, $clientId)
    {
        $group = $this->db->query('SELECT accgroup.*, GROUP_CONCAT(account_id) AS accounts FROM accgroup
                                   INNER JOIN accgroup_account
                                      ON accgroup.id = accgroup_account.group_id
                                   WHERE accgroup.id = :id
                                      AND client_id = :client_id
                                   LIMIT 1',
            [
                'id'        => $id,
                'client_id' => $clientId
            ]);

        $group = $group[0];

        $group['accounts'] = explode(',', $group['accounts']);

        $group = new Group($group);

        return $group;
    }


    /**
     * Create a new record and returns a Group object
     * @param $group
     * @return Group
     */
    public function createGroup(Group $group)
    {
        $this->db->query('INSERT INTO accgroup
                          (name, client_id)
                          VALUES (:name, :client_id)',
            [
                'name'      => $group->getName(),
                'client_id' => $group->getClientId()
            ]);

        $group->setId($this->db->lastInsertId());

        $this->accountDAO->addAccountsToGroup($group);

        return $group;
    }


    /**
     * Update existing record and return object
     * @param Group $group
     * @param array $accounts
     * @return Group
     */
    public function updateGroup(Group $group, array $accounts)
    {
        $this->db->query('UPDATE accgroup 
                          SET name = :name 
                          WHERE id = :id
                          LIMIT 1',
            [
                'id'   => $group->getId(),
                'name' => $group->getName()
            ]);

        $this->accountDAO->deleteAccountsFromGroup($group);

        $group->setAccounts($accounts);
        $this->accountDAO->addAccountsToGroup($group, $accounts);

        return $group;
    }


    /**
     * Delete group from the database
     * @param int $id
     * @param $clientId
     * @return bool
     */
    public function deleteGroup(int $id, $clientId)
    {
        $this->db->query('DELETE FROM accgroup
                                WHERE id = :id
                                  AND client_id = :client_id',
            [
                'id'        => $id,
                'client_id' => $clientId
            ]);

        return true;
    }


    /**
     * Takes an array and returns Group objects
     * @param array $groups
     * @return array
     */
    private function getGroupObjects(array $groups)
    {
        $groupObjects = [];
        foreach ($groups as $group) {
            $groupObjects[] = new Group($group);
        }

        return $groupObjects;
    }
}

groups.inc.php

<?php

/*
 *  Set-up the page
 */

use Aiir\Database\Database as Database;
use Aiir\Apps\SocialPublisher\GroupDAO;

$db = new Database(DB_GLOBAL_AWS_IP);
$db->useDatabase('social');

$groupDAO = new GroupDAO($db);
$groups = $groupDAO->getAllClientGroups($siteInfo['clientId']);


/*
 *  Manage an existing group
 */

$twig = new Twig_Environment(new Twig_Loader_Filesystem("twigs"));

echo $twig->render('groups.twig', [
    'groups' => $groups
]);

save_group.php

<?php

/*
 *  Set-up
 */

include_once "../../../common/init.inc.php";

list($myInfo, $siteInfo) = initUser(false);
requirePermissions("siteadmin");

use Aiir\Database\Database;
use Aiir\Apps\SocialPublisher\GroupDAO;
use Aiir\Apps\SocialPublisher\AccountDAO;
use Aiir\Apps\SocialPublisher\Models\Group;

$db = new Database(DB_GLOBAL_AWS_IP);
$db->useDatabase('social');

$groupDAO = new GroupDAO($db);
$accountDAO = new AccountDAO($db);
$groupDAO->setAccountDAO($accountDAO);



/*
 *  Save group
 */

if (empty($_POST['id'])) {

    $group = new Group([
        'name' => $_POST['name'],
        'client_id' => $siteInfo['clientId'],
        'accounts' => $_POST['accounts']
    ]);

    $group = $groupDAO->createGroup($group);

} else {

    $group = $groupDAO->getGroupById($_POST['id'], $siteInfo['clientId']);
    $group->setName($_POST['name']);
    $group = $groupDAO->updateGroup($group, $_POST['accounts']);

}


/*
 *  Return response from action
 */

header('Content-Type: application/json');
echo json_encode([
    'id' => $group->getId(),
    'name' => $group->getName(),
    'accounts' => count($group->getAccounts())
]);