Symfony Profiler

<?php
namespace App\Controller;
use App\Entity\PublicKey;
use App\Form\ClientAccountFormType;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\Mailer\MailerInterface;
use Symfony\Component\Security\Core\User\UserInterface;
/**
* Class ClientController
* @package App\Controller
*/
class ClientController extends BaseController
{
/**
* Client List
*/
public function index()
{
$sql = "SELECT
client_key,
name,
is_active,
copyright,
has_consolidated_supporting_feed,
isin_access
FROM feeds.CLIENT
ORDER BY is_active DESC, name";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->execute();
$clients = $stmt->fetchAll();
$data = array(
'clients' => $clients
);
return $this->render('client/list.html.twig', $data);
}
public function viewAccount(Request $request, $accountKey)
{
$sql = "SELECT
client_account_key,
client_name,
description,
account_name,
password,
start_date,
expire_date,
contact,
ip_whitelist,
notes,
is_active
FROM feeds.CLIENT_ACCOUNT
WHERE client_account_key = :account_key";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':account_key', $accountKey, \PDO::PARAM_INT);
$stmt->execute();
$data['accounts'] = [];
while ($row = $stmt->fetch()) {
$data['account'] = $row;
}
$sql = "SELECT
key_string,
key_type
FROM feeds.CLIENT_ACCOUNT_TO_ACCESS
WHERE client_account_fkey = :account_key";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':account_key', $accountKey, \PDO::PARAM_INT);
$stmt->execute();
$data['keys'] = [];
while ($row = $stmt->fetch()) {
$data['keys'][] = $row;
}
return $this->render('client/account_view.html.twig', $data);
}
/**
* @return \Symfony\Component\HttpFoundation\Response
*/
public function showAccount()
{
$sql = "SELECT
CA.client_account_key,
CA.client_name,
CA.account_name,
CA.password,
(SELECT
GROUP_CONCAT(CTH.hostname SEPARATOR ',')
FROM feeds.CLIENT_TO_HOSTNAME AS CTH
WHERE CTH.client_account_fkey = CA.client_account_key
) AS hostnames,
CA.start_date,
CA.expire_date,
CA.contact,
CA.notes,
CA.is_active
FROM feeds.CLIENT_ACCOUNT AS CA
ORDER BY CA.is_active DESC, CA.client_name";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->execute();
$data['accounts'] = [];
while ($row = $stmt->fetch()) {
$row['hostnames'] = explode(',', $row['hostnames']);
$data['accounts'][] = $row;
}
return $this->render('client/account.html.twig', $data);
}
/**
* @param Request $request
* @param null $accountKey
* @return \Symfony\Component\HttpFoundation\RedirectResponse|\Symfony\Component\HttpFoundation\Response
* @throws \Exception
*/
public function editAccount(Request $request, MailerInterface $mailer, UserInterface $user, $accountKey = null)
{
$account = [];
if ($accountKey) {
$sql = "SELECT
CA.client_account_key,
CA.client_name,
CA.description,
CA.account_name,
(SELECT
GROUP_CONCAT(CTH.hostname SEPARATOR ',')
FROM feeds.CLIENT_TO_HOSTNAME AS CTH
WHERE CTH.client_account_fkey = CA.client_account_key
) AS hostnames,
CA.start_date,
CA.expire_date,
CA.contact,
CA.notes,
CA.ip_whitelist,
CA.is_active
FROM feeds.CLIENT_ACCOUNT AS CA
WHERE client_account_key = :account_key";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':account_key', $accountKey, \PDO::PARAM_INT);
$stmt->execute();
$account = $stmt->fetch();
$account['hostnames'] = explode(',', $account['hostnames']);
$account['expire_date'] = $account['expire_date'] ? new \DateTime($account['expire_date']) : null;
}
!empty($account['hostnames']) ?: $account['hostnames'] = ["", "", ""];
$sql = "SELECT
key_string,
key_type,
key_hash
FROM feeds.CLIENT_ACCOUNT_TO_ACCESS
WHERE client_account_fkey = :account_key
";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':account_key', $accountKey, \PDO::PARAM_INT);
$stmt->execute();
$keys = [];
while ($row = $stmt->fetch()) {
$key = new PublicKey();
$key->setKeyString($row['key_string']);
$key->setKeyType($row['key_type']);
$keys[] = $key;
}
if (empty($keys) && !$accountKey) {
$keys = [new PublicKey()];
}elseif(empty($keys)){
$keys = [];
}
$account['public_keys'] = $keys;
$form = $this->createForm(ClientAccountFormType::class, $account);
$form->handleRequest($request);
if ($form->isSubmitted() && $form->isValid()) {
$sql = "INSERT INTO feeds.CLIENT_ACCOUNT
SET client_account_key = :client_account_key,
client_name = :client_name,
description = :description,
account_name = :account_name,
expire_date = :expire_date,
contact = :contact,
ip_whitelist = :ip_whitelist,
notes = :notes,
is_active = :is_active,
meta_date_time_created = NOW()
ON DUPLICATE KEY UPDATE
client_name = VALUES(client_name),
description = VALUES(description),
account_name = VALUES(account_name),
expire_date = VALUES(expire_date),
contact = VALUES(contact),
ip_whitelist = VALUES(ip_whitelist),
notes = VALUES(notes),
is_active = VALUES(is_active)
";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$expireDate = $form->get('expire_date')->getData() ? $form->get('expire_date')->getData()->format('Y-m-d') : null;
$accountKey = $form->get('client_account_key')->getData();
$stmt->bindValue(':client_account_key', $accountKey, \PDO::PARAM_INT);
$stmt->bindValue(':client_name', $form->get('client_name')->getData(), \PDO::PARAM_STR);
$stmt->bindValue(':description', $form->get('description')->getData(), \PDO::PARAM_STR);
$stmt->bindValue(':account_name', $form->get('account_name')->getData(), \PDO::PARAM_STR);
$stmt->bindValue(':expire_date', $expireDate, \PDO::PARAM_STR);
$stmt->bindValue(':contact', $form->get('contact')->getData(), \PDO::PARAM_STR);
$stmt->bindValue(':ip_whitelist', $form->get('ip_whitelist')->getData(), \PDO::PARAM_STR);
$stmt->bindValue(':notes', $form->get('notes')->getData(), \PDO::PARAM_STR);
$stmt->bindValue(':is_active', $form->get('is_active')->getData(), \PDO::PARAM_INT);
$stmt->execute();
// If we are adding a new account, the key will be empty, so lets get the insert ID to use later
if (!$accountKey) {
$accountKey = $db->lastInsertId();
}
// Remove any previous hostnames that are not in the request
$hostnames = $form->get('hostnames')->getData();
$sql = "DELETE FROM feeds.CLIENT_TO_HOSTNAME
WHERE client_account_fkey = :client_account_fkey
AND hostname NOT IN (:hostnames)";
$db->executeQuery($sql,
array(':client_account_fkey' => $accountKey,
':hostnames' => $hostnames),
array(':client_account_fkey' => \PDO::PARAM_INT,
':hostnames' => \Doctrine\DBAL\Connection::PARAM_STR_ARRAY));
$values = [];
$params = [];
$types = [];
foreach ($hostnames as $key => $hostname) {
$values[] = "(:client_account_fkey{$key}, :hostname{$key}, NOW())";
$params[":client_account_fkey{$key}"] = $accountKey;
$types[":client_account_fkey{$key}"] = \PDO::PARAM_INT;
$params[":hostname{$key}"] = $hostname;
$types[":hostname{$key}"] = \PDO::PARAM_STR;
}
// Insert any new host names
$values = implode(',', $values);
$sql = "INSERT IGNORE INTO feeds.CLIENT_TO_HOSTNAME
(client_account_fkey,
hostname,
meta_date_time_created)
VALUES {$values}";
$db->executeQuery($sql, $params, $types);
// clear previous keys
$sql = "DELETE FROM feeds.CLIENT_ACCOUNT_TO_ACCESS
WHERE client_account_fkey = :client_account_fkey";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':client_account_fkey', $accountKey, \PDO::PARAM_INT);
$stmt->execute();
// update public keys
$keys = $form->get('public_keys')->getData();
foreach ($keys as $key) {
$sql = "INSERT INTO feeds.CLIENT_ACCOUNT_TO_ACCESS
SET client_account_fkey = :client_account_fkey,
key_string = :key_string,
key_type = :key_type,
key_hash = :key_hash,
meta_date_time_created = NOW()
ON DUPLICATE KEY UPDATE
key_string = VALUES(key_string),
key_type = VALUES(key_type),
key_hash = VALUES(key_hash)";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':client_account_fkey', $accountKey, \PDO::PARAM_INT);
$stmt->bindValue(':key_string', $key->getKeyString(), \PDO::PARAM_STR);
$stmt->bindValue(':key_type', $key->getKeyType(), \PDO::PARAM_STR);
$key->setKeyHash();
$stmt->bindValue(':key_hash', $key->getKeyHash(), \PDO::PARAM_STR);
$stmt->execute();
}
$this->addFlash('success', 'Account successfully saved');
/* Disable client and its feed when marking account status as inactive
* AND this client does not have multiple active accounts
* */
$clientName = $form->get('client_name')->getData();
$isActive = (int)$form->get('is_active')->getData();
if ($isActive === 0 && $this->hasMultipleActiveAccount($clientName) === false){
$clientKey = $this->getClientKeyByName($clientName);
$activeChildren = array_filter($this->getChildren($clientKey), function($child) {
return $child['is_active'];
});
if (empty($activeChildren)) {
$this->disableClientFeed($mailer, $user, $request, $clientKey);
$this->addFlash('success', "Account is marked as inactive and All the feeds associated with $clientName are disabled as well");
} else {
$this->addFlash('warning', "Account is marked as inactive BUT the feeds associated with $clientName are still active because this Client has active Children");
}
}
return $this->redirectToRoute('client_account_list');
}
return $this->render('client/account_edit.html.twig', [
'clientAccountForm' => $form->createView()
]);
}
public function removeAccount($accountKey, MailerInterface $mailer, Request $request, UserInterface $user)
{
$sql = "UPDATE feeds.CLIENT_ACCOUNT
SET is_active = 0
WHERE client_account_key = ?";
$db = $this->getDoctrine()->getConnection();
$db->executeQuery($sql, [$accountKey], [\PDO::PARAM_INT]);
$this->addFlash('success', 'Account successfully removed');
$sql = "SELECT client_name
FROM feeds.CLIENT_ACCOUNT
WHERE client_account_key = :client_account_key";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(":client_account_key", $accountKey, \PDO::PARAM_INT);
$stmt->execute();
$clientName = $stmt->fetchColumn();
/* Disable client and its feed only if this client does not have multiple active accounts
*/
if ($this->hasMultipleActiveAccount($clientName) === false){
$clientKey = $this->getClientKeyByName($clientName);
$activeChildren = array_filter($this->getChildren($clientKey), function($child) {
return $child['is_active'];
});
if (empty($activeChildren)) {
$this->disableClientFeed($mailer, $user, $request, $clientKey);
$this->addFlash('success', "All the feeds associated with $clientName are disabled as well");
} else {
$this->addFlash('warning', "Account is marked as inactive BUT the feeds associated with $clientName are still active because this Client has active Children");
}
}
return $this->redirectToRoute('client_account_list');
}
/**
* @param $clientKey
* @return \Symfony\Component\HttpFoundation\RedirectResponse|\Symfony\Component\HttpFoundation\Response
*/
public function getClient($clientKey)
{
$genericClientKey = $this->getGenericClientKey();
$client = $this->getClientInfo($clientKey);
$children = $this->getChildren($clientKey);
$activeChildren = array_filter($children, function($child) {
return $child['is_active'];
});
$data = array(
'client' => $client,
'children' => $children,
'has_active_children' => (bool) count($activeChildren),
'client_key' => $clientKey,
'generic_client_key' => $genericClientKey,
'client_list' => $this->getEligibleParents()
);
if (!$client) {
$this->addFlash('error', 'Client doesn\'t exist. Invalid key');
return $this->redirectToRoute('client_list');
} else {
if (!$client['is_active']) {
$this->addFlash('warning', "This client is currently disabled!");
}
$sql = "SELECT
F.feed_number,
F.is_normalized,
CTF.feed_fkey,
CTF.is_active
FROM feeds.CLIENT_TO_FEED AS CTF
JOIN feeds.FEED AS F
ON CTF.feed_fkey = F.feed_key
WHERE CTF.client_fkey = :client_key
ORDER BY IF(CTF.is_active = 1, 1, 0) DESC, F.feed_number";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(":client_key", $clientKey, \PDO::PARAM_INT);
$stmt->execute();
$feeds = $stmt->fetchAll();
$data['feeds'] = $feeds;
$hasActiveFeed = 0;
foreach ($feeds as $feed) {
if ($feed['is_active'] == 1) {
$hasActiveFeed = 1;
break;
}
}
$data['has_active_feed'] = $hasActiveFeed;
//get GENERIC feeds that are not in current client
$sql = "SELECT DISTINCT
F.feed_key,
F.feed_number,
F.is_normalized
FROM feeds.CLIENT_TO_FEED AS CTF
JOIN feeds.FEED AS F
ON CTF.feed_fkey = F.feed_key
WHERE CTF.client_fkey = :generic_client_fkey
AND F.feed_number NOT IN
(
SELECT DISTINCT F1.feed_number
FROM feeds.CLIENT_TO_FEED AS CTF1
JOIN feeds.FEED AS F1
ON CTF1.feed_fkey = F1.feed_key
WHERE CTF1.client_fkey = :client_key
)
ORDER BY F.feed_number";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(":client_key", $clientKey, \PDO::PARAM_INT);
$stmt->bindValue(":generic_client_fkey", $genericClientKey, \PDO::PARAM_INT);
$stmt->execute();
$data['generic_feeds'] = $stmt->fetchAll();
}
return $this->render('client/edit.html.twig', $data);
}
/** Add new client view
*
* @return \Symfony\Component\HttpFoundation\Response
*/
public function addClient()
{
$colMeta = $this->getClientInfo();
$data = array(
'client' => $colMeta,
'client_key' => null,
'children' => null,
'has_active_children' => false,
'has_active_feed' => 0,
'client_list' => $this->getEligibleParents()
);
return $this->render('client/edit.html.twig', $data);
}
/**
* @param Request $request
* @return \Symfony\Component\HttpFoundation\RedirectResponse|\Symfony\Component\HttpFoundation\Response
*/
public function updateClient(Request $request, MailerInterface $mailer, UserInterface $user)
{
$clientKey = $request->request->get("client_key");
$parentClientKey = $request->request->get("parent_client_key");
$clientName = $request->request->get("client_name");
$feedDir = $request->request->get("feed_dir");
$headerType = $request->request->get("header_type");
$fileFormat = $request->request->get("file_format");
$compressionFormat = $request->request->get("compression_format");
$copyright = $request->request->getInt("copyright");
$hasDelta = $request->request->getInt("has_delta");
$isinAccess = $request->request->getInt("isin_access");
$hasConsolidatedSupportingFeed = $request->request->getInt("has_consolidated_supporting_feed");
$isActive = $request->request->getInt("is_active");
$error = false;
if (!$clientName || !$feedDir) {
$this->addFlash('error', "Client Name or Feed Dir must not be blank!");
$error = true;
}
# Validate that this client and the selected parent are not the same
if (($clientKey && $parentClientKey) && ($clientKey == $parentClientKey)) {
$this->addFlash('error', "A Client can not be its own Parent!");
$error = true;
}
# Validate that the parent is not itself a child, and that it is active
if ($parentClientKey) {
$parent = $this->getClientInfo($parentClientKey);
if (!empty($parent['parent_client_fkey'])) {
$this->addFlash('error', "The Parent you selected is a child of another Client. This is not allowed!");
$error = true;
}
if (!$parent['is_active']) {
$this->addFlash('error', "The Parent you selected is inactive. You must make it active to add this Client as a Child.");
$error = true;
}
}
# Validate that if we are marking this client as inactive, none of its children are active
if ($clientKey && !$isActive) {
$activeChildren = array_filter($this->getChildren($clientKey), function($child) {
return $child['is_active'];
});
if (count($activeChildren)) {
$this->addFlash('error', "This Client has child accounts that are active. You can not make this Client inactive.");
$error = true;
}
}
if ($error) {
return $clientKey ? $this->redirectToRoute('client_view', array('clientKey' => $clientKey)) : $this->redirectToRoute('client_add');
}
if ($clientKey) {
$data['client_key'] = $clientKey;
$sql = "SELECT COUNT(*)
FROM feeds.CLIENT_TO_FEED AS CTF
WHERE CTF.client_fkey = :client_key
AND CTF.is_active = 1";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(":client_key", $clientKey, \PDO::PARAM_INT);
$stmt->execute();
$activeFeeds = $stmt->fetchColumn();
if ($activeFeeds && $isActive === 0) {
// Set all feeds associated with this client to inactive
$this->disableClientFeed($mailer, $user, $request, $clientKey);
$this->addFlash('info', 'All feeds for this client have been marked inactive');
}
}
$sql = "INSERT INTO feeds.CLIENT
SET client_key = :client_key,
parent_client_fkey = :parent_client_key,
name =:name,
is_active =:is_active,
feed_dir =:feed_dir,
header_type= :header_type,
copyright =:copyright,
has_delta =:has_delta,
has_consolidated_supporting_feed =:has_consolidated_supporting_feed,
file_format =:file_format,
compression_format =:compression_format,
isin_access =:isin_access,
meta_date_time_created = NOW()
ON DUPLICATE KEY UPDATE
parent_client_fkey = :parent_client_key,
name = VALUES(name),
is_active = VALUES(is_active),
feed_dir = VALUES(feed_dir),
header_type = VALUES(header_type),
copyright = VALUES(copyright),
has_delta = VALUES(has_delta),
has_consolidated_supporting_feed = VALUES(has_consolidated_supporting_feed),
file_format = VALUES(file_format),
compression_format = VALUES(compression_format),
isin_access = VALUES(isin_access),
meta_date_time_changed = VALUES(meta_date_time_changed)";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(":client_key", $clientKey, \PDO::PARAM_INT);
$stmt->bindValue(":parent_client_key", $parentClientKey, \PDO::PARAM_INT);
$stmt->bindValue(":name", $clientName, \PDO::PARAM_STR);
$stmt->bindValue(":is_active", $isActive, \PDO::PARAM_INT);
$stmt->bindValue(":feed_dir", $feedDir, \PDO::PARAM_STR);
$stmt->bindValue(":header_type", $headerType, \PDO::PARAM_STR);
$stmt->bindValue(":copyright", $copyright, \PDO::PARAM_INT);
$stmt->bindValue(":has_delta", $hasDelta, \PDO::PARAM_INT);
$stmt->bindValue(":has_consolidated_supporting_feed", $hasConsolidatedSupportingFeed, \PDO::PARAM_INT);
$stmt->bindValue(":file_format", $fileFormat, \PDO::PARAM_STR);
$stmt->bindValue(":compression_format", $compressionFormat, \PDO::PARAM_STR);
$stmt->bindValue(":isin_access", $isinAccess, \PDO::PARAM_INT);
$stmt->execute();
if (!$clientKey) {
$clientKey = $db->lastInsertId();
}
$this->validateISINInClientFeed($clientKey);
$data['client'] = $this->getClientInfo($clientKey);
$data['client_key'] = $clientKey;
$this->addFlash('success', 'Client Info saved successfully!');
return $this->render('client/edit-post.html.twig', $data);
}
/**
* @param Request $request
* @param $clientKey
* @return mixed
*/
public function updateClientToFeed(Request $request, $clientKey){
$action = $request->request->get("action");
$feedFkey = $request->request->get("feed_fkey");
$feedNumber = $request->request->get("feed_number");
$this->validateISINInClientFeed($clientKey);
if ($action === 'active'){
$this->activeClientToFeed($clientKey, $feedFkey, 1);
$this->addFlash('success', "FEED{$feedNumber} is enabled from this client!");
} elseif ($action === 'inactive'){
$this->activeClientToFeed($clientKey, $feedFkey, 0);
$this->addFlash('success', "FEED{$feedNumber} is disabled from this client!");
}
return $this->redirectToRoute('client_view', array('clientKey' => $clientKey));
}
/** Enable/Disable a feed from client
*
* @param $clientKey
* @param $feedFkey
* @param $active
*/
protected function activeClientToFeed($clientKey, $feedFkey, $active)
{
$sql = "UPDATE feeds.CLIENT_TO_FEED
SET is_active = :active
WHERE client_fkey = :client_fkey
AND feed_fkey = :feed_fkey";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':client_fkey', $clientKey, \PDO::PARAM_INT);
$stmt->bindValue(':feed_fkey', $feedFkey, \PDO::PARAM_INT);
$stmt->bindValue(':active', $active, \PDO::PARAM_INT);
$stmt->execute();
}
/** Add generic feed to client
*
* @param Request $request
* @param $clientKey
*/
public function addGenericFeedToClient(Request $request, $clientKey)
{
$genericClientKey = $this->getGenericClientKey();
$genericFeedFkeys = $request->request->get("generic_feed_fkey", array());
$pitFeedFkeys = isset($genericFeedFkeys['pit'])? array_values($genericFeedFkeys['pit']): array();
$nonPitFeedFkeys = isset($genericFeedFkeys['non_pit'])? array_values($genericFeedFkeys['non_pit']): array();
$formattedFeedFkeys = array();
foreach($pitFeedFkeys as $pitFeedFkey){
$formattedFeedFkeys[$pitFeedFkey] = 1;
}
foreach($nonPitFeedFkeys as $nonPitFeedFkey){
$formattedFeedFkeys[$nonPitFeedFkey] = 0;
}
$hasAccessToISIN = $this->hasAccessToISIN($clientKey);
$feedAdded = array();
foreach($formattedFeedFkeys as $genericFeedFkey => $pit) {
$sql = "SELECT feed_number
FROM feeds.FEED
WHERE feed_key = :generic_feed_fkey";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':generic_feed_fkey', $genericFeedFkey, \PDO::PARAM_INT);
$stmt->execute();
$feedNumber = (int)$stmt->fetchColumn();
//check if generic PIT feed has normalized column to prevent adding it to client feed if missing
$sql ="SELECT COUNT(*)
FROM feeds.FEED_FILE AS FF
JOIN feeds.FEED_FILE_COLUMN AS FFC ON FF.feed_file_key = FFC.feed_file_fkey
WHERE FF.feed_fkey = :feed_key AND FF.is_support_file = 0 AND FFC.is_normalized = 1";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(":feed_key", $genericFeedFkey, \PDO::PARAM_INT);
$stmt->execute();
$hasNormalizedColumn = $stmt->fetchColumn();
if (!$hasNormalizedColumn && $pit){
$this->addFlash('error', "GENERIC PIT feed $feedNumber missing normalized columns! Please fix the GENERIC feed first!");
} else {
$sql = "SELECT COUNT(*)
FROM feeds.CLIENT_TO_FEED AS CTF
JOIN feeds.FEED AS F
ON CTF.feed_fkey = F.feed_key
WHERE F.feed_number = :feed_number
AND CTF.client_fkey = :client_fkey";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':feed_number', $feedNumber, \PDO::PARAM_INT);
$stmt->bindValue(':client_fkey', $clientKey, \PDO::PARAM_INT);
$stmt->execute();
$count = $stmt->fetchColumn();
if (!$count) {
$sql = "INSERT INTO feeds.FEED (feed_number, is_normalized, has_financial_block, meta_date_time_created)
SELECT F.feed_number, :is_normalized , F.has_financial_block, NOW()
FROM feeds.FEED AS F
WHERE F.feed_key = :generic_feed_fkey
LIMIT 1";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':is_normalized', $pit, \PDO::PARAM_INT);
$stmt->bindValue(':generic_feed_fkey', $genericFeedFkey, \PDO::PARAM_INT);
$stmt->execute();
$newFeedFkey = $db->lastInsertId();
if (!$newFeedFkey) {
continue;
}
$sql = "INSERT INTO feeds.CLIENT_TO_FEED (
client_fkey,
feed_fkey,
is_active,
is_normalized,
meta_date_time_created)
SELECT
:client_fkey,
:new_feed_fkey,
is_active,
:is_normalized,
NOW()
FROM feeds.CLIENT_TO_FEED
WHERE feed_fkey = :generic_feed_fkey
AND client_fkey = :generic_client_fkey
LIMIT 1";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':client_fkey', $clientKey, \PDO::PARAM_INT);
$stmt->bindValue(':new_feed_fkey', $newFeedFkey, \PDO::PARAM_INT);
$stmt->bindValue(':is_normalized', $pit, \PDO::PARAM_INT);
$stmt->bindValue(':generic_feed_fkey', $genericFeedFkey, \PDO::PARAM_INT);
$stmt->bindValue(":generic_client_fkey", $genericClientKey, \PDO::PARAM_INT);
$stmt->execute();
$excludeTables = '';
if (!$pit) { // for non-pit, we need to exclude PUBLISHED and FB tables
$excludeTables .= "AND table_name NOT LIKE '%\_PUBLISHED' AND table_name NOT LIKE '%\_FINANCIAL_BLOCK'";
}
//Don't add ISIN/CUSIP support tables if client doesnt have access
//skip DNB table by default as its only for certain clients
if (!$hasAccessToISIN) {
$excludeTables .= "AND table_name NOT LIKE '%ISIN%' AND table_name NOT LIKE '%CUSIP%' AND table_name NOT LIKE '%DNB%'";
}
//Make a copy of feed files from GENERIC
$sql = "SELECT feed_file_key
FROM feeds.FEED_FILE
WHERE feed_fkey = :generic_feed_fkey
{$excludeTables}";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':generic_feed_fkey', $genericFeedFkey, \PDO::PARAM_INT);
$stmt->execute();
$genericfeedFileKeys = $stmt->fetchAll(\PDO::FETCH_COLUMN);
$sql = "SELECT name
FROM feeds.CLIENT
WHERE client_key = :client_key";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(":client_key", $clientKey, \PDO::PARAM_INT);
$stmt->execute();
$clientName = $stmt->fetchColumn();
//remove generic name on client's feed file
$feedNumber = ($feedNumber < 10)? '0'. $feedNumber : $feedNumber;
$feedFileName = 'feed'. $feedNumber . '_' . strtolower($clientName);
// Only include feed files keys if their corresponding feed has at least one normalized column
if ($pit) {
// COUNT combined with GROUP BY will only return rows where the count of rows
// marked as normalized is greater than 0
$sql = "SELECT COUNT(*), feed_file_fkey
FROM feeds.FEED_FILE_COLUMN
WHERE feed_file_fkey IN (?)
AND is_normalized = 1
GROUP BY feed_file_fkey";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->executeQuery($sql, array($genericfeedFileKeys), array(\Doctrine\DBAL\Connection::PARAM_INT_ARRAY));
$genericfeedFileKeys = $stmt->fetchAll(\PDO::FETCH_COLUMN, 1);
}
foreach ($genericfeedFileKeys as $genericfeedFileKey) {
$sql = "INSERT INTO feeds.FEED_FILE (
feed_fkey,
database_name,
source_database_name,
table_name,
file_name,
is_support_file,
meta_date_time_created)
SELECT
:new_feed_fkey,
database_name,
source_database_name,
table_name,
CASE WHEN is_support_file = 1 THEN file_name ELSE :feed_file_name END AS file_name,
is_support_file,
NOW()
FROM feeds.FEED_FILE
WHERE feed_file_key = :generic_feed_file_key
LIMIT 1";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':new_feed_fkey', $newFeedFkey, \PDO::PARAM_INT);
$stmt->bindValue(':generic_feed_file_key', $genericfeedFileKey, \PDO::PARAM_INT);
$stmt->bindValue(':feed_file_name', $feedFileName, \PDO::PARAM_STR);
$stmt->execute();
$newFeedFileKey = $db->lastInsertId();
if (!$newFeedFileKey) {
continue;
}
$sqlWhere = '';
if ($pit){ //For PIT, copy normalized columns from GENERIC
$sqlWhere = "AND FFC.is_normalized = 1 ";
}
if (!$hasAccessToISIN) {
$sqlWhere .= " AND C.select_column NOT IN ('isin', 'cusip_number')";
}
$sql = "INSERT INTO feeds.FEED_FILE_COLUMN (
feed_file_fkey,
column_fkey,
is_primary_key,
is_business_key,
is_proprietary_column,
is_foreign_key,
is_normalized,
is_active,
is_event_date,
is_company_block,
is_financial_block,
is_eol,
has_delta,
select_order,
meta_date_time_created)
SELECT
:new_feed_file_fkey,
FFC.column_fkey,
FFC.is_primary_key,
FFC.is_business_key,
FFC.is_proprietary_column,
FFC.is_foreign_key,
FFC.is_normalized,
FFC.is_active,
FFC.is_event_date,
FFC.is_company_block,
FFC.is_financial_block,
FFC.is_eol,
FFC.has_delta,
FFC.select_order,
NOW()
FROM feeds.FEED_FILE_COLUMN AS FFC
JOIN feeds.FEED_FILE AS FF
ON FF.feed_file_key = FFC.feed_file_fkey
JOIN feeds.FEED_COLUMN AS C
ON FFC.column_fkey = C.column_key
WHERE FFC.feed_file_fkey = :generic_feed_file_fkey
AND FFC.is_eol != 1
AND C.select_column NOT LIKE 'meta%'
{$sqlWhere}";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':new_feed_file_fkey', $newFeedFileKey, \PDO::PARAM_INT);
$stmt->bindValue(':generic_feed_file_fkey', $genericfeedFileKey, \PDO::PARAM_INT);
$stmt->execute();
}
$feedAdded[] = $feedNumber;
}
}
}
if ($feedAdded) {
$feedAddedString = implode(', ', $feedAdded);
$this->addFlash('success', "GENERIC feeds $feedAddedString added to this client!");
}
return $this->redirectToRoute('client_view', array('clientKey' => $clientKey));
}
/**
* @param Request $request
* @return \Symfony\Component\HttpFoundation\Response
*/
public function searchClientFeed(Request $request){
$searchBy = $request->query->get('search_by');
$searchTerm = $request->query->get('search_term');
$searchResult = array();
if ($searchTerm) {
if ($searchBy == 'feed') {
$sql = "SELECT
C.client_key,
C.name,
C.is_active,
F.feed_number,
F.feed_key,
CA.client_account_key,
CA.account_name,
CA.contact,
CTF.is_active AS feed_active
FROM feeds.FEED AS F
JOIN feeds.CLIENT_TO_FEED AS CTF ON CTF.feed_fkey = F.feed_key
JOIN feeds.CLIENT AS C ON CTF.client_fkey = C.client_key
LEFT JOIN feeds.CLIENT_ACCOUNT AS CA ON C.name = CA.client_name
WHERE F.feed_number = :feed_number
ORDER BY C.is_active DESC, CTF.is_active DESC, C.name, F.feed_number";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':feed_number', $searchTerm, \PDO::PARAM_INT);
$stmt->execute();
} elseif ($searchBy == 'column') {
$sql = "SELECT
C.client_key,
C.name,
C.is_active,
F.feed_number,
F.feed_key,
CA.client_account_key,
CA.account_name,
CA.contact,
CTF.is_active AS feed_active
FROM feeds.FEED AS F
JOIN feeds.FEED_FILE AS FF ON F.feed_key = FF.feed_fkey
JOIN feeds.FEED_FILE_COLUMN AS FFC ON FF.feed_file_key = FFC.feed_file_fkey
JOIN feeds.FEED_COLUMN AS FC ON FFC.column_fkey = FC.column_key
JOIN feeds.CLIENT_TO_FEED AS CTF ON CTF.feed_fkey = F.feed_key
JOIN feeds.CLIENT AS C ON CTF.client_fkey = C.client_key
LEFT JOIN feeds.CLIENT_ACCOUNT AS CA ON C.name = CA.client_name
WHERE FC.select_column LIKE :select_column
ORDER BY C.is_active DESC, CTF.is_active DESC, C.name, F.feed_number";
$db = $this->getDoctrine()->getConnection();
$stmt = $db->prepare($sql);
$stmt->bindValue(':select_column', $searchTerm, \PDO::PARAM_STR);
$stmt->execute();
}
while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
$clientKey = $row['client_key'];
$accountKey = $row['client_account_key'];
$feedKey = $row['feed_key'];
$searchResult[$clientKey]['client_key'] = $clientKey;
$searchResult[$clientKey]['is_active'] = $row['is_active'];
$searchResult[$clientKey]['name'] = $row['name'];
$searchResult[$clientKey]['feeds'][$feedKey] = array(
'feed_key' => $feedKey,
'feed_number' => $row['feed_number'],
'feed_active' => $row['feed_active'],
);
$searchResult[$clientKey]['accounts'][$accountKey] = array(
'account_name' => $row['account_name'],
'contact' => $row['contact'],
);
}
}
$data = array(
'search_by' => $searchBy,
'search_term' => $searchTerm,
'search_result' => $searchResult
);
return $this->render('client/search.html.twig', $data);
}
}

src/Controller/ClientController.php line 902