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(); }