public function getTransactionsByMember(Corporation $corp, array $member_ids, Carbon $date)
{
$start = $date->copy();
$start->subMonth()->setTime(0, 0, 0);
$end = $date->copy();
$end->setTime(23, 59, 59);
$sql = 'SELECT jt.owner_id2, group_concat(DISTINCT jt.id) as ids
FROM journal_transactions as jt
LEFT JOIN accounts as acc on jt.account_id=acc.id
WHERE acc.corporation_id = :corp_id
AND jt.owner_id2 in ( :owner_ids )
AND jt.date >= :start_date
AND jt.date <= :end_date
GROUP BY jt.owner_id2';
$rsm = new ResultSetMappingBuilder($this->getEntityManager());
$rsm->addRootEntityFromClassMetadata('AppBundle\\Entity\\JournalTransaction', 'jt');
$rsm->addFieldResult('jt', 'owner_id2', 'owner_id2');
$rsm->addFieldResult('jt', 'ids', 'id');
$q = $this->getEntityManager()->createNativeQuery($sql, $rsm);
$q->setParameter('corp_id', $corp->getId());
$q->setParameter('owner_ids', $member_ids, Connection::PARAM_INT_ARRAY);
$q->setParameter('start_date', $start);
$q->setParameter('end_date', $end);
$results = $q->getResult();
$real_res = [];
foreach ($results as $res) {
$ids = explode(',', $res->getId());
$rt = $this->createQueryBuilder('jt')->select('sum(jt.amount) as total_amount')->where('jt.id in (:j_ids)')->setParameter('j_ids', $ids)->getQuery()->getResult();
$r = $this->createQueryBuilder('jt')->select('jt')->where('jt.id in (:j_ids)')->setParameter('j_ids', $ids)->getQuery()->getResult();
$real_res[] = ['user' => $res->getOwnerId2(), 'total' => $rt, 'orig_ids' => $r];
}
return $real_res;
}
/**
* Получаем последние диалоги пользователя
*
* @param int $id
* @param int $count
*
* @return array
*/
public function findByDialog($id, $count)
{
$rsm = new ResultSetMappingBuilder($this->getEntityManager());
$rsm->addEntityResult("UserMessagesBundle:Dialog", "d");
$rsm->addFieldResult("d", "id", "id");
$rsm->addFieldResult("d", "flags", "flags");
$rsm->addJoinedEntityResult("UserUserBundle:Users", "du", "d", "usersTo");
$rsm->addFieldResult("du", "users_to_id", "id");
$rsm->addFieldResult("du", "realname", "realname");
$rsm->addFieldResult("du", "path", "path");
$rsm->addJoinedEntityResult("UserMessagesBundle:Messages", "m", "d", "messages");
$rsm->addFieldResult("m", "message_id", "id");
$rsm->addFieldResult("m", "text", "text");
$rsm->addJoinedEntityResult("UserUserBundle:Users", "u", "m", "users");
$rsm->addFieldResult("u", "users_id", "id");
$rsm->addFieldResult("u", "m_realname", "realname");
$rsm->addFieldResult("u", "m_path", "path");
$query = $this->getEntityManager()->createNativeQuery('
SELECT
DISTINCT ON (d.id) d.id,
d.flags,
du.id as users_to_id,
du.realname,
du.path,
m.id as message_id,
m.text,
u.id as users_id,
u.realname as m_realname,
u.path as m_path
FROM dialog d
LEFT JOIN users du ON d.users_to_id = du.id
LEFT JOIN messages m ON d.id = m.dialog_id
LEFT JOIN users u ON m.users_id = u.id
WHERE d.users_id = ?
GROUP BY d.id, d.flags, du.id, m.text, u.id, m.id
LIMIT 20 OFFSET ?
', $rsm)->setParameters([1 => $id, 2 => $count]);
try {
return $query->getArrayResult();
} catch (\Doctrine\ORM\NoResultException $e) {
return null;
}
}
/**
* Get all groups of a kind
*
* @param Kind $kind
* @param integer|null $orgId
* @return mixed
*/
public function groupsOfKind(Kind $kind, $orgId = null)
{
if ($orgId) {
$sql = <<<EOT
SELECT
p.id AS id,
p.lastName,
g.description,
g.avatar
FROM groups g
INNER JOIN parties p ON g.id = p.id
INNER JOIN p2p_relations rel ON p.id = rel.context
WHERE p.kind_id = ? AND rel.reference =?
ORDER BY p.id
EOT;
} else {
$sql = <<<EOT
SELECT
p.id AS id,
p.firstName,
p.lastName,
g.description,
g.avatar
FROM groups g
INNER JOIN parties p ON g.id = p.id
WHERE p.kind_id = ?
ORDER BY p.id
EOT;
}
$rsm = new ResultSetMappingBuilder($this->em);
$rsm->addEntityResult('Bakgat\\Notos\\Domain\\Model\\Identity\\Party', 'p');
$rsm->addEntityResult('Bakgat\\Notos\\Domain\\Model\\Identity\\Group', 'g');
$rsm->addFieldResult('p', 'id', 'id');
$rsm->addFieldResult('p', 'lastName', 'lastName');
$rsm->addFieldResult('g', 'description', 'description');
$qb = $this->em->createNativeQuery($sql, $rsm)->setParameter(1, $kind->id());
if ($orgId) {
$qb->setParameter(2, $orgId);
}
$groups = $qb->getScalarResult();
return $groups;
}
/**
* Find change_beneficiary signatures by client account id
*
* @param int $accountId
* @return DocumentSignature[]
*/
public function findChangeBeneficiaryByClientAccountId($accountId)
{
$sql = 'SELECT ds.* FROM document_signatures ds
LEFT JOIN client_beneficiaries cb ON (ds.source_id = cb.id)
WHERE cb.account_id = :account_id AND ds.active = :is_active AND ds.type = :type_change_beneficiary';
$rsm = new ResultSetMappingBuilder($this->_em);
$rsm->addEntityResult('WealthbotSignatureBundle:DocumentSignature', 'ds');
$rsm->addFieldResult('ds', 'id', 'id');
$rsm->addFieldResult('ds', 'source_id', 'source_id');
$rsm->addFieldResult('ds', 'document_id', 'document_id');
$rsm->addFieldResult('ds', 'docusign_envelope_id', 'docusign_envelope_id');
$rsm->addFieldResult('ds', 'type', 'type');
$rsm->addFieldResult('ds', 'status', 'status');
$rsm->addFieldResult('ds', 'active', 'active');
$rsm->addFieldResult('ds', 'created', 'created');
$rsm->addFieldResult('ds', 'updated', 'updated');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameters(array('type_change_beneficiary' => DocumentSignature::TYPE_CHANGE_BENEFICIARY, 'account_id' => $accountId, 'is_active' => 1));
return $query->getResult();
}
/**
* Корзина пользователя
*
* @param int $id
*
* @return array
*/
public function findByProductsUsersBasket($id)
{
$rsm = new ResultSetMappingBuilder($this->getEntityManager());
$rsm->addEntityResult("ShopOrderBundle:OrderItem", "oi");
$rsm->addFieldResult("oi", "id", "id");
$rsm->addFieldResult("oi", "number", "number");
$rsm->addJoinedEntityResult("ShopOrderBundle:Order", "o", "oi", "order");
$rsm->addJoinedEntityResult("ShopProductBundle:Product", "p", "oi", "product");
$rsm->addFieldResult("p", "product", "id");
$rsm->addFieldResult("p", "price", "price");
$rsm->addJoinedEntityResult("ShopProductBundle:ProductImage", "pi", "p", "image");
$rsm->addFieldResult("pi", "image_id", "id");
$rsm->addFieldResult("pi", "path", "path");
$query = $this->getEntityManager()->createNativeQuery('
SELECT
DISTINCT ON (oi.id) oi.id,
oi.product_id,
oi.number,
p.id as product,
p.price,
pi.id as image_id,
pi.path
FROM order_item oi
LEFT JOIN "order" o ON oi.order_id = o.id
LEFT JOIN product p ON oi.product_id = p.id
LEFT JOIN product_image pi ON p.id = pi.product_id
WHERE
o.users_id = ?
AND o.is_create_order = \'f\'
', $rsm)->setParameter(1, $id);
try {
return $query->getArrayResult();
} catch (\Doctrine\ORM\NoResultException $e) {
return null;
}
}
/**
* return All Product Shop
*
* @param string $name
* @param int $count
*
* @return array
*/
public function findByProductShop($name, $count)
{
$rsm = new ResultSetMappingBuilder($this->getEntityManager());
$rsm->addEntityResult("ShopProductBundle:Product", "p");
$rsm->addFieldResult("p", "id", "id");
$rsm->addFieldResult("p", "price", "price");
$rsm->addJoinedEntityResult("UserUserBundle:Users", "u", "p", "likeProduct");
$rsm->addFieldResult("u", "likes", "id");
$rsm->addJoinedEntityResult("ShopProductBundle:ProductImage", "pi", "p", "image");
$rsm->addFieldResult("pi", "image_id", "id");
$rsm->addFieldResult("pi", "path", "path");
$query = $this->getEntityManager()->createNativeQuery('
SELECT DISTINCT ON (p.id) p.id, p.price, count(u.id) as likes, pi.id as image_id, pi.path
FROM product p
LEFT JOIN product_image pi ON pi.product_id = p.id
LEFT JOIN product_like pl ON pl.product_id = p.id
LEFT JOIN users u ON pl.users_id = u.id
LEFT JOIN shops s ON s.id = p.shops_id
WHERE s.unique_name = ?
GROUP BY p.id, p.price, pi.id, pi.path
LIMIT 16 OFFSET ?
', $rsm)->setParameters([1 => $name, 2 => $count]);
try {
return $query->getArrayResult();
} catch (\Doctrine\ORM\NoResultException $e) {
return null;
}
}
public function findLatestValuesForClientPortfolio($clientPortfolioId)
{
$sql = "SELECT cav.* FROM client_account_values cav\n LEFT JOIN client_portfolio cp ON cav.client_portfolio_id = cp.id\n WHERE cav.id = (SELECT id FROM client_account_values cav1\n WHERE cav1.system_client_account_id = cav.system_client_account_id\n ORDER BY cav1.date DESC\n LIMIT 1)\n AND cav.client_portfolio_id = :clientPortfolioId";
$rsm = new ResultSetMappingBuilder($this->_em);
$rsm->addEntityResult('WealthbotClientBundle:ClientAccountValue', 'cav');
$rsm->addFieldResult('cav', 'id', 'id');
$rsm->addFieldResult('cav', 'client_portfolio_id', 'client_portfolio_id');
$rsm->addFieldResult('cav', 'system_client_account_id', 'system_client_account_id');
$rsm->addFieldResult('cav', 'source', 'source');
$rsm->addFieldResult('cav', 'total_value', 'total_value');
$rsm->addFieldResult('cav', 'total_in_securities', 'total_in_securities');
$rsm->addFieldResult('cav', 'total_cash_in_account', 'total_cash_in_account');
$rsm->addFieldResult('cav', 'total_cash_in_money_market', 'total_cash_in_money_market');
$rsm->addFieldResult('cav', 'date', 'date');
$rsm->addFieldResult('cav', 'sas_cash', 'sas_cash');
$rsm->addFieldResult('cav', 'cash_buffer', 'cash_buffer');
$rsm->addFieldResult('cav', 'billing_cash', 'billing_cash');
$rsm->addFieldResult('cav', 'required_cash', 'required_cash');
$rsm->addFieldResult('cav', 'model_deviation', 'model_deviation');
$rsm->addFieldResult('cav', 'investable_cash', 'investable_cash');
$query = $this->_em->createNativeQuery($sql, $rsm);
$query->setParameter('clientPortfolioId', $clientPortfolioId);
return $query->getResult();
}
