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;
}
public function initializeAccounts($accounts, Corporation $corp)
{
foreach ($accounts as $a) {
if (intval($a->accountKey) <= 1006) {
$exists = $this->doctrine->getRepository('AppBundle:Account')->findOneBy(['corporation' => $corp, 'division' => $a->accountKey]);
if ($exists instanceof Account) {
$exists->setName($a->description);
} else {
$account = new Account();
$account->setDivision($a->accountKey)->setName($a->description);
$corp->addAccount($account);
}
}
}
}
/**
* Remove corporationFund
*
* @param \AppBundle\Entity\Corporation $corporationFund
*/
public function removeCorporationFund(\AppBundle\Entity\Corporation $corporationFund)
{
$this->corporationFunds->removeElement($corporationFund);
}
