Symfony Profiler

<?php
namespace ApplicationBundle\Controller;
use ApplicationBundle\Constants\GeneralConstant;
use ApplicationBundle\Constants\HumanResourceConstant;
use ApplicationBundle\Entity\DashboardWidget;
use ApplicationBundle\Entity\Employee;
use ApplicationBundle\Entity\EmployeeDetails;
use ApplicationBundle\Entity\SysUser;
use ApplicationBundle\Interfaces\SessionCheckInterface;
use ApplicationBundle\Modules\Accounts\Accounts;
use ApplicationBundle\Modules\Authentication\Constants\UserConstants; use ApplicationBundle\Modules\Api\Constants\ApiConstants;
use ApplicationBundle\Modules\HumanResource\HumanResource;
use ApplicationBundle\Modules\Inventory\Inventory;
use ApplicationBundle\Modules\Purchase\Supplier;
use ApplicationBundle\Modules\Sales\Client;
use ApplicationBundle\Modules\System\Support\PeriodFilterCore;
use ApplicationBundle\Modules\System\MiscActions;
use ApplicationBundle\Modules\System\System;
use ApplicationBundle\Modules\User\Company;
use ApplicationBundle\Modules\User\Position;
use ApplicationBundle\Modules\User\Users;
use CompanyGroupBundle\Entity\CompanyGroup;
use CompanyGroupBundle\Entity\EntityApplicantApplicationList;
use CompanyGroupBundle\Entity\EntityApplicantDetails;
use CompanyGroupBundle\Entity\EntityTicket;
use Symfony\Component\BrowserKit\Client as BrowserClientKit;
use Symfony\Component\Debug\Exception\FlattenException;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\HttpKernel\Log\DebugLoggerInterface;
use Symfony\Component\Routing\Generator\UrlGenerator;
use Symfony\Component\Routing\Generator\UrlGeneratorInterface;
class DashboardController extends GenericController implements SessionCheckInterface
{
private function getPublicDocumentEntityManager($appId)
{
$emGoc = $this->getDoctrine()->getManager('company_group');
$emGoc->getConnection()->connect();
$goc = $emGoc
->getRepository("CompanyGroupBundle\\Entity\\CompanyGroup")
->findOneBy(
array(
'appId' => $appId
)
);
if (!$goc) {
return array(null, null);
}
$connector = $this->container->get('application_connector');
$connector->resetConnection(
'default',
$goc->getDbName(),
$goc->getDbUser(),
$goc->getDbPass(),
$goc->getDbHost(),
$reset = true
);
return array($this->getDoctrine()->getManager(), $goc);
}
private function buildAllocationAwareTransactionSourceSql(array $filters = [])
{
$conn = $this->getDoctrine()->getManager()->getConnection();
$conditions = [];
if (!empty($filters['document_type'])) {
$conditions[] = 't.document_type = ' . (int) $filters['document_type'];
}
if (!empty($filters['company_id'])) {
$conditions[] = 't.company_id = ' . (int) $filters['company_id'];
}
if (!empty($filters['start_date'])) {
$conditions[] = "t.ledger_hit_date >= " . $conn->quote(date('Y-m-d', strtotime((string) $filters['start_date'])) . ' 00:00:00');
}
if (!empty($filters['end_date'])) {
$conditions[] = "t.ledger_hit_date <= " . $conn->quote(date('Y-m-d', strtotime((string) $filters['end_date'])) . ' 23:59:59.999');
}
if (!empty($filters['client_head_ids'])) {
$conditions[] = 'td.accounts_head_id IN (' . implode(',', array_map('intval', (array) $filters['client_head_ids'])) . ')';
}
if (isset($filters['approved'])) {
$conditions[] = 't.approved = ' . (int) $filters['approved'];
} else {
$conditions[] = 't.approved = 1';
}
if (isset($filters['ledger_hit'])) {
$conditions[] = 't.ledger_hit = ' . (int) $filters['ledger_hit'];
} else {
$conditions[] = 't.ledger_hit = 1';
}
$whereSql = empty($conditions) ? '1=1' : implode(' AND ', $conditions);
return "(
SELECT td.transaction_details_id AS transaction_details_id,
td.transaction_id,
td.accounts_head_id,
td.position,
td.amount AS allocation_amount,
td.ledger_hit_date,
t.document_hash,
t.company_id,
t.document_type,
1 AS has_allocation
FROM acc_transaction_details td
JOIN acc_transactions t ON t.transaction_id = td.transaction_id
JOIN transaction_detail_allocations tda ON tda.transaction_detail_id = td.transaction_details_id
WHERE " . $whereSql . "
UNION ALL
SELECT td.transaction_details_id AS transaction_details_id,
td.transaction_id,
td.accounts_head_id,
td.position,
td.amount AS allocation_amount,
td.ledger_hit_date,
t.document_hash,
t.company_id,
t.document_type,
0 AS has_allocation
FROM acc_transaction_details td
JOIN acc_transactions t ON t.transaction_id = td.transaction_id
WHERE " . $whereSql . "
AND NOT EXISTS (
SELECT 1
FROM transaction_detail_allocations tda
WHERE tda.transaction_detail_id = td.transaction_details_id
)
)";
}
public function doLoginAsAction(Request $request)
{
$session = $request->getSession();
if ($request->isMethod('POST')) {
$session->set(UserConstants::USER_CURRENT_POSITION, $request->request->get('position'));
$curr_position_id = $request->request->get('position');
if ($session->get(UserConstants::USER_LOGIN_ID) != 0) {
$loginID = $this->get('user_module')->addUserLoginLog(
$session->get(UserConstants::USER_ID),
$request->server->get("REMOTE_ADDR"),
$request->request->get('position'),
'',
'',
'',
$session->get(UserConstants::USER_LOGIN_ID)
);
} else $loginID = $this->get('user_module')->addUserLoginLog(
$session->get(UserConstants::USER_ID),
$request->server->get("REMOTE_ADDR"),
$request->request->get('position'),
'',
'',
'',
0
);
if ($session->get(UserConstants::ALL_MODULE_ACCESS_FLAG) == 1)
$prohibit_list_array = [];
else if ($curr_position_id != 0)
$prohibit_list_array = Position::getUserProhibitRouteArray($this->getDoctrine()->getManager(), $curr_position_id, $session->get(UserConstants::USER_ID));
$session->set(UserConstants::USER_LOGIN_ID, $loginID);
$session->set(UserConstants::USER_PROHIBIT_LIST, json_encode($prohibit_list_array));
$session->set(UserConstants::USER_ROUTE_LIST, json_encode(Position::getUserRouteArray($this->getDoctrine()->getManager(), $request->request->get('position'), $session->get(UserConstants::USER_ID))));
return $this->redirectToRoute("dashboard");
}
$message = "";
$PositionList = array();
$PL = json_decode($session->get(UserConstants::USER_POSITION_LIST), true);
foreach ($PL as &$positionID) {
$PositionList[$positionID] = Position::getPositionName($this->getDoctrine()->getManager(), $positionID);
}
return $this->render(
'@Authentication/pages/views/login_position.html.twig',
array(
"message" => $message,
'page_title' => 'Users',
'position_list' => $PositionList
)
);
}
// protected $twig;
// protected $debug;
//
// /**
// * @param bool $debug Show error (false) or exception (true) pages by default
// */
// public function __construct(Environment $twig, $debug)
// {
// $this->twig = $twig;
// $this->debug = $debug;
// }
public function TestSmsAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$session = $request->getSession();
$em = $this->getDoctrine()->getManager();
$userAppId = $session->get('userAppId');
$companyId = $session->get('userCompanyId');
if (GeneralConstant::SMS_ENABLED == 1) {
$company = $em->getRepository('ApplicationBundle\\Entity\\Company')->findOneBy(
array(
'id' => $companyId, ///material
)
);
if ($company->getSmsNotificationEnabled() == 1) {
$smsData = [];
$smsSettings = json_decode($company->getSmsSettings(), true);
if ($smsSettings != null && !empty($smsSettings)) {
$method = isset($smsSettings['method']) ? $smsSettings['method'] : 'GET';
$destination_url = isset($smsSettings['destination_url']) ? $smsSettings['destination_url'] : 'http://172.105.70.96:1234';
$user_name = isset($smsSettings['user_name']) ? $smsSettings['user_name'] : 'test';
$user_name_var = isset($smsSettings['user_name_var']) ? $smsSettings['user_name_var'] : 'Username';
$password_var = isset($smsSettings['password_var']) ? $smsSettings['password_var'] : 'Password';
$password = isset($smsSettings['password']) ? $smsSettings['password'] : '';
$from_number_var = isset($smsSettings['from_number_var']) ? $smsSettings['from_number_var'] : 'From';
$from_number = isset($smsSettings['from_number']) ? $smsSettings['from_number'] : '';
$to_number_var = isset($smsSettings['to_number_var']) ? $smsSettings['to_number_var'] : 'To';
$to_number = $request->query->has('To') ? $request->query->get('To') : '01911706483';
$text_var = isset($smsSettings['text_var']) ? $smsSettings['text_var'] : 'Message';
$text = $request->query->has('Message') ? $request->query->get('Message') : 'Test message';
return new JsonResponse(System::sendSms(
1,
$method,
$destination_url,
$user_name_var,
$user_name,
$password_var,
$password,
$from_number_var,
$from_number,
$to_number_var,
$to_number,
$text_var,
$text,
$viewlink = ""
));
}
}
return new JsonResponse(
[
'companyId' => $companyId,
'companyName' => $company->getName(),
'enabled' => $company->getSmsNotificationEnabled(),
// 'smsSettings'=>$smsSettings
]
);
}
return new JsonResponse(
[
'success' => false
]
);
}
public function SyncCompanyProductToEntityProductAction(Request $request)
{
$gocId = $request->query->has('gocId') ? $request->query->get('gocId') : 0;
$companyId = $request->query->has('companyId') ? $request->query->get('companyId') : 1;
$genQueryArray = [];
$em_goc = $this->getDoctrine()->getManager('company_group');
$em_goc->getConnection()->connect();
$em = $this->getDoctrine()->getManager();
if ($request->query->has('forceRefresh')) {
$genQueryArray = [];
$query = "
TRUNCATE `entity_brand_company`;
TRUNCATE `entity_item_group`;
TRUNCATE `entity_products`;
TRUNCATE `entity_product_categories`;
TRUNCATE `entity_product_specifications`;
TRUNCATE `entity_product_sub_categories`;
TRUNCATE `entity_specification_types`;
TRUNCATE `entity_unit_type`;
SELECT * from `entity_unit_type`;";
$stmt = $em_goc->getConnection()->executeStatement($query);
// $Transactions = $stmt;
$get_kids_sql = 'select * from acc_accounts_head where accounts_head_id not in (select distinct parent_id from acc_accounts_head) ;';
//UPDATE company SET sales=0, expense=0, payable=0 ,net_worth=0, monthly_growth=0 WHERE 1;';
$stmt = $em->getConnection()->executeStatement($get_kids_sql);
$query = $stmt;
} else
$genQueryArray = ['globalId' => [0, null]];
//1st step get all company item ids and then check for global id null if its null then the
// item group is not present in entity so add it
$company_wise_entities = [
'Currencies',
'UnitType',
'SpecType',
'BrandCompany',
'InvItemGroup',
'InvProductCategories',
'InvProductSubCategories',
'InvProductSpecifications',
'InvSpecificationTypes',
'InvProducts'
];
$entity_wise_entities = [
'EntityCurrencies',
'EntityUnitType',
'EntitySpecType',
'EntityBrandCompany',
'EntityItemGroup',
'EntityProductCategories',
'EntityProductSubCategories',
'EntityProductSpecifications',
'EntitySpecificationTypes',
'EntityProducts'
];
////ITEMGROUPS
foreach ($company_wise_entities as $k => $cwa) {
$dataOnCompany = $em
->getRepository("ApplicationBundle\\Entity\\" . $cwa)
->findBy(
$genQueryArray
);
$match_cid_gid_itemgroup = []; //[cid=>gid]
$match_cid_gid_itemgroup = []; //[cid=>gid]
foreach ($dataOnCompany as $data) {
$newClass = "\\CompanyGroupBundle\\Entity\\" . $entity_wise_entities[$k];
$new = new $newClass();
// $new = new \CompanyGroupBundle\Entity\EntityItemGroup();
$getters = array_filter(get_class_methods($data), function ($method) {
return 'get' === substr($method, 0, 3);
});
foreach ($getters as $getter) {
if ($getter == 'getGlobalId')
continue;
// if ($getter == 'getId')
// continue;
$setMethod = str_replace('get', 'set', $getter);
if (method_exists($new, $setMethod))
$new->{$setMethod}($data->{$getter}()); // `foo!`
}
$em_goc->persist($new);
$em_goc->flush();
if ($cwa == 'Currencies') {
$data->setGlobalId($new->getCurrencyId());
$new->setGlobalId($new->getCurrencyId());
} else {
$data->setGlobalId($new->getId());
$new->setGlobalId($new->getId());
}
$em->flush();
$em_goc->flush();
// $match_cid_gid_itemgroup[$data->getId()] = $new->getId();
}
}
return new JsonResponse(
array(
'data' => $entity_wise_entities
)
);
}
public function SyncEntityProductToCompanyProductAction(Request $request)
{
$gocId = $request->query->has('gocId') ? $request->query->get('gocId') : 0;
$companyId = $request->query->has('companyId') ? $request->query->get('companyId') : 1;
$genQueryArray = [];
$em_goc = $this->getDoctrine()->getManager('company_group');
$em_goc->getConnection()->connect();
$em = $this->getDoctrine()->getManager();
if ($request->query->has('forceRefresh')) {
$genQueryArray = [];
$query = "
TRUNCATE `brand_company`;
TRUNCATE `inv_item_group`;
TRUNCATE `inv_products`;
TRUNCATE `inv_product_categories`;
TRUNCATE `inv_product_specifications`;
TRUNCATE `inv_product_sub_categories`;
TRUNCATE `inv_specification_types`;
TRUNCATE `unit_type`;
SELECT * from `unit_type`;";
$stmt = $em->getConnection()->executeStatement($query);
// $Transactions = $stmt;
$get_kids_sql = 'select * from acc_accounts_head where accounts_head_id not in (select distinct parent_id from acc_accounts_head) ;';
//UPDATE company SET sales=0, expense=0, payable=0 ,net_worth=0, monthly_growth=0 WHERE 1;';
$stmt = $em->getConnection()->fetchAllAssociative($get_kids_sql);
$query = $stmt;
} else {
// $genQueryArray = ['globalId' => [0, null]];
}
//1st step get all company item ids and then check for global id null if its null then the
// item group is not present in entity so add it
$company_wise_entities = [
'Currencies',
'UnitType',
'SpecType',
'BrandCompany',
'InvItemGroup',
'InvProductCategories',
'InvProductSubCategories',
'InvProductSpecifications',
'InvSpecificationTypes',
'InvProducts'
];
$entity_wise_entities = [
'EntityCurrencies',
'EntityUnitType',
'EntitySpecType',
'EntityBrandCompany',
'EntityItemGroup',
'EntityProductCategories',
'EntityProductSubCategories',
'EntityProductSpecifications',
'EntitySpecificationTypes',
'EntityProducts'
];
foreach ($entity_wise_entities as $k => $ewa) {
$dataOnEntity = $em_goc
->getRepository("CompanyGroupBundle\\Entity\\" . $ewa)
->findBy(
$genQueryArray
);
$match_cid_gid_itemgroup = []; //[cid=>gid]
$match_cid_gid_itemgroup = []; //[cid=>gid]
foreach ($dataOnEntity as $data) {
$newClass = "\\ApplicationBundle\\Entity\\" . $company_wise_entities[$k];
$rec_exists = 0;
$new = $em
->getRepository("ApplicationBundle\\Entity\\" . $company_wise_entities[$k])
->findOneBy(
array(
'globalId' => $data->getGlobalId()
)
);
if ($new) {
$rec_exists = 1;
} else
$new = new $newClass();
// $new = new \CompanyGroupBundle\Entity\EntityItemGroup();
$getters = array_filter(get_class_methods($data), function ($method) {
return 'get' === substr($method, 0, 3);
});
foreach ($getters as $getter) {
if ($getter == 'getId')
continue;
$setMethod = str_replace('get', 'set', $getter);
if (method_exists($new, $setMethod)) {
if ($ewa == 'EntityProducts') {
if ($rec_exists == 1) {
if (in_array($setMethod, [
'setPurchasePrice',
'setPurchasePriceWoExpense',
'setQty',
'setReorderLevel',
'setBookingQty',
'setNonSalesInvoicedQty',
'setNonSalesReturnReceivedQty',
'setBookingQty',
'setPhysicalQty',
])) {
continue;
}
}
}
$new->{$setMethod}($data->{$getter}()); // `foo!`
}
}
if ($rec_exists == 0)
$em->persist($new);
$em->flush();
if ($ewa == 'EntityCurrencies') {
$new->setGlobalId($data->getCurrencyId());
} else {
$new->setGlobalId($data->getId());
}
$em->flush();
// $match_cid_gid_itemgroup[$data->getId()] = $new->getId();
}
}
return new JsonResponse(
array(
'data' => $company_wise_entities
)
);
}
public function getAppGeneralDashboardDataAction(Request $request)
{
$dataFor = 'client';
$personId = 0;
if ($request->query->has('dataFor'))
$dataFor = $request->query->get('dataFor');
if ($request->request->has('dataFor'))
$dataFor = $request->request->get('dataFor');
if ($request->query->has('personId'))
$personId = $request->query->get('personId');
if ($request->request->has('personId'))
$personId = $request->request->get('personId');
if ($personId == 0)
return new JsonResponse(
array(
'success' => false,
'data' => [],
)
);
$session = $request->getSession();
$em = $this->getDoctrine()->getManager();
$userAppId = $session->get('userAppId');
$companyId = $session->get('userCompanyId');
$dataToConnectList = [];
$appIdList = $session->get('appIdList');
$companyIdListByAppId = $session->get('companyIdListByAppId');
$companyNameListByAppId = $session->get('companyNameListByAppId');
$companyImageListByAppId = $session->get('companyImageListByAppId');
$gocEnabled = 0;
if ($this->container->hasParameter('entity_group_enabled'))
$gocEnabled = $this->container->getParameter('entity_group_enabled');
$connector = $this->container->get('application_connector');
$connector->resetConnection(
'default',
$session->get('gocDbName'),
$session->get('gocDbUser'),
$session->get('gocDbPass'),
$session->get('gocDbHost'),
$reset = true
);
$em = $this->getDoctrine()->getManager();
$data = [];
if ($dataFor == 'client') {
//get order_count, order_amount,bill_count, bill_amount, collection_amount, due_amount
$data = [
'order_count' => 0,
'order_amount' => 0,
'invoice_count' => 0,
'invoice_amount' => 0,
'collection_amount' => 0,
'due_amount' => 0,
'client_count' => 1,
];
$clientIds = [$personId];
//order count and amount
$get_sql = "SELECT count(sales_order_id) order_count, sum(so_amount) order_amount
FROM sales_order WHERE approved=1 and sales_order.company_id=" . $companyId . " and sales_order.client_id in (" . implode(',', $clientIds) . ");";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
if (!empty($entries)) {
$data['order_count'] = 1 * $entries[0]['order_count'];
$data['order_amount'] = 1 * $entries[0]['order_amount'];
}
//invoice
$get_sql = "SELECT count(sales_invoice_id) invoice_count, sum(invoice_amount) invoice_amount
FROM sales_invoice WHERE approved=1 and sales_invoice.company_id=" . $companyId . " and sales_invoice.client_id in (" . implode(',', $clientIds) . ");";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
if (!empty($entries)) {
$data['invoice_count'] = 1 * $entries[0]['invoice_count'];
$data['invoice_amount'] = 1 * $entries[0]['invoice_amount'];
}
//now collection
$allocationSourceSql = $this->buildAllocationAwareTransactionSourceSql(array(
'document_type' => 6,
'company_id' => $companyId,
'start_date' => null,
'end_date' => null,
'approved' => 1,
'ledger_hit' => 1,
));
$get_sql = "SELECT SUM(CASE WHEN alloc_rows.position = 'cr' THEN alloc_rows.allocation_amount ELSE 0 END) positive_amount,
SUM(CASE WHEN alloc_rows.position = 'dr' THEN alloc_rows.allocation_amount ELSE 0 END) negative_amount,client_type.client_type_id, client_type.name, acc_clients.client_id, acc_clients.type, acc_clients.client_due, acc_clients.client_id
FROM " . $allocationSourceSql . " alloc_rows
join acc_clients on acc_clients.accounts_head_id=alloc_rows.accounts_head_id or acc_clients.advance_head_id=alloc_rows.accounts_head_id
join client_type on acc_clients.type=client_type.client_type_id
WHERE acc_clients.company_id=" . $companyId . " and acc_clients.client_id in (" . implode(',', $clientIds) . ") ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
$clientTypes = [];
$clientTypeNames = [];
$alreadyCids = [];
foreach ($entries as $entry) {
$data['collection_amount'] += (1 * ($entry['positive_amount'] - $entry['negative_amount']));
if (!in_array($entry['client_id'], $alreadyCids)) {
$data['due_amount'] += ($entry['client_due']);
$alreadyCids[] = $entry['client_id'];
}
}
}
if ($dataFor == 'sales_user') {
//get order_count, order_amount,bill_count, bill_amount, collection_amount, due_amount
$data = [
'order_count' => 0,
'order_amount' => 0,
'invoice_count' => 0,
'invoice_amount' => 0,
'collection_amount' => 0,
'due_amount' => 0,
'client_count' => 0,
];
$clientIds = [];
$salesPersonIds = [];
$allSalespersonFlag = 1; //temp
if ($allSalespersonFlag != 1) {
$query = $em->getRepository('ApplicationBundle\\Entity\\Employee')->findOneBy(
array(
'userId' => $personId
)
);
if ($query)
$salesPersonIds[] = $query->getEmployeeId();
}
//Client count
$get_sql = "SELECT client_id
FROM acc_clients WHERE acc_clients.company_id=" . $companyId . " ";
if ($allSalespersonFlag != 1)
$get_sql .= " and acc_clients.sales_person_id in (" . implode(',', $salesPersonIds) . ");";
else
$get_sql .= " ;";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
if (!empty($entries)) {
foreach ($entries as $entry) {
$data['client_count'] += 1;
$clientIds[] = $entry['client_id'];
}
}
//order count and amount
if (!empty($clientIds)) {
$get_sql = "SELECT count(sales_order_id) order_count, sum(so_amount) order_amount
FROM sales_order WHERE approved=1 and sales_order.company_id=" . $companyId . " and sales_order.client_id in (" . implode(',', $clientIds) . ");";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
if (!empty($entries)) {
$data['order_count'] = 1 * $entries[0]['order_count'];
$data['order_amount'] = 1 * $entries[0]['order_amount'];
}
//invoice
$get_sql = "SELECT count(sales_invoice_id) invoice_count, sum(invoice_amount) invoice_amount
FROM sales_invoice WHERE approved=1 and sales_invoice.company_id=" . $companyId . " and sales_invoice.client_id in (" . implode(',', $clientIds) . ");";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
if (!empty($entries)) {
$data['invoice_count'] = 1 * $entries[0]['invoice_count'];
$data['invoice_amount'] = 1 * $entries[0]['invoice_amount'];
}
//now collection
$allocationSourceSql = $this->buildAllocationAwareTransactionSourceSql(array(
'document_type' => 6,
'approved' => 1,
'ledger_hit' => 1,
));
$get_sql = "SELECT SUM(CASE WHEN alloc_rows.position = 'cr' THEN alloc_rows.allocation_amount ELSE 0 END) positive_amount,
SUM(CASE WHEN alloc_rows.position = 'dr' THEN alloc_rows.allocation_amount ELSE 0 END) negative_amount,client_type.client_type_id, client_type.name, acc_clients.client_id, acc_clients.type, acc_clients.client_due, acc_clients.client_id
FROM " . $allocationSourceSql . " alloc_rows
join acc_clients on acc_clients.accounts_head_id=alloc_rows.accounts_head_id or acc_clients.advance_head_id=alloc_rows.accounts_head_id
join client_type on acc_clients.type=client_type.client_type_id
WHERE acc_clients.company_id=" . $companyId . " and acc_clients.client_id in (" . implode(',', $clientIds) . ") ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
$clientTypes = [];
$clientTypeNames = [];
$alreadyCids = [];
foreach ($entries as $entry) {
$data['collection_amount'] += (1 * ($entry['positive_amount'] - $entry['negative_amount']));
if (!in_array($entry['client_id'], $alreadyCids)) {
$data['due_amount'] += ($entry['client_due']);
$alreadyCids[] = $entry['client_id'];
}
}
}
}
if ($dataFor == 'user') {
//get order_count, payment_amount,
}
return new JsonResponse(
array(
'success' => true,
'data' => $data,
'dataFor' => $dataFor,
'personId' => $personId,
)
);
}
public function getManagementDashboardDataAction(Request $request)
{
$session = $request->getSession();
$data = array(
'cash_and_bank_position' => [],
'order_and_client_count' => [],
'expense_vs_revenue' => [],
'receivable_position' => [],
'payable_position' => [],
'expense_position' => [],
'inventory_position' => [],
'production_position' => [],
'sales_position' => [],
'collection_position' => [],
'market_return_position' => [],
'top_selling_item_position' => [],
'top_selling_client_position' => [],
);
$em = $this->getDoctrine()->getManager();
$reportsToGet = [
'cash_and_bank_position',
'order_and_client_count',
'expense_vs_revenue',
'receivable_position',
'payable_position',
'expense_position',
'inventory_position',
'production_position',
'sales_position',
'collection_position',
'market_return_position',
'top_selling_item_position',
'top_selling_client_position',
];
// $periodType='day'; //day,month,week,year,quarter etc
$periodType = 'month'; //day,month,week,year,quarter etc
if ($request->request->has('reportsToGet')) {
if ($request->request->get('reportsToGet') != 'all' && !in_array('all', $request->request->get('reportsToGet'))) {
$reportsToGet = $request->request->get('reportsToGet');
}
}
if ($request->request->has('periodType')) {
$periodType = $request->request->get('periodType');
}
$userAppId = $session->get('userAppId');
$dataToConnectList = [];
$appIdList = $session->get('appIdList');
$companyIdListByAppId = $session->get('companyIdListByAppId');
$companyNameListByAppId = $session->get('companyNameListByAppId');
$companyImageListByAppId = $session->get('companyImageListByAppId');
$gocEnabled = 0;
if ($this->container->hasParameter('entity_group_enabled'))
$gocEnabled = $this->container->getParameter('entity_group_enabled');
$thisPeriodStartDate = '';
$lastPeriodStartDate = '';
$thisPeriodEndDate = '';
$lastPeriodEndDate = '';
$today = new \DateTime();
if ($periodType == 'month') {
$thisPeriodEndDate = $today->format('Y-m-d');
$thisPeriodStartDate = $today->format('Y-m-') . '01';
$currDate = new \DateTime($thisPeriodStartDate);
$currDate->modify('-1 day');
$lastPeriodEndDate = $currDate->format('Y-m-d');
$lastPeriodStartDate = $currDate->format('Y-m-') . '01';
} else if ($periodType == 'week') {
$thisPeriodEndDate = $today->format('Y-m-d');
$thisWeekStartDate = date("Y-m-d", strtotime("previous saturday"));
$thisPeriodStartDate = date("Y-m-d", strtotime("previous saturday"));
$currDate = new \DateTime($thisPeriodStartDate);
$currDate->modify('-1 day');
$lastPeriodEndDate = $currDate->format('Y-m-d');
$currDate->modify('-6 day');
$lastPeriodStartDate = $currDate->format('Y-m-d');
} else if ($periodType == 'quarter') {
$thisPeriodEndDate = $today->format('Y-m-d');
$thisWeekStartDate = date("Y-m-d", strtotime("previous saturday"));
$thisPeriodStartDate = date("Y-m-d", strtotime("previous saturday"));
$currDate = new \DateTime($thisPeriodStartDate);
$currDate->modify('-1 day');
$lastPeriodEndDate = $currDate->format('Y-m-d');
$currDate->modify('-6 day');
$lastPeriodStartDate = $currDate->format('Y-m-d');
} else if ($periodType == 'year') {
$thisPeriodEndDate = $today->format('Y-m-d');
// $thisWeekStartDate = date("Y-m-d", strtotime("previous saturday"));
$thisPeriodStartDate = ($today->format('Y')) . '-01-01';
$currDate = new \DateTime($thisPeriodStartDate);
$currDate->modify('-1 day');
$lastPeriodEndDate = $currDate->format('Y-m-d');
$lastPeriodStartDate = ($currDate->format('Y')) . '-01-01';;
}
$companyListData = [];
foreach ($appIdList as $appId) {
//queryonly if mor ethan one app id
$skip = 0;
if ($appId == $userAppId) {
$connector = $this->container->get('application_connector');
$connector->resetConnection(
'default',
$session->get('gocDbName'),
$session->get('gocDbUser'),
$session->get('gocDbPass'),
$session->get('gocDbHost'),
$reset = true
);
$em = $this->getDoctrine()->getManager();
} else {
$dataToConnect = System::changeDoctrineManagerByAppId($this->getDoctrine()->getManager('company_group'), $gocEnabled, $appId);
$dataToConnectList[] = $dataToConnect;
if (!empty($dataToConnect)) {
$connector = $this->container->get('application_connector');
$connector->resetConnection(
'default',
$dataToConnect['dbName'],
$dataToConnect['dbUser'],
$dataToConnect['dbPass'],
$dataToConnect['dbHost'],
$reset = true
);
$em = $this->getDoctrine()->getManager();
} else {
$skip = 1;
}
}
if ($skip == 1)
continue;
// $companyListData[] = Company::getCompanyListWithImage($em);
// $get_sql = "SELECT * FROM company ";
// $stmt = $em->getConnection()->executeStatement($get_sql);
//
// $entries = $stmt;
// $companyListData[] = $entries;
// foreach ($companyIdListByAppId as $app_company_index)
if (isset($companyIdListByAppId[$appId])) {
foreach ($companyIdListByAppId[$appId] as $app_company_index) {
//exp vs rev
if (in_array('expense_vs_revenue', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByAcHead = [];
$lastBalanceDataByAcHead = [];
$mon_names = ["", "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"];
$limit = 12;
$get_kids_sql = "SELECT expense, revenue, `date` summ_date
FROM monthly_summary
where monthly_summary.company_id=$companyId
AND ( monthly_summary.branch_id=0 or monthly_summary.branch_id is null) ";
// GROUP BY monthly_summary.`date`
$get_kids_sql .= " ORDER BY monthly_summary.`date` DESC limit " . $limit;
$stmt = $em->getConnection()->fetchAllAssociative($get_kids_sql);
$pl = $stmt;
$curr_date = new \DateTime();
$curr_mon = (1 * $curr_date->format('m'));
$curr_year = (1 * $curr_date->format('Y'));
$object_list = [];
$debug_list = [];
$list = [];
$timeStampList = [];
// $first
foreach ($pl as $key => $entry) {
$qryDate = new \DateTime($entry['summ_date']);
$qrMon = 1 * $qryDate->format('m');;
$qrYr = 1 * $qryDate->format('Y');;
$object_list[$qrYr . '_' . $qrMon] = $entry;
}
for ($k = 0; $k < 11; $k++) {
$debug_list[$k] = $curr_year . '_' . $curr_mon;
$currTimeStamp = strtotime($curr_year . "-" . $curr_mon . '-1');
$currTimeStampMili = $currTimeStamp * 1000;
$cashAddition = 0;
$bankAddition = 0;
if (isset($object_list[$curr_year . '_' . $curr_mon])) {
$entry = $object_list[$curr_year . '_' . $curr_mon];
$list['monthList'][] = $mon_names[$curr_mon];
$list['revenue'][] = round($entry['revenue']);
$list['tsCommaRevenue'][] = [$currTimeStampMili, round($entry['revenue'])];
$list['expense'][] = round($entry['expense']);
$list['tsCommaExpense'][] = [$currTimeStampMili, round($entry['expense'])];
} else {
$list['monthList'][] = $mon_names[($curr_mon)];
$list['revenue'][] = 0;
$list['expense'][] = 0;
$list['tsCommaRevenue'][] = [$currTimeStampMili, 0];
$list['tsCommaExpense'][] = [$currTimeStampMili, 0];
}
$curr_mon--;
if ($curr_mon <= 0) {
$curr_mon = 12;
$curr_year--;
}
//new end
}
$data['expense_vs_revenue'][$app_company_index] = $list;
}
//cash and bank position
if (in_array('cash_and_bank_position', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByAcHead = [];
$lastBalanceDataByAcHead = [];
$bank_settings = $em->getRepository('ApplicationBundle\\Entity\\AccSettings')->findOneBy(array(
'name' => 'bank_parents',
// 'CompanyId'=>$companyId
));
$bank_id_list = [];
if ($bank_settings)
$bank_id_list = json_decode($bank_settings->getData());
$bank_child_id_list = [];
$likeQuery = '';
foreach ($bank_id_list as $p) {
$likeQuery .= "OR path_tree like '%/" . $p . "%/' ";
}
$get_sql = "SELECT * FROM acc_accounts_head WHERE acc_accounts_head.company_id=" . $companyId . " AND accounts_head_id not in (select distinct parent_id from acc_accounts_head)and (1=0 " . $likeQuery . ") ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
foreach ($entries as $entry) {
$has_payments = 1;
$bank_child_id_list[] = $entry['accounts_head_id'];
$acHeadList[] = $entry['accounts_head_id'];
$acHeadDataById[$entry['accounts_head_id']]['name'] = $entry['name'];
}
$cash_settings = $em->getRepository('ApplicationBundle\\Entity\\AccSettings')->findOneBy(array(
'name' => 'cash_parents'
// 'CompanyId'=>$companyId
));
$cash_id_list = [];
if ($cash_settings)
$cash_id_list = json_decode($cash_settings->getData());
$cash_child_id_list = [];
$likeQuery = '';
foreach ($cash_id_list as $p) {
$likeQuery .= "OR path_tree like '%/" . $p . "%/' ";
}
$get_sql = "SELECT * FROM acc_accounts_head WHERE acc_accounts_head.company_id=" . $companyId . " AND accounts_head_id not in (select distinct parent_id from acc_accounts_head)and (1=0 " . $likeQuery . ") ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
foreach ($entries as $entry) {
$has_payments = 1;
$cash_child_id_list[] = $entry['accounts_head_id'];
$acHeadList[] = $entry['accounts_head_id'];
$acHeadDataById[$entry['accounts_head_id']]['name'] = $entry['name'];
}
$thisBalance_data = Accounts::GetBalanceOnDate($em, $thisPeriodEndDate, []);
$lastBalance_data = Accounts::GetBalanceOnDate($em, $lastPeriodEndDate, []);
foreach ($acHeadList as $acHead) {
$thisBalanceDataByAcHead[$acHead]['balance'] = $thisBalance_data[$acHead]['end_balance']['balance'];
$lastBalanceDataByAcHead[$acHead]['balance'] = $lastBalance_data[$acHead]['end_balance']['balance'];
}
$thisReportData = array(
'accountsHeadList' => $acHeadList,
'acHeadDataById' => $acHeadDataById,
'cash_child_id_list' => $cash_child_id_list,
'bank_child_id_list' => $bank_child_id_list,
'thisBalanceDataByAcHead' => $thisBalanceDataByAcHead,
'lastBalanceDataByAcHead' => $lastBalanceDataByAcHead,
);
$data['cash_and_bank_position'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
//Expense Position
if (in_array('expense_position', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByAcHead = [];
$lastBalanceDataByAcHead = [];
$get_sql = "SELECT * FROM acc_accounts_head WHERE acc_accounts_head.company_id=" . $companyId . "
AND acc_accounts_head.head_level= 2 ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
foreach ($entries as $entry) {
$has_payments = 1;
$cash_child_id_list[] = $entry['accounts_head_id'];
$acHeadList[] = $entry['accounts_head_id'];
$acHeadDataById[$entry['accounts_head_id']]['name'] = $entry['name'];
}
$thisBalance_data = Accounts::GetPeriodicDataByType($em, $type = 'exp', $level_to_group_by = 1, $parent_id_list = [], $expand_level = 1, $thisPeriodStartDate, $thisPeriodEndDate, '_during_', [], [], [], false);
$lastBalance_data = Accounts::GetPeriodicDataByType($em, $type = 'exp', $level_to_group_by = 1, $parent_id_list = [], $expand_level = 1, $lastPeriodStartDate, $lastPeriodEndDate, '_during_', [], [], [], false);
$skipHeadIds = [];
foreach ($thisBalance_data as $parentHead => $balanceData) {
foreach ($balanceData['data'] as $acHead => $headData) {
if (!in_array($acHead, $acHeadList))
continue;
$thisBalanceDataByAcHead[$acHead]['balance'] = $headData['head_balance'];
if (isset($lastBalance_data[$parentHead])) {
if (isset($lastBalance_data[$parentHead]['data'][$acHead]))
$lastBalanceDataByAcHead[$acHead]['balance'] = $lastBalance_data[$parentHead]['data'][$acHead]['head_balance'];
else
$lastBalanceDataByAcHead[$acHead]['balance'] = 0;
} else
$lastBalanceDataByAcHead[$acHead]['balance'] = 0;
$skipHeadIds[] = $acHead;
}
}
foreach ($lastBalance_data as $parentHead => $balanceData) {
foreach ($balanceData['data'] as $acHead => $headData) {
if (in_array($acHead, $skipHeadIds))
continue;
if (!in_array($acHead, $acHeadList))
continue;
$lastBalanceDataByAcHead[$acHead]['balance'] = $headData['head_balance'];
if (isset($thisBalance_data[$parentHead])) {
if (isset($thisBalance_data[$parentHead]['data'][$acHead]))
$thisBalanceDataByAcHead[$acHead]['balance'] = $thisBalance_data[$parentHead]['data'][$acHead]['head_balance'];
else
$thisBalanceDataByAcHead[$acHead]['balance'] = 0;
} else
$thisBalanceDataByAcHead[$acHead]['balance'] = 0;
$skipHeadIds[] = $acHead;
}
}
$thisReportData = array(
'accountsHeadList' => $acHeadList,
// 'thisBalance_data' => $thisBalance_data,
'confirmedHeadList' => $skipHeadIds,
'acHeadDataById' => $acHeadDataById,
'thisBalanceDataByAcHead' => $thisBalanceDataByAcHead,
'lastBalanceDataByAcHead' => $lastBalanceDataByAcHead,
);
$data['expense_position'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
//Payable Position
if (in_array('payable_position', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByAcHead = [];
$lastBalanceDataByAcHead = [];
$filter_settings = $em->getRepository('ApplicationBundle\\Entity\\AccSettings')->findOneBy(array(
'name' => 'payable_parents',
// 'CompanyId'=>$companyId
));
$filter_head_parents_id_list = [];
if ($filter_settings)
$filter_head_parents_id_list = json_decode($filter_settings->getData());
$likeQuery = '';
foreach ($filter_head_parents_id_list as $p) {
$likeQuery .= "OR path_tree like '%/" . $p . "%/' ";
}
$get_sql = "SELECT * FROM acc_accounts_head WHERE acc_accounts_head.company_id=" . $companyId . "
and (1=0 " . $likeQuery . ") ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
foreach ($entries as $entry) {
$has_payments = 1;
$acHeadList[] = $entry['accounts_head_id'];
$acHeadDataById[$entry['accounts_head_id']]['name'] = $entry['name'];
}
// $get_sql = "SELECT * FROM acc_accounts_head WHERE acc_accounts_head.company_id=" . $companyId . "
// AND acc_accounts_head.head_level= 2 and acc_accounts_head.type='lib'";
//
// $stmt = $em->getConnection()->executeStatement($get_sql);
//
// $entries = $stmt;
//
// foreach ($entries as $entry) {
// $has_payments = 1;
// $cash_child_id_list[] = $entry['accounts_head_id'];
// $acHeadList[] = $entry['accounts_head_id'];
// $acHeadDataById[$entry['accounts_head_id']]['name'] = $entry['name'];
// }
$thisBalance_data = Accounts::GetPeriodicDataByType($em, $type = 'lib', $level_to_group_by = 2, $parent_id_list = [], $expand_level = 1, $thisPeriodStartDate, $thisPeriodEndDate, '_during_', [], [], [], false);
$lastBalance_data = Accounts::GetPeriodicDataByType($em, $type = 'lib', $level_to_group_by = 2, $parent_id_list = [], $expand_level = 1, $lastPeriodStartDate, $lastPeriodEndDate, '_during_', [], [], [], false);
$skipHeadIds = [];
foreach ($thisBalance_data as $parentHead => $balanceData) {
foreach ($balanceData['data'] as $acHead => $headData) {
if (!(in_array($acHead, $acHeadList)))
continue;
if ($headData['head_level'] > 4)
continue;
// $thisBalanceDataByAcHead[$acHead]['balance'] = $headData['head_balance'];
$thisBalanceDataByAcHead[$acHead]['balance'] = $headData['end_balance']['balance'];
if (isset($lastBalance_data[$parentHead])) {
if (isset($lastBalance_data[$parentHead]['data'][$acHead]))
// $lastBalanceDataByAcHead[$acHead]['balance'] = $lastBalance_data[$parentHead]['data'][$acHead]['head_balance'];
$lastBalanceDataByAcHead[$acHead]['balance'] = $lastBalance_data[$parentHead]['data'][$acHead]['end_balance']['balance'];
else
$lastBalanceDataByAcHead[$acHead]['balance'] = 0;
} else
$lastBalanceDataByAcHead[$acHead]['balance'] = 0;
$skipHeadIds[] = $acHead;
}
}
foreach ($lastBalance_data as $parentHead => $balanceData) {
foreach ($balanceData['data'] as $acHead => $headData) {
if (in_array($acHead, $skipHeadIds))
continue;
if (!in_array($acHead, $acHeadList))
continue;
if ($headData['head_level'] > 4)
continue;
$lastBalanceDataByAcHead[$acHead]['balance'] = $headData['end_balance']['balance'];
if (isset($thisBalance_data[$parentHead])) {
if (isset($thisBalance_data[$parentHead]['data'][$acHead]))
$thisBalanceDataByAcHead[$acHead]['balance'] = $thisBalance_data[$parentHead]['data'][$acHead]['end_balance']['balance'];
else
$thisBalanceDataByAcHead[$acHead]['balance'] = 0;
} else
$thisBalanceDataByAcHead[$acHead]['balance'] = 0;
$skipHeadIds[] = $acHead;
}
}
$thisReportData = array(
'accountsHeadList' => $acHeadList,
// 'thisBalance_data' => $thisBalance_data,
'confirmedHeadList' => $skipHeadIds,
'acHeadDataById' => $acHeadDataById,
'thisBalanceDataByAcHead' => $thisBalanceDataByAcHead,
'lastBalanceDataByAcHead' => $lastBalanceDataByAcHead,
);
$data['payable_position'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
//Receivable Position
if (in_array('receivable_position', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByAcHead = [];
$lastBalanceDataByAcHead = [];
// $filter_settings = $em->getRepository('ApplicationBundle\\Entity\\AccSettings')->findOneBy(array(
// 'name' => 'receivable_parents',
//// 'CompanyId'=>$companyId
// ));
// $filter_head_parents_id_list = [];
// if ($filter_settings)
// $filter_head_parents_id_list = json_decode($filter_settings->getData());
// $likeQuery = '';
// foreach ($filter_head_parents_id_list as $p) {
// $likeQuery .= "OR path_tree like '%/" . $p . "%/' ";
// }
// $get_sql = "SELECT * FROM acc_accounts_head WHERE acc_accounts_head.company_id=" . $companyId . "
// and (1=0 " . $likeQuery . ") ";
$get_sql = "SELECT acc_accounts_head.* FROM acc_accounts_head
WHERE acc_accounts_head.company_id=" . $companyId . "
and ( acc_accounts_head.accounts_head_id in (select distinct accounts_head_id from acc_clients)
OR acc_accounts_head.accounts_head_id in (select distinct advance_head_id from acc_clients)
) ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
$account_head_ids_by_client_type = [];
foreach ($entries as $entry) {
$has_payments = 1;
$acHeadList[] = $entry['accounts_head_id'];
$acHeadDataById[$entry['accounts_head_id']]['name'] = $entry['name'];
}
$get_sql = "SELECT acc_clients.* FROM acc_clients
WHERE acc_clients.company_id=" . $companyId;
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
$clientTypeListArray = Client::ClientTypeList($em, [$companyId], true);
$clientTypesOfHeads = [];
foreach ($entries as $entry) {
if (!isset($account_head_ids_by_client_type[$entry['type']]))
$account_head_ids_by_client_type[$entry['type']] = [];
$account_head_ids_by_client_type[$entry['type']][] = $entry['accounts_head_id'];
$clientTypesOfHeads[$entry['accounts_head_id']] = $entry['type'];
if ($entry['accounts_head_id'] != $entry['advance_head_id']) {
$account_head_ids_by_client_type[$entry['type']][] = $entry['advance_head_id'];
$clientTypesOfHeads[$entry['advance_head_id']] = $entry['type'];
}
}
// $get_sql = "SELECT * FROM acc_accounts_head WHERE acc_accounts_head.company_id=" . $companyId . "
// AND acc_accounts_head.head_level= 2 and acc_accounts_head.type='lib'";
//
// $stmt = $em->getConnection()->executeStatement($get_sql);
//
// $entries = $stmt;
//
// foreach ($entries as $entry) {
// $has_payments = 1;
// $cash_child_id_list[] = $entry['accounts_head_id'];
// $acHeadList[] = $entry['accounts_head_id'];
// $acHeadDataById[$entry['accounts_head_id']]['name'] = $entry['name'];
// }
$thisBalance_data = Accounts::GetPeriodicDataByType($em, $type = 'ast', $level_to_group_by = 2, $parent_id_list = [], $expand_level = 1, $thisPeriodStartDate, $thisPeriodEndDate, '_during_', [], [], [], true);
$lastBalance_data = Accounts::GetPeriodicDataByType($em, $type = 'ast', $level_to_group_by = 2, $parent_id_list = [], $expand_level = 1, $lastPeriodStartDate, $lastPeriodEndDate, '_during_', [], [], [], true);
$skipHeadIds = [];
$thisBalanceDataByClientType = [];
$lastBalanceDataByClientType = [];
foreach ($clientTypeListArray as $ct) {
$thisBalanceDataByClientType[$ct['id']] = 0;
$lastBalanceDataByClientType[$ct['id']] = 0;
}
foreach ($thisBalance_data as $parentHead => $balanceData) {
foreach ($balanceData['data'] as $acHead => $headData) {
if (!(in_array($acHead, $acHeadList)))
continue;
// if ($headData['head_level'] > 4)
// continue;
$thisBalanceDataByAcHead[$acHead]['balance'] = $headData['end_balance']['balance'];
if (isset($lastBalance_data[$parentHead])) {
if (isset($lastBalance_data[$parentHead]['data'][$acHead]))
$lastBalanceDataByAcHead[$acHead]['balance'] = $lastBalance_data[$parentHead]['data'][$acHead]['end_balance']['balance'];
else
$lastBalanceDataByAcHead[$acHead]['balance'] = 0;
} else
$lastBalanceDataByAcHead[$acHead]['balance'] = 0;
if (!isset($thisBalanceDataByClientType[$clientTypesOfHeads[$acHead]]))
$thisBalanceDataByClientType[$clientTypesOfHeads[$acHead]] = $headData['end_balance']['balance'];
else
$thisBalanceDataByClientType[$clientTypesOfHeads[$acHead]] += $headData['end_balance']['balance'];
if (!isset($lastBalanceDataByClientType[$clientTypesOfHeads[$acHead]]))
$lastBalanceDataByClientType[$clientTypesOfHeads[$acHead]] = 0;
else
$lastBalanceDataByClientType[$clientTypesOfHeads[$acHead]] += $lastBalance_data[$parentHead]['data'][$acHead]['end_balance']['balance'];
$skipHeadIds[] = $acHead;
}
}
foreach ($lastBalance_data as $parentHead => $balanceData) {
foreach ($balanceData['data'] as $acHead => $headData) {
if (in_array($acHead, $skipHeadIds))
continue;
if (!in_array($acHead, $acHeadList))
continue;
// if ($headData['head_level'] > 4)
// continue;
$lastBalanceDataByAcHead[$acHead]['balance'] = $headData['end_balance']['balance'];
if (isset($thisBalance_data[$parentHead])) {
if (isset($thisBalance_data[$parentHead]['data'][$acHead]))
$thisBalanceDataByAcHead[$acHead]['balance'] = $thisBalance_data[$parentHead]['data'][$acHead]['end_balance']['balance'];
else
$thisBalanceDataByAcHead[$acHead]['balance'] = 0;
} else
$thisBalanceDataByAcHead[$acHead]['balance'] = 0;
if (!isset($lastBalanceDataByClientType[$clientTypesOfHeads[$acHead]]))
$lastBalanceDataByClientType[$clientTypesOfHeads[$acHead]] = $headData['end_balance']['balance'];
else
$lastBalanceDataByClientType[$clientTypesOfHeads[$acHead]] += $headData['end_balance']['balance'];
if (!isset($thisBalanceDataByClientType[$clientTypesOfHeads[$acHead]]))
$thisBalanceDataByClientType[$clientTypesOfHeads[$acHead]] = 0;
$skipHeadIds[] = $acHead;
}
}
$thisReportData = array(
// 'accountsHeadList' => $acHeadList,
// 'thisBalance_data' => $thisBalance_data,
// 'confirmedHeadList' => $skipHeadIds,
// 'acHeadDataById' => $acHeadDataById,
'thisBalance_data' => $thisBalance_data,
'lastBalance_data' => $lastBalance_data,
'thisBalanceDataByAcHead' => $thisBalanceDataByAcHead,
'lastBalanceDataByAcHead' => $lastBalanceDataByAcHead,
'clientTypeListArray' => $clientTypeListArray,
'thisBalanceDataByClientType' => $thisBalanceDataByClientType,
'lastBalanceDataByClientType' => $lastBalanceDataByClientType,
);
$data['receivable_position'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
//Sales Position
if (in_array('sales_position', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByClientType = [];
$lastBalanceDataByClientType = [];
$get_sql = "SELECT sum(sales_invoice.invoice_amount) sales_amount,client_type.client_type_id, client_type.name, acc_clients.client_id, acc_clients.type
FROM sales_invoice
join acc_clients on acc_clients.client_id=sales_invoice.client_id
join client_type on acc_clients.type=client_type.client_type_id
WHERE sales_invoice.company_id=" . $companyId . " AND sales_invoice.approved=1
AND sales_invoice_date between '" . $thisPeriodStartDate . " 00:00:00' and '" . $thisPeriodEndDate . " 23:59:59.999'
GROUP BY client_type.client_type_id
";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
$clientTypes = [];
$clientTypeNames = [];
$get_sql_1 = $get_sql;
foreach ($entries as $entry) {
$has_payments = 1;
$thisBalanceDataByClientType[$entry['client_type_id']] = $entry;
$lastBalanceDataByClientType[$entry['client_type_id']]['sales_amount'] = 0; //just for the lulz
if (!in_array($entry['client_type_id'], $clientTypes)) {
$clientTypes[] = $entry['client_type_id'];
$clientTypeNames[$entry['client_type_id']] = $entry['name'];
}
}
$get_sql = "SELECT sum(sales_invoice.invoice_amount) sales_amount,client_type.client_type_id, client_type.name, acc_clients.client_id, acc_clients.type
FROM sales_invoice
join acc_clients on acc_clients.client_id=sales_invoice.client_id
join client_type on acc_clients.type=client_type.client_type_id
WHERE sales_invoice.company_id=" . $companyId . " AND sales_invoice.approved=1
AND sales_invoice_date between '" . $lastPeriodStartDate . " 00:00:00' and '" . $lastPeriodEndDate . " 23:59:59.999'
GROUP BY client_type.client_type_id
";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
foreach ($entries as $entry) {
$has_payments = 1;
$lastBalanceDataByClientType[$entry['client_type_id']] = $entry;
if (!isset($thisBalanceDataByClientType[$entry['client_type_id']]))
$thisBalanceDataByClientType[$entry['client_type_id']]['sales_amount'] = 0; //just for the lulz
if (!in_array($entry['client_type_id'], $clientTypes)) {
$clientTypes[] = $entry['client_type_id'];
$clientTypeNames[$entry['client_type_id']] = $entry['name'];
}
}
$skipHeadIds = [];
$thisReportData = array(
'clientTypes' => $clientTypes,
// 'get_sql_1' => $get_sql_1,
// 'get_sql' => $get_sql,
'clientTypeNames' => $clientTypeNames,
'thisBalanceDataByClientType' => $thisBalanceDataByClientType,
'lastBalanceDataByClientType' => $lastBalanceDataByClientType,
);
$data['sales_position'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
//Collection Position
if (in_array('collection_position', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByClientType = [];
$lastBalanceDataByClientType = [];
$allocationSourceSql = $this->buildAllocationAwareTransactionSourceSql(array(
'document_type' => 6,
'start_date' => $thisPeriodStartDate,
'end_date' => $thisPeriodEndDate,
'approved' => 1,
'ledger_hit' => 1,
));
$get_sql = "SELECT SUM(CASE WHEN alloc_rows.position = 'cr' THEN alloc_rows.allocation_amount ELSE 0 END) positive_amount,
SUM(CASE WHEN alloc_rows.position = 'dr' THEN alloc_rows.allocation_amount ELSE 0 END) negative_amount,client_type.client_type_id, client_type.name, acc_clients.client_id, acc_clients.type
FROM " . $allocationSourceSql . " alloc_rows
join acc_clients on acc_clients.accounts_head_id=alloc_rows.accounts_head_id or acc_clients.advance_head_id=alloc_rows.accounts_head_id
join client_type on acc_clients.type=client_type.client_type_id
WHERE acc_clients.company_id=" . $companyId . "
GROUP BY client_type.client_type_id";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
$clientTypes = [];
$clientTypeNames = [];
foreach ($entries as $entry) {
$has_payments = 1;
$thisBalanceDataByClientType[$entry['client_type_id']] = $entry;
$lastBalanceDataByClientType[$entry['client_type_id']]['collection_amount'] = 0; //just for the lulz
$thisBalanceDataByClientType[$entry['client_type_id']]['collection_amount'] = $entry['positive_amount'] - $entry['negative_amount'];
if (!in_array($entry['client_type_id'], $clientTypes)) {
$clientTypes[] = $entry['client_type_id'];
$clientTypeNames[$entry['client_type_id']] = $entry['name'];
}
}
$allocationSourceSql = $this->buildAllocationAwareTransactionSourceSql(array(
'document_type' => 6,
'start_date' => $lastPeriodStartDate,
'end_date' => $lastPeriodEndDate,
'approved' => 1,
'ledger_hit' => 1,
));
$get_sql = "SELECT SUM(CASE WHEN alloc_rows.position = 'cr' THEN alloc_rows.allocation_amount ELSE 0 END) positive_amount,
SUM(CASE WHEN alloc_rows.position = 'dr' THEN alloc_rows.allocation_amount ELSE 0 END) negative_amount,client_type.client_type_id, client_type.name, acc_clients.client_id, acc_clients.type
FROM " . $allocationSourceSql . " alloc_rows
join acc_clients on acc_clients.accounts_head_id=alloc_rows.accounts_head_id or acc_clients.advance_head_id=alloc_rows.accounts_head_id
join client_type on acc_clients.type=client_type.client_type_id
WHERE acc_clients.company_id=" . $companyId . "
GROUP BY client_type.client_type_id";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
foreach ($entries as $entry) {
$has_payments = 1;
$lastBalanceDataByClientType[$entry['client_type_id']] = $entry;
$lastBalanceDataByClientType[$entry['client_type_id']]['collection_amount'] = $entry['positive_amount'] - $entry['negative_amount'];
if (!isset($thisBalanceDataByClientType[$entry['client_type_id']]))
$thisBalanceDataByClientType[$entry['client_type_id']]['collection_amount'] = 0; //just for the lulz
if (!in_array($entry['client_type_id'], $clientTypes)) {
$clientTypes[] = $entry['client_type_id'];
$clientTypeNames[$entry['client_type_id']] = $entry['name'];
}
}
$skipHeadIds = [];
$thisReportData = array(
'clientTypes' => $clientTypes,
'clientTypeNames' => $clientTypeNames,
'thisBalanceDataByClientType' => $thisBalanceDataByClientType,
'lastBalanceDataByClientType' => $lastBalanceDataByClientType,
);
$data['collection_position'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
//Return Position
if (in_array('market_return_position', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByClientType = [];
$lastBalanceDataByClientType = [];
$get_sql = "SELECT sum(irr_item.received_total_sales_price) return_amount,client_type.client_type_id, client_type.name, acc_clients.client_id, acc_clients.type
FROM irr_item
join item_received_replacement on item_received_replacement.irr_id=irr_item.irr_id
join acc_clients on acc_clients.client_id=item_received_replacement.client_id
join client_type on acc_clients.type=client_type.client_type_id
WHERE item_received_replacement.company_id=" . $companyId . " AND item_received_replacement.approved=1
AND item_received_replacement.irr_date between '" . $thisPeriodStartDate . " 00:00:00' and '" . $thisPeriodEndDate . " 23:59:59.999'
GROUP BY client_type.client_type_id
";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
$clientTypes = [];
$clientTypeNames = [];
$get_sql_1 = $get_sql;
foreach ($entries as $entry) {
$has_payments = 1;
$thisBalanceDataByClientType[$entry['client_type_id']] = $entry;
$lastBalanceDataByClientType[$entry['client_type_id']]['return_amount'] = 0; //just for the lulz
if (!in_array($entry['client_type_id'], $clientTypes)) {
$clientTypes[] = $entry['client_type_id'];
$clientTypeNames[$entry['client_type_id']] = $entry['name'];
}
}
$get_sql = "SELECT sum(irr_item.received_total_sales_price) return_amount,client_type.client_type_id, client_type.name, acc_clients.client_id, acc_clients.type
FROM irr_item
join item_received_replacement on item_received_replacement.irr_id=irr_item.irr_id
join acc_clients on acc_clients.client_id=item_received_replacement.client_id
join client_type on acc_clients.type=client_type.client_type_id
WHERE item_received_replacement.company_id=" . $companyId . " AND item_received_replacement.approved=1
AND item_received_replacement.irr_date between '" . $lastPeriodStartDate . " 00:00:00' and '" . $lastPeriodEndDate . " 23:59:59.999'
GROUP BY client_type.client_type_id
";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
foreach ($entries as $entry) {
$has_payments = 1;
$lastBalanceDataByClientType[$entry['client_type_id']] = $entry;
if (!isset($thisBalanceDataByClientType[$entry['client_type_id']]))
$thisBalanceDataByClientType[$entry['client_type_id']]['return_amount'] = 0; //just for the lulz
if (!in_array($entry['client_type_id'], $clientTypes)) {
$clientTypes[] = $entry['client_type_id'];
$clientTypeNames[$entry['client_type_id']] = $entry['name'];
}
}
$skipHeadIds = [];
$thisReportData = array(
'clientTypes' => $clientTypes,
// 'get_sql_1' => $get_sql_1,
// 'get_sql' => $get_sql,
'clientTypeNames' => $clientTypeNames,
'thisBalanceDataByClientType' => $thisBalanceDataByClientType,
'lastBalanceDataByClientType' => $lastBalanceDataByClientType,
);
$data['market_return_position'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
//order and client count
if (in_array('order_and_client_count', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByAcHead = [];
$lastBalanceDataByAcHead = [];
$today = new \DateTime();
$get_sql = "SELECT count(sales_order_id) total_so_count, sum(so_amount) total_so_amount, sum(invoice_amount) total_invoice_amount FROM sales_order WHERE sales_order.company_id=" . $companyId . " and sales_order.sales_order_date between '" . $thisPeriodStartDate . " 00:00:00' and '" . $thisPeriodEndDate . " 23:59:59.999' ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries1 = $stmt;
$get_sql = "SELECT count(client_id) total_clients FROM acc_clients WHERE acc_clients.company_id=" . $companyId;
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries2 = $stmt;
$currCompanyData = Company::getCompanyData($em, $session->get(UserConstants::USER_COMPANY_ID, $companyId));
$allocationSourceSql = $this->buildAllocationAwareTransactionSourceSql(array(
'document_type' => 6,
'company_id' => $companyId,
'start_date' => $thisPeriodStartDate,
'end_date' => $thisPeriodEndDate,
'approved' => 1,
'ledger_hit' => 1,
));
$get_sql = "SELECT SUM(CASE WHEN alloc_rows.position = 'cr' THEN alloc_rows.allocation_amount ELSE ((-1)*alloc_rows.allocation_amount) END) total_collection_amount, alloc_rows.document_hash FROM " . $allocationSourceSql . " alloc_rows
join acc_clients on acc_clients.accounts_head_id=alloc_rows.accounts_head_id or acc_clients.advance_head_id=alloc_rows.accounts_head_id
WHERE acc_clients.company_id=" . $companyId . " ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries3 = $stmt;
//
// $get_sql = "SELECT (select c.balance last_balance from inv_closing_balance c
//where c.product_id=a.product_id and c.warehouse_id=a.warehouse_id and c.action_tag_id=a.action_tag_id order by date desc limit 1) stock_amount
//from inv_closing_balance a
// where a.warehouse_id in (select distinct id from warehouse
// WHERE warehouse.company_id=" . $companyId . ") and a.date between '" . $thisPeriodStartDate . " 00:00:00' and '" . $thisPeriodEndDate . " 23:59:59.999' ";
$get_sql = "SELECT a.* , (select c.balance last_balance from inv_closing_balance c
where date <='" . $thisPeriodEndDate . " 00:00:00' and c.product_id=a.product_id and c.warehouse_id=a.warehouse_id and c.action_tag_id=a.action_tag_id order by date desc limit 1) last_balance
from inv_closing_balance a
where a.warehouse_id in (select distinct id from warehouse
WHERE warehouse.company_id=" . $companyId . ") ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries4 = $stmt;
$currCompanyData = Company::getCompanyData($em, $session->get(UserConstants::USER_COMPANY_ID, $companyId));
$total_stock_amount = 0;
foreach ($entries4 as $ent) {
$total_stock_amount += $ent['last_balance'];
}
$thisReportData = array(
// 'total_stock_amount' => empty($entries3) ? 0 : $entries4[0]['total_stock_amount'],
'total_stock_amount' => $total_stock_amount,
'total_collection_amount' => empty($entries3) ? 0 : $entries3[0]['total_collection_amount'],
'total_so_count' => empty($entries1) ? 0 : $entries1[0]['total_so_count'],
'total_so_amount' => empty($entries1) ? 0 : $entries1[0]['total_so_amount'],
'total_invoice_amount' => empty($entries1) ? 0 : $entries1[0]['total_invoice_amount'],
'total_receivable' => $currCompanyData->getReceivable(),
'total_payable' => $currCompanyData->getPayable(),
'total_clients' => empty($entries2) ? 0 : $entries2[0]['total_clients'],
);
$data['order_and_client_count'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
//Inventory Position
if (in_array('inventory_position', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByActionTag = [];
$lastBalanceDataByActionTag = [];
$warehouseList = Inventory::warehouse_action_list($em, $companyId, $method = 'object');
$get_sql = "SELECT a.* , (select c.balance last_balance from inv_closing_balance c
where date <='" . $thisPeriodEndDate . " 00:00:00' and c.product_id=a.product_id and c.warehouse_id=a.warehouse_id and c.action_tag_id=a.action_tag_id order by date desc limit 1) last_balance
from inv_closing_balance a
where a.warehouse_id in (select distinct id from warehouse
WHERE warehouse.company_id=" . $companyId . ") ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
$confirmed_action_ids = [];
$confirmed_action_names = [];
$get_sql_1 = $get_sql;
foreach ($entries as $entry) {
$has_payments = 1;
if (isset($thisBalanceDataByActionTag[$entry['action_tag_id']]))
$thisBalanceDataByActionTag[$entry['action_tag_id']]['inventory_amount'] += $entry['last_balance'];
else
$thisBalanceDataByActionTag[$entry['action_tag_id']]['inventory_amount'] = $entry['last_balance'];
if (!isset($lastBalanceDataByActionTag[$entry['action_tag_id']]))
$lastBalanceDataByActionTag[$entry['action_tag_id']]['inventory_amount'] = 0;
if (!in_array($entry['action_tag_id'], $confirmed_action_ids)) {
$confirmed_action_ids[] = $entry['action_tag_id'];
$confirmed_action_names[$entry['action_tag_id']] = $warehouseList[$entry['action_tag_id']]['name'];
}
}
$get_sql = "SELECT a.* , (select c.balance last_balance from inv_closing_balance c
where date <='" . $lastPeriodEndDate . " 00:00:00' and c.product_id=a.product_id and c.warehouse_id=a.warehouse_id and c.action_tag_id=a.action_tag_id order by date desc limit 1) last_balance
from inv_closing_balance a
where a.warehouse_id in (select distinct id from warehouse
WHERE warehouse.company_id=" . $companyId . ") ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
foreach ($entries as $entry) {
$has_payments = 1;
if (isset($lastBalanceDataByActionTag[$entry['action_tag_id']]))
$lastBalanceDataByActionTag[$entry['action_tag_id']]['inventory_amount'] += $entry['last_balance'];
else
$lastBalanceDataByActionTag[$entry['action_tag_id']]['inventory_amount'] = $entry['last_balance'];
if (!isset($thisBalanceDataByActionTag[$entry['action_tag_id']]))
$thisBalanceDataByActionTag[$entry['action_tag_id']]['inventory_amount'] = 0;
if (!in_array($entry['action_tag_id'], $confirmed_action_ids)) {
$confirmed_action_ids[] = $entry['action_tag_id'];
$confirmed_action_names[$entry['action_tag_id']] = $warehouseList[$entry['action_tag_id']]['name'];
}
}
$skipHeadIds = [];
$thisReportData = array(
// 'clientTypes' => $clientTypes,
// 'get_sql_1' => $get_sql_1,
// 'get_sql' => $get_sql,
'confirmed_action_ids' => $confirmed_action_ids,
'confirmed_action_names' => $confirmed_action_names,
'thisBalanceDataByActionTag' => $thisBalanceDataByActionTag,
'lastBalanceDataByActionTag' => $lastBalanceDataByActionTag,
);
$data['inventory_position'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
//Production Position
if (in_array('production_position', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByIgId = [];
$lastBalanceDataByIgId = [];
$itemGroups = [];
$itemGroupNames = [];
$get_sql = "SELECT sum((case WHEN inv_products.curr_sales_price!=0 then inv_products.curr_sales_price else inv_products.curr_purchase_price END )*production_entry_item.produced_qty) production_amount,inv_item_group.id item_group_id ,inv_item_group.name item_group_name ,inv_products.id product_id
FROM production_entry_item
join production on production_entry_item.production_id=production.production_id
join inv_products on production_entry_item.product_id=inv_products.id
join inv_item_group on inv_item_group.id=inv_products.ig_id
WHERE production.company_id=" . $companyId . " AND production.approved=1
AND production.production_date between '" . $thisPeriodStartDate . " 00:00:00' and '" . $thisPeriodEndDate . " 23:59:59.999'
GROUP BY inv_item_group.id
";
// $get_sql = "SELECT sum(product_mrp.average_price*production_entry_item.produced_qty) production_amount,inv_item_group.id item_group_id ,inv_item_group.name item_group_name ,inv_products.id product_id
// FROM production_entry_item
// join production on production_entry_item.production_id=production.production_id
// join inv_products on production_entry_item.product_id=inv_products.id
// join product_mrp on production_entry_item.product_id=product_mrp.product_id
// join inv_item_group on inv_item_group.id=inv_products.ig_id
// WHERE production.company_id=" . $companyId . " AND production.approved=1
// AND production.production_date between '" . $thisPeriodStartDate . " 00:00:00' and '" . $thisPeriodEndDate . " 23:59:59.999'
// GROUP BY inv_item_group.id
// ";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
$get_sql_1 = $get_sql;
foreach ($entries as $entry) {
$has_payments = 1;
$thisBalanceDataByIgId[$entry['item_group_id']] = $entry;
$lastBalanceDataByIgId[$entry['item_group_id']]['production_amount'] = 0; //just for the lulz
if (!in_array($entry['item_group_id'], $clientTypes)) {
$itemGroups[] = $entry['item_group_id'];
$itemGroupNames[$entry['item_group_id']] = $entry['item_group_name'];
}
}
$get_sql = "SELECT sum(production_entry_item.price*production_entry_item.produced_qty) production_amount,inv_item_group.id item_group_id ,inv_item_group.name item_group_name ,inv_products.id product_id
FROM production_entry_item
join production on production_entry_item.production_id=production.production_id
join inv_products on production_entry_item.product_id=inv_products.id
join inv_item_group on inv_item_group.id=inv_products.ig_id
WHERE production.company_id=" . $companyId . " AND production.approved=1
AND production.production_date between '" . $lastPeriodStartDate . " 00:00:00' and '" . $lastPeriodEndDate . " 23:59:59.999'
GROUP BY inv_item_group.id
";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
foreach ($entries as $entry) {
$has_payments = 1;
$lastBalanceDataByIgId[$entry['item_group_id']] = $entry;
if (!isset($thisBalanceDataByIgId[$entry['item_group_id']]))
$thisBalanceDataByIgId[$entry['item_group_id']]['production_amount'] = 0; //just for the lulz
if (!in_array($entry['item_group_id'], $clientTypes)) {
$itemGroups[] = $entry['item_group_id'];
$itemGroupNames[$entry['item_group_id']] = $entry['item_group_name'];
}
}
$skipHeadIds = [];
$thisReportData = array(
'itemGroups' => $itemGroups,
// 'get_sql_1' => $get_sql_1,
// 'get_sql' => $get_sql,
'itemGroupNames' => $itemGroupNames,
'thisBalanceDataByIgId' => $thisBalanceDataByIgId,
'lastBalanceDataByIgId' => $lastBalanceDataByIgId,
);
$data['production_position'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
//Top Products
if (in_array('top_selling_item_position', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByRelId = [];
$lastBalanceDataByRelId = [];
$clientIds = [];
$clientNames = [];
$productIds = [];
$productNames = [];
$get_sql = "SELECT sum(sales_invoice_item.qty*sales_invoice_item.current_purchase_price) sales_amount, inv_products.id product_id, inv_products.name product_name, acc_clients.client_id, acc_clients.type
FROM sales_invoice_item
join sales_invoice on sales_invoice_item.sales_invoice_id=sales_invoice.sales_invoice_id
join inv_products on sales_invoice_item.product_id=inv_products.id
join acc_clients on acc_clients.client_id=sales_invoice.client_id
WHERE sales_invoice.company_id=" . $companyId . " AND sales_invoice.approved=1
AND sales_invoice_date between '" . $thisPeriodStartDate . " 00:00:00' and '" . $thisPeriodEndDate . " 23:59:59.999'
GROUP BY sales_invoice_item.product_id
ORDER BY sales_amount DESC
LIMIT 10
";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
$get_sql_1 = $get_sql;
for ($k = 0; $k < 10; $k++) {
$has_payments = 1;
if (!isset($entries[$k]))
$entries[$k] = array(
'sales_amount' => '',
'product_name' => '',
'client_name' => '',
);
$has_payments = 1;
$thisBalanceDataByRelId[$k] = $entries[$k];
}
$get_sql = "SELECT sum(sales_invoice_item.qty*sales_invoice_item.current_purchase_price) sales_amount, inv_products.id product_id, inv_products.name product_name, acc_clients.client_id, acc_clients.type
FROM sales_invoice_item
join sales_invoice on sales_invoice_item.sales_invoice_id=sales_invoice.sales_invoice_id
join inv_products on sales_invoice_item.product_id=inv_products.id
join acc_clients on acc_clients.client_id=sales_invoice.client_id
WHERE sales_invoice.company_id=" . $companyId . " AND sales_invoice.approved=1
AND sales_invoice_date between '" . $lastPeriodStartDate . " 00:00:00' and '" . $lastPeriodEndDate . " 23:59:59.999'
GROUP BY sales_invoice_item.product_id
ORDER BY sales_amount DESC
LIMIT 10
";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
for ($k = 0; $k < 10; $k++) {
$has_payments = 1;
if (!isset($entries[$k]))
$entries[$k] = array(
'sales_amount' => '',
'product_name' => '',
'client_name' => '',
);
$lastBalanceDataByRelId[$k] = $entries[$k];
}
$skipHeadIds = [];
$thisReportData = array(
// 'clientTypes' => $clientTypes,
//// 'get_sql_1' => $get_sql_1,
//// 'get_sql' => $get_sql,
// 'clientTypeNames' => $clientTypeNames,
'thisBalanceDataByRelId' => $thisBalanceDataByRelId,
'lastBalanceDataByRelId' => $lastBalanceDataByRelId,
);
$data['top_selling_item_position'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
//Top Products
if (in_array('top_selling_client_position', $reportsToGet)) {
$acHeadList = [];
$companyId = explode('_', $app_company_index)[1];
$acHeadDataById = [];
$thisBalanceDataByRelId = [];
$lastBalanceDataByRelId = [];
$clientIds = [];
$clientNames = [];
$productIds = [];
$productNames = [];
$get_sql = "SELECT sum(sales_invoice.invoice_amount) sales_amount, acc_clients.client_id, acc_clients.client_name client_name
FROM sales_invoice
join acc_clients on acc_clients.client_id=sales_invoice.client_id
WHERE sales_invoice.company_id=" . $companyId . " AND sales_invoice.approved=1
AND sales_invoice_date between '" . $thisPeriodStartDate . " 00:00:00' and '" . $thisPeriodEndDate . " 23:59:59.999'
GROUP BY sales_invoice.client_id
ORDER BY sales_amount DESC
LIMIT 10
";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
$get_sql_1 = $get_sql;
for ($k = 0; $k < 10; $k++) {
$has_payments = 1;
if (!isset($entries[$k]))
$entries[$k] = array(
'sales_amount' => '',
'product_name' => '',
'client_name' => '',
);
$has_payments = 1;
$thisBalanceDataByRelId[$k] = $entries[$k];
}
$get_sql = "SELECT sum(sales_invoice.invoice_amount) sales_amount, acc_clients.client_id, acc_clients.client_name client_name
FROM sales_invoice
join acc_clients on acc_clients.client_id=sales_invoice.client_id
WHERE sales_invoice.company_id=" . $companyId . " AND sales_invoice.approved=1
AND sales_invoice_date between '" . $lastPeriodStartDate . " 00:00:00' and '" . $lastPeriodEndDate . " 23:59:59.999'
GROUP BY sales_invoice.client_id
ORDER BY sales_amount DESC
LIMIT 10
";
$stmt = $em->getConnection()->fetchAllAssociative($get_sql);
$entries = $stmt;
for ($k = 0; $k < 10; $k++) {
$has_payments = 1;
if (!isset($entries[$k]))
$entries[$k] = array(
'sales_amount' => '',
'product_name' => '',
'client_name' => '',
);
$lastBalanceDataByRelId[$k] = $entries[$k];
}
$skipHeadIds = [];
$thisReportData = array(
// 'clientTypes' => $clientTypes,
//// 'get_sql_1' => $get_sql_1,
//// 'get_sql' => $get_sql,
// 'clientTypeNames' => $clientTypeNames,
'thisBalanceDataByRelId' => $thisBalanceDataByRelId,
'lastBalanceDataByRelId' => $lastBalanceDataByRelId,
);
$data['top_selling_client_position'][$app_company_index] = $thisReportData;
// $companyListData[] = Company::getCompanyListWithImage($em);
}
}
}
}
return new JsonResponse(
array(
'success' => true,
'data' => $data,
'companyListData' => $companyListData,
'dataToConnectList' => $dataToConnectList,
'periodType' => $periodType,
'queriedReportsToGet' => $request->request->get('reportsToGet'),
'reportsToGet' => $reportsToGet,
'thisPeriodStartDate' => $thisPeriodStartDate,
'thisPeriodEndDate' => $thisPeriodEndDate,
'lastPeriodStartDate' => $lastPeriodStartDate,
'lastPeriodEndDate' => $lastPeriodEndDate,
'companyIdListByAppId' => $companyIdListByAppId,
'companyNameListByAppId' => $companyNameListByAppId,
'companyImageListByAppId' => $companyImageListByAppId,
'appIdList' => $appIdList,
)
);
}
public function indexAction(Request $request, $cgHash = '')
{
$session = $request->getSession();
$systemType = $this->container->hasParameter('system_type') ? $this->container->getParameter('system_type') : '_ERP_';
if ($systemType == '_ERP_') {
//do nothing its default to dashboard index
} else if ($systemType == '_BUDDYBEE_') {
if ($cgHash != '')
$session->set('codeHash', $cgHash);
}
// Generic::debugMessage($session);
$ROUTE_LIST = json_decode($session->get(UserConstants::USER_ROUTE_LIST), true);
if ($ROUTE_LIST == null)
$ROUTE_LIST = [];
$CurrentRoute = $request->attributes->get('_route');
if ($session->get(UserConstants::USER_ROUTE_LIST) == 1 || in_array('management_dashboard', $ROUTE_LIST)) {
// User is not authorized. send him to dashboard
// $controller->addFlash(
// 'error',
// 'Sorry Couldnot insert Data.'
// );
// return $this->render('ApplicationBundle:pages/dashboard:advanced_management_dashboard.html.twig',
if ($session->has('isMobile'))
if ($session->get('isMobile') == true) {
return $this->render(
'@Application/pages/dashboard/index_finance_mobile.html.twig',
array(
'page_title' => 'Finance Dashboard',
'dashboardDataForUser' => []
)
);
}
return $this->render(
'@Application/pages/dashboard/advanced_management_dashboard.html.twig',
array(
'page_title' => 'Dashboard'
)
);
}
if ($session->has('isMobile'))
if ($session->get('isMobile') == true) {
if ($systemType == '_CENTRAL_') {
$defaultRoute = 'central_landing';
return $this->redirectToRoute($defaultRoute);
}
elseif ($systemType == '_SOPHIA_') {
$defaultRoute = 'sofia_dashboard_admin';
return $this->redirectToRoute($defaultRoute);
}else
return $this->render(
'@Application/pages/dashboard/index_finance_mobile.html.twig',
array(
'page_title' => 'Finance Dashboard',
'dashboardDataForUser' => []
)
);
}
else {
//check if user has a default route if yes redirect to it
if ($session->get(UserConstants::USER_TYPE) == UserConstants::USER_TYPE_SUPPLIER)
$defaultRoute = 'supplier_dashboard';
else if ($session->get(UserConstants::USER_TYPE) == UserConstants::USER_TYPE_CLIENT)
$defaultRoute = 'client_dashboard';
else if ($session->get(UserConstants::USER_TYPE) == UserConstants::USER_TYPE_APPLICANT) {
if ($systemType == '_CENTRAL_') {
$defaultRoute = 'central_landing';
}
elseif ($systemType == '_SOPHIA_') {
$defaultRoute = 'sofia_dashboard_admin';
} else
$defaultRoute = 'applicant_dashboard';
} else
$defaultRoute = $session->get(UserConstants::USER_DEFAULT_ROUTE);
// if(in_array($defaultRoute, $ROUTE_LIST))
if ($defaultRoute != '' && $defaultRoute != null)
return $this->redirectToRoute($defaultRoute);
else
return $this->redirectToRoute('sales_dashboard');
// return $this->render(
// 'ApplicationBundle:pages/dashboard:index.html.twig',
// array(
// 'page_title' => 'Dashboard',
// 'allocation_filters' => [],
// 'allocationFilters' => [],
// 'allocation_tag_types' => [],
// 'allocation_tag_values_by_type' => [],
// 'project_list' => [],
// 'branch_list' => [],
// 'cost_centers' => []
// )
// );
}
}
public function indexSupplierAction(Request $request)
{
$session = $request->getSession();
$supplierId = 3;
if ($request->query->has('supplierId')) //for debug now
{
$session->set('supplierId', $request->query->get('supplierId'));
}
$supplierId = $session->get('supplierId'); //////////////////////LATER
$em = $this->getDoctrine()->getManager();
$companyId = $this->getLoggedUserCompanyId($request);
$userId = $request->getSession()->get(UserConstants::USER_ID);
$SD = Supplier::GetSupplierProfileDetails($em, $supplierId, 'SI');
$dashboardDataForUser = [];
return $this->render(
'@Application/pages/dashboard/index_supplier.html.twig',
array(
'page_title' => 'Supplier Dashboard',
'dashboardDataForUser' => $dashboardDataForUser,
'data' => $SD,
'igList' => Inventory::ItemGroupList($em, $companyId)
)
);
}
public function indexClientAction(Request $request)
{
$session = $request->getSession();
$clientId = 3;
if ($request->query->has('clientId')) //for debug now
{
$session->set('clientId', $request->query->get('clientId'));
}
$clientId = $session->get('clientId'); //////////////////////LATER
$em = $this->getDoctrine()->getManager();
$companyId = $this->getLoggedUserCompanyId($request);
$userId = $request->getSession()->get(UserConstants::USER_ID);
$SD = Client::GetClientProfileDetails($em, $clientId, 'CI');
$dashboardDataForUser = [];
return $this->render(
'@Application/pages/dashboard/index_client.html.twig',
array(
'page_title' => 'Client Dashboard',
'dashboardDataForUser' => $dashboardDataForUser,
'data' => $SD,
'igList' => Inventory::ItemGroupList($em, $companyId)
)
);
}
public function indexFinanceAction(Request $request)
{
$session = $request->getSession();
$dashboardDataForUser = [];
$em = $this->getDoctrine()->getManager();
$companyId = $this->getLoggedUserCompanyId($request);
$userId = $request->getSession()->get(UserConstants::USER_ID);
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
'userId' => $userId,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
if (empty($dbQuery)) ///now get the defaults
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
// 'userId'=>$userId,
'defaultBoxFlag' => 1,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
foreach ($dbQuery as $entry) {
$dashboardDataForUser[] = array(
'id' => $entry->getId(),
'widgetId' => $entry->getId(),
'widgetSequence' => $entry->getSequence(),
'widgetStatus' => $entry->getStatus(),
'widgetData' => json_decode($entry->getData(), true),
'defaultBoxFlag' => $entry->getDefaultBoxFlag(),
'widgetSizeTiny' => $entry->getSizeTiny(),
'widgetSizeSmall' => $entry->getSizeSmall(),
'widgetSizeMedium' => $entry->getSizeMedium(),
'widgetSizeLarge' => $entry->getSizeLarge(),
'refreshInterval' => $entry->getRefreshInterval(),
);
}
if ($session->has('isMobile')) {
if ($session->get('isMobile') == true) {
return $this->render(
'@Application/pages/dashboard/index_finance_mobile.html.twig',
array(
'page_title' => 'Finance Dashboard',
'dashboardDataForUser' => $dashboardDataForUser
)
);
} else {
}
}
return $this->render(
'@Accounts/pages/finance_dashboard.html.twig',
array(
'page_title' => 'Finance Dashboard',
'dashboardDataForUser' => $dashboardDataForUser,
'dashboardAnalytics' => $this->buildFinanceDashboardAnalytics($em)
)
);
}
/**
* Lean, fail-safe analytics for the Finance cp-shell dashboard. Guarded scalar DQL —
* a schema/tenant quirk degrades to 0 rather than 500-ing the dashboard.
*/
private function buildFinanceDashboardAnalytics($em)
{
$scalar = function ($dql, $params = []) use ($em) {
try {
$v = $em->createQuery($dql)->setParameters($params)->getSingleScalarResult();
return $v === null ? 0 : $v;
} catch (\Throwable $e) {
return 0;
}
};
$salesInv = (int) $scalar("SELECT COUNT(i) FROM ApplicationBundle:SalesInvoice i WHERE (i.deleteFlag = 0 OR i.deleteFlag IS NULL)");
$expenseInv = (int) $scalar("SELECT COUNT(e) FROM ApplicationBundle:ExpenseInvoice e WHERE (e.deleteFlag = 0 OR e.deleteFlag IS NULL)");
$banks = (int) $scalar("SELECT COUNT(b) FROM ApplicationBundle:BankAccounts b");
$salesPending = (int) $scalar("SELECT COUNT(i) FROM ApplicationBundle:SalesInvoice i WHERE i.approved <> :a AND (i.deleteFlag = 0 OR i.deleteFlag IS NULL)", ['a' => GeneralConstant::APPROVED]);
$expPending = (int) $scalar("SELECT COUNT(e) FROM ApplicationBundle:ExpenseInvoice e WHERE e.approved <> :a AND (e.deleteFlag = 0 OR e.deleteFlag IS NULL)", ['a' => GeneralConstant::APPROVED]);
// ── Money figures (outstanding + this-month), fail-safe ──
// Receivable/Payable come from the GL (transactions × each leg's FX rate to base), NOT the
// invoice sub-ledger (i.dueAmount) nor the stored current_balance. Both of those drift when a
// payment is booked as a journal/contra voucher instead of an invoice-linked receipt — verified
// on a live tenant where the sub-ledger read 38,519 but the GL truth was 2,927. Receivable
// (client) heads are debit-nature (dr−cr); payable (supplier) heads credit-nature (cr−dr).
// NULLIF(rate,0)→1 keeps base-currency legs (no rate) unconverted.
$rawScalar = function ($sql) use ($em) {
try { $v = $em->getConnection()->fetchOne($sql); return ($v === null || $v === false) ? 0.0 : (float) $v; }
catch (\Throwable $e) { return 0.0; }
};
$arOutstanding = $rawScalar(
"SELECT COALESCE(SUM(td.amount * COALESCE(NULLIF(td.currency_multiply_rate,0),1) * (CASE WHEN td.position='dr' THEN 1 ELSE -1 END)),0)
FROM acc_transaction_details td
WHERE td.accounts_head_id IN (SELECT accounts_head_id FROM acc_clients WHERE accounts_head_id IS NOT NULL)");
$apOutstanding = $rawScalar(
"SELECT COALESCE(SUM(td.amount * COALESCE(NULLIF(td.currency_multiply_rate,0),1) * (CASE WHEN td.position='cr' THEN 1 ELSE -1 END)),0)
FROM acc_transaction_details td
WHERE td.accounts_head_id IN (SELECT accounts_head_id FROM acc_suppliers WHERE accounts_head_id IS NOT NULL)");
$monthStart = (new \DateTime('first day of this month 00:00:00'))->format('Y-m-d H:i:s');
$salesThisMonth = (float) $scalar("SELECT COALESCE(SUM(i.invoiceAmount),0) FROM ApplicationBundle:SalesInvoice i WHERE i.createdAt >= :d AND (i.deleteFlag = 0 OR i.deleteFlag IS NULL)", ['d' => $monthStart]);
$expenseThisMonth = (float) $scalar("SELECT COALESCE(SUM(e.invoiceAmount),0) FROM ApplicationBundle:ExpenseInvoice e WHERE e.createdAt >= :d AND (e.deleteFlag = 0 OR e.deleteFlag IS NULL)", ['d' => $monthStart]);
// ── Reuse the company cash-flow builder for the mini trend + current cash ──
$cashFlow = ['hasData' => false];
try { $cashFlow = $this->buildCompanyCashFlow($em, 12); } catch (\Throwable $e) { $cashFlow = ['hasData' => false]; }
// Base (functional) currency so the money tiles are labelled, not bare numbers.
$baseCurrency = '';
try { $baseCurrency = (string) \ApplicationBundle\Modules\Accounts\Service\CompanyCurrencyResolver::getFunctionalCurrencyCode($em, 1); } catch (\Throwable $e) { $baseCurrency = ''; }
return [
'base_currency' => $baseCurrency,
'kpis' => [
['label' => 'Sales Invoices', 'value' => $salesInv, 'note' => 'Receivables'],
['label' => 'Expense Invoices', 'value' => $expenseInv, 'note' => 'Payables'],
['label' => 'Bank Accounts', 'value' => $banks, 'note' => 'Cash & bank'],
['label' => 'Pending AR', 'value' => $salesPending, 'note' => 'Sales invoices to approve'],
['label' => 'Pending AP', 'value' => $expPending, 'note' => 'Expense invoices to approve'],
],
'money' => [
['label' => 'Current Cash', 'value' => isset($cashFlow['closingBalance']) ? $cashFlow['closingBalance'] : 0, 'note' => 'Cash & bank balance', 'accent' => 'a'],
['label' => 'Receivable Due', 'value' => round($arOutstanding, 2), 'note' => 'Outstanding from customers', 'accent' => 'b'],
['label' => 'Payable Due', 'value' => round($apOutstanding, 2), 'note' => 'Outstanding to suppliers', 'accent' => 'c'],
['label' => 'Invoiced (mo)', 'value' => round($salesThisMonth, 2), 'note' => 'Sales billed this month', 'accent' => 'b'],
['label' => 'Expensed (mo)', 'value' => round($expenseThisMonth, 2), 'note' => 'Expenses this month', 'accent' => 'c'],
],
'cashFlow' => $cashFlow,
'pending_items' => [
['label' => 'Sales Invoices', 'value' => $salesPending, 'note' => 'Receivable approvals'],
['label' => 'Expense Invoices', 'value' => $expPending, 'note' => 'Payable approvals'],
],
];
}
public function indexSalesAction(Request $request)
{
$session = $request->getSession();
$dashboardDataForUser = [];
$em = $this->getDoctrine()->getManager();
$companyId = $this->getLoggedUserCompanyId($request);
$userId = $request->getSession()->get(UserConstants::USER_ID);
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
'userId' => $userId,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
if (empty($dbQuery)) ///now get the defaults
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
// 'userId'=>$userId,
'defaultBoxFlag' => 1,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
foreach ($dbQuery as $entry) {
$dashboardDataForUser[] = array(
'id' => $entry->getId(),
'widgetId' => $entry->getId(),
'widgetSequence' => $entry->getSequence(),
'widgetStatus' => $entry->getStatus(),
'widgetData' => json_decode($entry->getData(), true),
'defaultBoxFlag' => $entry->getDefaultBoxFlag(),
'widgetSizeTiny' => $entry->getSizeTiny(),
'widgetSizeSmall' => $entry->getSizeSmall(),
'widgetSizeMedium' => $entry->getSizeMedium(),
'widgetSizeLarge' => $entry->getSizeLarge(),
'refreshInterval' => $entry->getRefreshInterval(),
);
}
if ($session->has('isMobile')) {
if ($session->get('isMobile') == true) {
return $this->render(
'@Application/pages/dashboard/index_finance_mobile.html.twig',
array(
'page_title' => 'Finance Dashboard',
'dashboardDataForUser' => $dashboardDataForUser
)
);
} else {
}
}
return $this->render(
'@Sales/pages/sales_dashboard.html.twig',
array(
'page_title' => 'Sales Dashboard',
'dashboardDataForUser' => $dashboardDataForUser,
'dashboardAnalytics' => $this->buildSalesDashboardAnalytics($em)
)
);
}
/**
* Fail-safe analytics for the Sales cp-shell dashboard — KPIs + 6-month order-value trend +
* a health snapshot. Every metric/query is guarded so a schema/tenant quirk degrades to
* empty rather than 500-ing the dashboard.
*/
private function buildSalesDashboardAnalytics($em)
{
$scalar = function ($dql, $params = []) use ($em) {
try {
$v = $em->createQuery($dql)->setParameters($params)->getSingleScalarResult();
return $v === null ? 0 : $v;
} catch (\Throwable $e) {
return 0;
}
};
$soTotal = (int) $scalar("SELECT COUNT(s) FROM ApplicationBundle:SalesOrder s WHERE (s.deleteFlag = 0 OR s.deleteFlag IS NULL)");
$soApproved = (int) $scalar("SELECT COUNT(s) FROM ApplicationBundle:SalesOrder s WHERE s.approved = :a AND (s.deleteFlag = 0 OR s.deleteFlag IS NULL)", ['a' => GeneralConstant::APPROVED]);
$soValue = (float) $scalar("SELECT SUM(s.soAmount) FROM ApplicationBundle:SalesOrder s WHERE s.approved = :a AND (s.deleteFlag = 0 OR s.deleteFlag IS NULL)", ['a' => GeneralConstant::APPROVED]);
$soPending = max($soTotal - $soApproved, 0);
$opps = (int) $scalar("SELECT COUNT(o) FROM ApplicationBundle:Opportunity o WHERE (o.deleteFlag = 0 OR o.deleteFlag IS NULL)");
$clients = (int) $scalar("SELECT COUNT(c) FROM ApplicationBundle:AccClients c");
// ── 6-month order-value trend (guarded raw SQL — flat/empty on any quirk) ──
$monthKeys = [];
$monthLabels = [];
for ($i = 5; $i >= 0; $i--) {
$m = new \DateTime('first day of this month');
if ($i > 0) {
$m->modify('-' . $i . ' month');
}
$monthKeys[] = $m->format('Y-m');
$monthLabels[] = $m->format('M');
}
$trend = function ($sql) use ($em) {
try {
return $em->getConnection()->fetchAllAssociative($sql);
} catch (\Throwable $e) {
return [];
}
};
$seriesFromRows = function (array $rows, array $keys) {
$map = [];
foreach ($rows as $r) {
$map[$r['month']] = (float) $r['total'];
}
$out = [];
foreach ($keys as $k) {
$out[] = isset($map[$k]) ? round($map[$k], 2) : 0;
}
return $out;
};
$orderRows = $trend("
SELECT DATE_FORMAT(sales_order_date, '%Y-%m') AS month,
COALESCE(SUM(CAST(COALESCE(NULLIF(so_amount,''),0) AS DECIMAL(15,2))),0) AS total
FROM sales_order
WHERE status = 1 AND (delete_flag = 0 OR delete_flag IS NULL)
AND sales_order_date >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
GROUP BY DATE_FORMAT(sales_order_date, '%Y-%m') ORDER BY month ASC");
$approvedRows = $trend("
SELECT DATE_FORMAT(sales_order_date, '%Y-%m') AS month,
COALESCE(SUM(CAST(COALESCE(NULLIF(so_amount,''),0) AS DECIMAL(15,2))),0) AS total
FROM sales_order
WHERE status = 1 AND approved = 1 AND (delete_flag = 0 OR delete_flag IS NULL)
AND sales_order_date >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
GROUP BY DATE_FORMAT(sales_order_date, '%Y-%m') ORDER BY month ASC");
$coverage = $soTotal > 0 ? round(($soApproved / $soTotal) * 100, 1) : 0;
// ── Intelligent helper — surface the most useful next action ──
if ($soPending > 0) {
$salesHelper = ['type' => 'warn', 'text' => $soPending . ' sales order' . ($soPending == 1 ? '' : 's') . ' awaiting approval — sign off to release delivery.'];
} elseif ($opps > 0) {
$salesHelper = ['type' => 'ok', 'text' => $opps . ' opportunit' . ($opps == 1 ? 'y' : 'ies') . ' in the pipeline and nothing stuck in approval — keep them moving.'];
} else {
$salesHelper = null;
}
return [
'helper' => $salesHelper,
'kpis' => [
['label' => 'Sales Orders', 'value' => $soTotal, 'note' => 'Active orders'],
['label' => 'Approved Value', 'value' => round($soValue), 'note' => 'Approved SO value'],
['label' => 'Opportunities', 'value' => $opps, 'note' => 'In the pipeline'],
['label' => 'Clients', 'value' => $clients, 'note' => 'Customer base'],
['label' => 'Pending Approval', 'value' => $soPending, 'note' => 'Awaiting sign-off'],
],
'pending_items' => [
['label' => 'Sales Orders', 'value' => $soPending, 'note' => 'Waiting for approval'],
],
'hero' => [
'labels' => $monthLabels,
'series' => [
['label' => 'Order Value', 'values' => $seriesFromRows($orderRows, $monthKeys), 'color' => '#2b5fb8', 'fill' => 'rgba(43,95,184,0.14)'],
['label' => 'Approved', 'values' => $seriesFromRows($approvedRows, $monthKeys), 'color' => '#4BAA6B', 'fill' => 'rgba(75,170,107,0.14)'],
],
],
'hero_summary' => [
'subhead' => 'Order value over the last 6 months',
],
'snapshot' => [
'label' => 'Sales Health',
'ring_value' => $coverage,
'ring_note' => 'Approved of all orders',
'metrics' => [
['label' => 'Approved Value', 'value' => number_format(round($soValue)), 'note' => 'Signed-off SO value'],
['label' => 'Pending Orders', 'value' => $soPending, 'note' => 'Awaiting approval'],
['label' => 'Opportunities', 'value' => $opps, 'note' => 'Open pipeline'],
],
],
];
}
public function indexPurchaseAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$companyId = $this->getLoggedUserCompanyId($request);
$dashboardDataForUser = $this->loadDashboardWidgetsForUser($request);
$dashboardAnalytics = $this->buildPurchaseDashboardAnalytics($em, $companyId);
return $this->render(
'@Purchase/pages/supply_chain_dashboard.html.twig',
array(
'page_title' => 'Purchasing Dashboard',
'dashboardMode' => 'purchase',
'dashboardDataForUser' => $dashboardDataForUser,
'dashboardAnalytics' => $dashboardAnalytics
)
);
}
public function indexDistributionAction(Request $request)
{
$session = $request->getSession();
$dashboardDataForUser = [];
$em = $this->getDoctrine()->getManager();
$companyId = $this->getLoggedUserCompanyId($request);
$userId = $request->getSession()->get(UserConstants::USER_ID);
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
'userId' => $userId,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
if (empty($dbQuery)) ///now get the defaults
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
// 'userId'=>$userId,
'defaultBoxFlag' => 1,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
foreach ($dbQuery as $entry) {
$dashboardDataForUser[] = array(
'id' => $entry->getId(),
'widgetId' => $entry->getId(),
'widgetSequence' => $entry->getSequence(),
'widgetStatus' => $entry->getStatus(),
'widgetData' => json_decode($entry->getData(), true),
'defaultBoxFlag' => $entry->getDefaultBoxFlag(),
'widgetSizeTiny' => $entry->getSizeTiny(),
'widgetSizeSmall' => $entry->getSizeSmall(),
'widgetSizeMedium' => $entry->getSizeMedium(),
'widgetSizeLarge' => $entry->getSizeLarge(),
'refreshInterval' => $entry->getRefreshInterval(),
);
}
return $this->render(
'@Distribution/pages/distribution_dashboard.html.twig',
array(
'page_title' => 'Distribution Dashboard',
'dashboardDataForUser' => $dashboardDataForUser,
'dashboardAnalytics' => $this->buildDistributionDashboardAnalytics($em)
)
);
}
/**
* Lean, fail-safe analytics for the Distribution cp-shell dashboard. Guarded scalar DQL —
* a schema/tenant quirk degrades to 0 rather than 500-ing the dashboard.
*/
private function buildDistributionDashboardAnalytics($em)
{
$scalar = function ($dql, $params = []) use ($em) {
try {
$v = $em->createQuery($dql)->setParameters($params)->getSingleScalarResult();
return $v === null ? 0 : $v;
} catch (\Throwable $e) {
return 0;
}
};
$doTotal = (int) $scalar("SELECT COUNT(d) FROM ApplicationBundle:DeliveryOrder d WHERE (d.deleteFlag = 0 OR d.deleteFlag IS NULL)");
$doApproved = (int) $scalar("SELECT COUNT(d) FROM ApplicationBundle:DeliveryOrder d WHERE d.approved = :a AND (d.deleteFlag = 0 OR d.deleteFlag IS NULL)", ['a' => GeneralConstant::APPROVED]);
$doPending = max($doTotal - $doApproved, 0);
$confirms = (int) $scalar("SELECT COUNT(c) FROM ApplicationBundle:DeliveryConfirmation c");
$receipts = (int) $scalar("SELECT COUNT(r) FROM ApplicationBundle:DeliveryReceipt r");
$challans = (int) $scalar("SELECT COUNT(s) FROM ApplicationBundle:ServiceChallan s");
return [
'kpis' => [
['label' => 'Delivery Orders', 'value' => $doTotal, 'note' => 'All dispatch orders'],
['label' => 'Confirmations', 'value' => $confirms, 'note' => 'Delivery confirmed'],
['label' => 'Delivery Receipts', 'value' => $receipts, 'note' => 'Received by customer'],
['label' => 'Service Challans', 'value' => $challans, 'note' => 'Dispatch challans'],
['label' => 'Pending Dispatch', 'value' => $doPending, 'note' => 'Awaiting sign-off'],
],
'pending_items' => [
['label' => 'Delivery Orders', 'value' => $doPending, 'note' => 'Waiting for approval'],
],
];
}
public function indexProjectsAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
return $this->render(
'@Project/pages/projects_dashboard.html.twig',
array(
'page_title' => 'Projects Dashboard',
'dashboardAnalytics' => $this->buildProjectsDashboardAnalytics($em),
)
);
}
/**
* Lean, fail-safe analytics for the Projects cp-shell home. Guarded scalar DQL — a
* schema/tenant quirk degrades each metric to 0 rather than 500-ing the landing page.
* Project.deleteFlag is nullable-with-no-default, so every count uses (=0 OR IS NULL).
*/
private function buildProjectsDashboardAnalytics($em)
{
$scalar = function ($dql, $params = []) use ($em) {
try {
$v = $em->createQuery($dql)->setParameters($params)->getSingleScalarResult();
return $v === null ? 0 : $v;
} catch (\Throwable $e) {
return 0;
}
};
$list = function ($dql, $params = [], $max = 6) use ($em) {
try {
$q = $em->createQuery($dql)->setParameters($params)->setMaxResults($max);
return $q->getArrayResult();
} catch (\Throwable $e) { return []; }
};
$today = date('Y-m-d');
$total = (int) $scalar("SELECT COUNT(p) FROM ApplicationBundle:Project p WHERE (p.deleteFlag = 0 OR p.deleteFlag IS NULL)");
$active = (int) $scalar("SELECT COUNT(p) FROM ApplicationBundle:Project p WHERE p.status = :s AND (p.deleteFlag = 0 OR p.deleteFlag IS NULL)", ['s' => GeneralConstant::ACTIVE]);
$baselines = (int) $scalar("SELECT COUNT(b) FROM ApplicationBundle:ProjectConfigBaseline b");
// ── Project confirmation status mix (0/null budgeting, 2 confirmed-not-impacted, 1 impacted) ──
$budgeting = (int) $scalar("SELECT COUNT(p) FROM ApplicationBundle:Project p WHERE (p.projectConfirmationStatus = 0 OR p.projectConfirmationStatus IS NULL) AND (p.deleteFlag = 0 OR p.deleteFlag IS NULL)");
$confirmed = (int) $scalar("SELECT COUNT(p) FROM ApplicationBundle:Project p WHERE p.projectConfirmationStatus = 2 AND (p.deleteFlag = 0 OR p.deleteFlag IS NULL)");
$impacted = (int) $scalar("SELECT COUNT(p) FROM ApplicationBundle:Project p WHERE p.projectConfirmationStatus = 1 AND (p.deleteFlag = 0 OR p.deleteFlag IS NULL)");
// ── Milestone health ──
$msTotal = (int) $scalar("SELECT COUNT(m) FROM ApplicationBundle:ProjectMilestone m");
$msDone = (int) $scalar("SELECT COUNT(m) FROM ApplicationBundle:ProjectMilestone m WHERE m.actualDate IS NOT NULL");
$msOverdue = (int) $scalar("SELECT COUNT(m) FROM ApplicationBundle:ProjectMilestone m WHERE m.actualDate IS NULL AND m.plannedDate IS NOT NULL AND m.plannedDate < :t", ['t' => $today]);
$msUpcoming = (int) $scalar("SELECT COUNT(m) FROM ApplicationBundle:ProjectMilestone m WHERE m.actualDate IS NULL AND m.plannedDate >= :t", ['t' => $today]);
$msBilling = (float) $scalar("SELECT COALESCE(SUM(m.billingAmount),0) FROM ApplicationBundle:ProjectMilestone m");
$msInvoiced = (float) $scalar("SELECT COALESCE(SUM(m.billingAmount),0) FROM ApplicationBundle:ProjectMilestone m WHERE m.invoicedDate IS NOT NULL");
$pctComplete = $msTotal > 0 ? round($msDone / $msTotal * 100) : 0;
// ── Upcoming milestones (not yet done, soonest first) ──
$upRows = $list("SELECT m.milestoneName AS name, m.plannedDate AS planned, m.billingAmount AS amount
FROM ApplicationBundle:ProjectMilestone m
WHERE m.actualDate IS NULL AND m.plannedDate >= :t
ORDER BY m.plannedDate ASC", ['t' => $today], 5);
$upcoming = [];
foreach ($upRows as $r) {
$upcoming[] = [
'name' => $r['name'] ?: 'Milestone',
'date' => $r['planned'] instanceof \DateTimeInterface ? $r['planned']->format('Y-m-d') : (is_string($r['planned']) ? substr($r['planned'], 0, 10) : ''),
'amount' => number_format((float) ($r['amount'] ?? 0), 2, '.', ','),
];
}
// ── CoPilot surface counts ──
$surfaces = [
['label' => 'Requirements', 'value' => (int) $scalar("SELECT COUNT(r) FROM ApplicationBundle:ProjectRequirement r"), 'icon' => 'checklist', 'route' => 'project_requirement_list'],
['label' => 'Devices', 'value' => (int) $scalar("SELECT COUNT(d) FROM ApplicationBundle:ProjectDevice d"), 'icon' => 'memory', 'route' => 'project_device_list'],
['label' => 'Interfaces', 'value' => (int) $scalar("SELECT COUNT(i) FROM ApplicationBundle:ProjectInterface i"), 'icon' => 'cable', 'route' => 'project_interface_list'],
['label' => 'Config Baselines','value' => $baselines, 'icon' => 'layers', 'route' => 'project_config_baseline_list'],
];
// ── Sales target vs achievement (current year) — fail-safe ──
$tva = null;
try {
$sts = new \ApplicationBundle\Modules\ProjectSalesReport\Service\SalesTargetService($em);
$year = date('Y');
$perf = $sts->fprPerformance(\ApplicationBundle\Modules\ProjectSalesReport\Support\SalesTargetCore::YEARLY, $year);
$tgt = (float) $perf['totals']['target'];
$ach = (float) $perf['totals']['achieved'];
$tva = [
'period' => $year,
'target' => number_format($tgt, 0, '.', ','),
'achieved' => number_format($ach, 0, '.', ','),
'pct' => $perf['totals']['achievement_pct'], // null when no target set
'bar' => $tgt > 0 ? min(100, round($ach / $tgt * 100)) : 0,
'has_target' => $tgt > 0,
];
} catch (\Throwable $e) { $tva = null; }
// ── Smart helper ──
if ($msOverdue > 0) {
$helper = ['type' => 'warn', 'text' => $msOverdue . ' milestone' . ($msOverdue == 1 ? '' : 's') . ' overdue — the planned date has passed with no completion recorded. Review delivery on these first.'];
} elseif ($msUpcoming > 0) {
$helper = ['type' => 'ok', 'text' => $msUpcoming . ' milestone' . ($msUpcoming == 1 ? '' : 's') . ' coming up and nothing overdue — delivery is on track.'];
} else {
$helper = null;
}
return [
'kpis' => [
['label' => 'Total Projects', 'value' => $total, 'note' => 'All non-deleted projects'],
['label' => 'Active', 'value' => $active, 'note' => 'Currently active'],
['label' => 'Milestones Due', 'value' => $msUpcoming, 'note' => 'Upcoming, not yet done'],
['label' => 'Overdue', 'value' => $msOverdue, 'note' => 'Past planned date'],
],
'status_mix' => [
['label' => 'Budgeting', 'value' => $budgeting, 'color' => '#c98a00'],
['label' => 'Confirmed', 'value' => $confirmed, 'color' => '#1976d2'],
['label' => 'Impacted', 'value' => $impacted, 'color' => '#2e7d32'],
],
'status_total' => $budgeting + $confirmed + $impacted,
'milestones' => [
'total' => $msTotal, 'done' => $msDone, 'overdue' => $msOverdue, 'upcoming' => $msUpcoming,
'pct_complete' => $pctComplete,
'billing_total' => number_format($msBilling, 2, '.', ','),
'invoiced_total' => number_format($msInvoiced, 2, '.', ','),
'outstanding_total' => number_format(max(0, $msBilling - $msInvoiced), 2, '.', ','),
],
'surfaces' => $surfaces,
'upcoming' => $upcoming,
'target_vs_achievement' => $tva,
'helper' => $helper,
'pending_items' => [
['label' => 'Open Projects', 'value' => $active, 'note' => 'Active delivery'],
['label' => 'Overdue Milestones', 'value' => $msOverdue, 'note' => 'Past planned date'],
['label' => 'Due Soon', 'value' => $msUpcoming,'note' => 'Upcoming milestones'],
],
];
}
public function indexProductionAction(Request $request)
{
$session = $request->getSession();
$dashboardDataForUser = [];
$em = $this->getDoctrine()->getManager();
$companyId = $this->getLoggedUserCompanyId($request);
$userId = $request->getSession()->get(UserConstants::USER_ID);
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
'userId' => $userId,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
if (empty($dbQuery)) ///now get the defaults
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
// 'userId'=>$userId,
'defaultBoxFlag' => 1,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
foreach ($dbQuery as $entry) {
$dashboardDataForUser[] = array(
'id' => $entry->getId(),
'widgetId' => $entry->getId(),
'widgetSequence' => $entry->getSequence(),
'widgetStatus' => $entry->getStatus(),
'widgetData' => json_decode($entry->getData(), true),
'defaultBoxFlag' => $entry->getDefaultBoxFlag(),
'widgetSizeTiny' => $entry->getSizeTiny(),
'widgetSizeSmall' => $entry->getSizeSmall(),
'widgetSizeMedium' => $entry->getSizeMedium(),
'widgetSizeLarge' => $entry->getSizeLarge(),
'refreshInterval' => $entry->getRefreshInterval(),
);
}
return $this->render(
'@Production/pages/production_dashboard.html.twig',
array(
'page_title' => 'Production Dashboard',
'dashboardDataForUser' => $dashboardDataForUser,
'dashboardAnalytics' => $this->buildProductionDashboardAnalytics($em),
)
);
}
/**
* Lean, fail-safe analytics for the Production cp-shell dashboard. Guarded scalar DQL —
* a missing entity/column degrades to 0 rather than 500-ing the page.
*/
private function buildProductionDashboardAnalytics($em)
{
$scalar = function ($dql) use ($em) {
try {
$v = $em->createQuery($dql)->getSingleScalarResult();
return $v === null ? 0 : $v;
} catch (\Throwable $e) {
return 0;
}
};
$entries = (int) $scalar("SELECT COUNT(p) FROM ApplicationBundle:Production p");
$boms = (int) $scalar("SELECT COUNT(b) FROM ApplicationBundle:ProductionBom b");
$schedules = (int) $scalar("SELECT COUNT(s) FROM ApplicationBundle:ProductionSchedule s");
$lines = (int) $scalar("SELECT COUNT(l) FROM ApplicationBundle:ProductionLine l");
return [
'kpis' => [
['label' => 'Production Entries', 'value' => $entries, 'note' => 'All production runs'],
['label' => 'Bills of Material', 'value' => $boms, 'note' => 'Defined BOMs'],
['label' => 'Schedules', 'value' => $schedules, 'note' => 'Planned runs'],
['label' => 'Production Lines', 'value' => $lines, 'note' => 'Configured lines'],
],
'pending_items' => [],
];
}
public function indexInventoryAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$companyId = $this->getLoggedUserCompanyId($request);
$dashboardDataForUser = $this->loadDashboardWidgetsForUser($request);
$dashboardAnalytics = $this->buildInventoryDashboardAnalytics($em, $companyId);
return $this->render(
'@Inventory/pages/inventory_dashboard.html.twig',
array(
'page_title' => 'Inventory Dashboard',
'dashboardMode' => 'inventory',
'dashboardDataForUser' => $dashboardDataForUser,
'dashboardAnalytics' => $dashboardAnalytics
)
);
}
private function loadDashboardWidgetsForUser(Request $request)
{
$dashboardDataForUser = [];
$em = $this->getDoctrine()->getManager();
$companyId = $this->getLoggedUserCompanyId($request);
$userId = $request->getSession()->get(UserConstants::USER_ID);
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
'userId' => $userId,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
if (empty($dbQuery)) {
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
'defaultBoxFlag' => 1,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
}
foreach ($dbQuery as $entry) {
$dashboardDataForUser[] = array(
'id' => $entry->getId(),
'widgetId' => $entry->getId(),
'widgetSequence' => $entry->getSequence(),
'widgetStatus' => $entry->getStatus(),
'widgetData' => json_decode($entry->getData(), true),
'defaultBoxFlag' => $entry->getDefaultBoxFlag(),
'widgetSizeTiny' => $entry->getSizeTiny(),
'widgetSizeSmall' => $entry->getSizeSmall(),
'widgetSizeMedium' => $entry->getSizeMedium(),
'widgetSizeLarge' => $entry->getSizeLarge(),
'refreshInterval' => $entry->getRefreshInterval(),
);
}
return $dashboardDataForUser;
}
private function buildPurchaseDashboardAnalytics($em, $companyId)
{
$conn = $em->getConnection();
$monthKeys = [];
$monthLabels = [];
for ($i = 5; $i >= 0; $i--) {
$month = new \DateTime('first day of this month');
if ($i > 0) {
$month->modify('-' . $i . ' month');
}
$monthKeys[] = $month->format('Y-m');
$monthLabels[] = $month->format('M');
}
$poTrendRows = $conn->fetchAllAssociative("
SELECT DATE_FORMAT(purchase_order_date, '%Y-%m') AS month, COALESCE(SUM(CAST(COALESCE(NULLIF(po_amount, ''), 0) AS DECIMAL(15,2))), 0) AS total
FROM purchase_order
WHERE company_id = :companyId
AND status = 1
AND approved = 1
AND purchase_order_date >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
GROUP BY DATE_FORMAT(purchase_order_date, '%Y-%m')
ORDER BY month ASC
", ['companyId' => $companyId]);
$invoiceTrendRows = $conn->fetchAllAssociative("
SELECT DATE_FORMAT(purchase_invoice_date, '%Y-%m') AS month, COALESCE(SUM(CAST(COALESCE(NULLIF(invoice_amount, ''), 0) AS DECIMAL(15,2))), 0) AS total
FROM purchase_invoice
WHERE company_id = :companyId
AND status = 1
AND approved = 1
AND purchase_invoice_date >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
GROUP BY DATE_FORMAT(purchase_invoice_date, '%Y-%m')
ORDER BY month ASC
", ['companyId' => $companyId]);
$paidTrendRows = $conn->fetchAllAssociative("
SELECT DATE_FORMAT(purchase_invoice_date, '%Y-%m') AS month, COALESCE(SUM(CAST(COALESCE(NULLIF(paid_amount, ''), invoice_amount, 0) AS DECIMAL(15,2))), 0) AS total
FROM purchase_invoice
WHERE company_id = :companyId
AND status = 1
AND approved = 1
AND purchase_invoice_date >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
GROUP BY DATE_FORMAT(purchase_invoice_date, '%Y-%m')
ORDER BY month ASC
", ['companyId' => $companyId]);
$requisitionTrendRows = $conn->fetchAllAssociative("
SELECT DATE_FORMAT(purchase_requisition_date, '%Y-%m') AS month, COUNT(*) AS total
FROM purchase_requisition
WHERE company_id = :companyId
AND status = 1
AND purchase_requisition_date >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
GROUP BY DATE_FORMAT(purchase_requisition_date, '%Y-%m')
ORDER BY month ASC
", ['companyId' => $companyId]);
$seriesFromRows = function (array $rows, array $monthKeys) {
$series = array_fill_keys($monthKeys, 0);
foreach ($rows as $row) {
$month = $row['month'] ?? null;
if ($month !== null && array_key_exists($month, $series)) {
$series[$month] = (float) $row['total'];
}
}
return array_values($series);
};
$purchaseOrderTotal = (float) $conn->fetchOne("
SELECT COALESCE(SUM(CAST(COALESCE(NULLIF(po_amount, ''), 0) AS DECIMAL(15,2))), 0)
FROM purchase_order
WHERE company_id = :companyId AND status = 1 AND approved = 1
", ['companyId' => $companyId]);
$purchaseInvoiceTotal = (float) $conn->fetchOne("
SELECT COALESCE(SUM(CAST(COALESCE(NULLIF(invoice_amount, ''), 0) AS DECIMAL(15,2))), 0)
FROM purchase_invoice
WHERE company_id = :companyId AND status = 1 AND approved = 1
", ['companyId' => $companyId]);
$purchasePaidTotal = (float) $conn->fetchOne("
SELECT COALESCE(SUM(CAST(COALESCE(NULLIF(paid_amount, ''), invoice_amount, 0) AS DECIMAL(15,2))), 0)
FROM purchase_invoice
WHERE company_id = :companyId AND status = 1 AND approved = 1
", ['companyId' => $companyId]);
$purchaseRequisitionCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM purchase_requisition
WHERE company_id = :companyId AND status = 1
", ['companyId' => $companyId]);
$purchaseOrderCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM purchase_order
WHERE company_id = :companyId AND status = 1 AND approved = 1
", ['companyId' => $companyId]);
$purchaseInvoiceCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM purchase_invoice
WHERE company_id = :companyId AND status = 1 AND approved = 1
", ['companyId' => $companyId]);
$pendingPurchaseRequisitionCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM purchase_requisition
WHERE company_id = :companyId AND status = 1 AND COALESCE(approved, 0) = 0
", ['companyId' => $companyId]);
$pendingPurchaseOrderCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM purchase_order
WHERE company_id = :companyId AND status = 1 AND COALESCE(approved, 0) = 0
", ['companyId' => $companyId]);
$pendingPurchaseInvoiceCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM purchase_invoice
WHERE company_id = :companyId AND status = 1 AND COALESCE(approved, 0) = 0
", ['companyId' => $companyId]);
$supplierCount = (int) $conn->fetchOne("
SELECT COUNT(DISTINCT supplier_id)
FROM purchase_order
WHERE company_id = :companyId AND status = 1 AND approved = 1
", ['companyId' => $companyId]);
$topSuppliers = $conn->fetchAllAssociative("
SELECT po.supplier_id, s.supplier_name, COALESCE(SUM(CAST(COALESCE(NULLIF(po.po_amount, ''), 0) AS DECIMAL(15,2))), 0) AS total
FROM purchase_order po
LEFT JOIN acc_suppliers s ON s.supplier_id = po.supplier_id
WHERE po.company_id = :companyId AND po.status = 1 AND po.approved = 1
GROUP BY po.supplier_id, s.supplier_name
ORDER BY total DESC
LIMIT 5
", ['companyId' => $companyId]);
$topSupplierTotal = 0;
foreach ($topSuppliers as $row) {
$topSupplierTotal += (float) $row['total'];
}
$funnelBase = max($purchaseRequisitionCount, $purchaseOrderCount, $purchaseInvoiceCount, 1);
$paidCoverage = $purchaseInvoiceTotal > 0 ? ($purchasePaidTotal / $purchaseInvoiceTotal) * 100 : 0;
return array(
'kpis' => array(
array('label' => 'Purchase Orders', 'value' => $purchaseOrderCount, 'note' => 'Approved and active', 'icon' => 'shopping-cart', 'color' => 'blue'),
array('label' => 'Invoice Value', 'value' => $purchaseInvoiceTotal, 'note' => 'Billed through the period', 'icon' => 'file-text', 'color' => 'green'),
array('label' => 'Paid Value', 'value' => $purchasePaidTotal, 'note' => 'Settled to suppliers', 'icon' => 'check-circle', 'color' => 'teal'),
array('label' => 'Outstanding Due', 'value' => max($purchaseInvoiceTotal - $purchasePaidTotal, 0), 'note' => 'Open supplier liability', 'icon' => 'hourglass-half', 'color' => 'red'),
array('label' => 'Suppliers', 'value' => $supplierCount, 'note' => 'Active vendor base', 'icon' => 'building', 'color' => 'amber'),
array('label' => 'Open Requisitions', 'value' => $pendingPurchaseRequisitionCount, 'note' => 'Waiting for movement', 'icon' => 'tasks', 'color' => 'slate'),
),
'pending_total' => $pendingPurchaseRequisitionCount + $pendingPurchaseOrderCount + $pendingPurchaseInvoiceCount,
'pending_items' => array(
array('label' => 'Requisitions', 'value' => $pendingPurchaseRequisitionCount, 'note' => 'Needs buy-side review'),
array('label' => 'Purchase Orders', 'value' => $pendingPurchaseOrderCount, 'note' => 'Waiting for approval'),
array('label' => 'Purchase Invoices', 'value' => $pendingPurchaseInvoiceCount, 'note' => 'Pending matching'),
),
'hero' => array(
'labels' => $monthLabels,
'series' => array(
array('label' => 'PO Value', 'values' => $seriesFromRows($poTrendRows, $monthKeys), 'color' => '#3D7DB8', 'fill' => 'rgba(61,125,184,0.14)'),
array('label' => 'Invoice Value', 'values' => $seriesFromRows($invoiceTrendRows, $monthKeys), 'color' => '#4BAA6B', 'fill' => 'rgba(75,170,107,0.14)'),
array('label' => 'Paid Value', 'values' => $seriesFromRows($paidTrendRows, $monthKeys), 'color' => '#2F9E91', 'fill' => 'rgba(47,158,145,0.14)'),
),
),
'hero_summary' => array(
'headline' => $purchaseInvoiceTotal > 0 ? round($paidCoverage, 1) . '%' : '0%',
'headline_label' => 'Payment coverage',
'subhead' => 'Paid ' . round($paidCoverage, 1) . '% of billed value',
),
'snapshot' => array(
'label' => 'Procurement Health',
'ring_value' => round($paidCoverage, 1),
'ring_note' => 'Invoice coverage',
'metrics' => array(
array('label' => 'Requisition to PO', 'value' => $purchaseRequisitionCount > 0 ? round(($purchaseOrderCount / $purchaseRequisitionCount) * 100, 1) . '%' : '0%', 'note' => 'Flow conversion'),
array('label' => 'Average PO Size', 'value' => $purchaseOrderCount > 0 ? number_format($purchaseOrderTotal / $purchaseOrderCount, 0, '.', ',') : '0', 'note' => 'Approved order value'),
array('label' => 'Due Ratio', 'value' => $purchaseInvoiceTotal > 0 ? round((max($purchaseInvoiceTotal - $purchasePaidTotal, 0) / $purchaseInvoiceTotal) * 100, 1) . '%' : '0%', 'note' => 'Open liability share'),
array('label' => 'Supplier Concentration', 'value' => $purchaseOrderTotal > 0 ? round(($topSupplierTotal / $purchaseOrderTotal) * 100, 1) . '%' : '0%', 'note' => 'Top-5 spend share'),
),
),
'lower_cards' => array(
array(
'title' => 'Pipeline Atlas',
'subtitle' => 'Requisition to payment flow',
'type' => 'pipeline',
'items' => array(
array('label' => 'Requisitions', 'value' => $purchaseRequisitionCount, 'note' => 'Planning demand'),
array('label' => 'Purchase Orders', 'value' => $purchaseOrderCount, 'note' => 'Committed spend'),
array('label' => 'Purchase Invoices', 'value' => $purchaseInvoiceCount, 'note' => 'Vendor billing'),
array('label' => 'Settled', 'value' => (int) $conn->fetchOne("SELECT COUNT(*) FROM purchase_invoice WHERE company_id = :companyId AND status = 1 AND approved = 1 AND CAST(COALESCE(NULLIF(paid_amount, ''), 0) AS DECIMAL(15,2)) > 0", ['companyId' => $companyId]), 'note' => 'Cash released'),
),
'base' => $funnelBase,
),
array(
'title' => 'Supplier Mix',
'subtitle' => 'Concentration by approved spend',
'type' => 'bars',
'items' => array_map(function ($row) use ($topSupplierTotal) {
$total = (float) $row['total'];
return array(
'label' => $row['supplier_name'] ?: ('Supplier #' . $row['supplier_id']),
'value' => $total,
'bar' => $topSupplierTotal > 0 ? round(($total / $topSupplierTotal) * 100, 1) : 0,
'note' => 'Approved spend share',
);
}, $topSuppliers),
),
array(
'title' => 'Spend Lens',
'subtitle' => 'Analytical ratios that matter',
'type' => 'ratios',
'items' => array(
array('label' => 'Payment Coverage', 'value' => round($paidCoverage, 1) . '%', 'bar' => $paidCoverage, 'note' => 'Cash settled vs billed'),
array('label' => 'Requisition Coverage', 'value' => $purchaseRequisitionCount > 0 ? round(($purchaseOrderCount / $purchaseRequisitionCount) * 100, 1) . '%' : '0%', 'bar' => $purchaseRequisitionCount > 0 ? (($purchaseOrderCount / $purchaseRequisitionCount) * 100) : 0, 'note' => 'POs created from requests'),
array('label' => 'Open Due', 'value' => number_format(max($purchaseInvoiceTotal - $purchasePaidTotal, 0), 0, '.', ','), 'bar' => $purchaseInvoiceTotal > 0 ? ((max($purchaseInvoiceTotal - $purchasePaidTotal, 0) / $purchaseInvoiceTotal) * 100) : 0, 'note' => 'Balance still outstanding'),
),
),
),
'months' => $monthLabels
);
}
private function buildInventoryDashboardAnalytics($em, $companyId)
{
$conn = $em->getConnection();
$monthKeys = [];
$monthLabels = [];
for ($i = 5; $i >= 0; $i--) {
$month = new \DateTime('first day of this month');
if ($i > 0) {
$month->modify('-' . $i . ' month');
}
$monthKeys[] = $month->format('Y-m');
$monthLabels[] = $month->format('M');
}
$receivedRows = $conn->fetchAllAssociative("
SELECT DATE_FORMAT(stock_received_note_date, '%Y-%m') AS month, COUNT(*) AS total
FROM stock_received_note
WHERE company_id = :companyId
AND status = 1
AND approved = 1
AND stock_received_note_date >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
GROUP BY DATE_FORMAT(stock_received_note_date, '%Y-%m')
ORDER BY month ASC
", ['companyId' => $companyId]);
$transferRows = $conn->fetchAllAssociative("
SELECT DATE_FORMAT(stock_transfer_date, '%Y-%m') AS month, COUNT(*) AS total
FROM stock_transfer
WHERE company_id = :companyId
AND status = 1
AND approved = 1
AND stock_transfer_date >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
GROUP BY DATE_FORMAT(stock_transfer_date, '%Y-%m')
ORDER BY month ASC
", ['companyId' => $companyId]);
$consumptionRows = $conn->fetchAllAssociative("
SELECT DATE_FORMAT(stock_consumption_note_date, '%Y-%m') AS month, COUNT(*) AS total
FROM stock_consumption_note
WHERE company_id = :companyId
AND status = 1
AND approved = 1
AND stock_consumption_note_date >= DATE_SUB(CURDATE(), INTERVAL 5 MONTH)
GROUP BY DATE_FORMAT(stock_consumption_note_date, '%Y-%m')
ORDER BY month ASC
", ['companyId' => $companyId]);
$seriesFromRows = function (array $rows, array $monthKeys) {
$series = array_fill_keys($monthKeys, 0);
foreach ($rows as $row) {
$month = $row['month'] ?? null;
if ($month !== null && array_key_exists($month, $series)) {
$series[$month] = (float) $row['total'];
}
}
return array_values($series);
};
$skuCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM inv_products
WHERE company_id = :companyId AND status = 1
", ['companyId' => $companyId]);
$totalUnits = (float) $conn->fetchOne("
SELECT COALESCE(SUM(CAST(COALESCE(NULLIF(qty, ''), 0) AS DECIMAL(15,2))), 0)
FROM inv_products
WHERE company_id = :companyId AND status = 1
", ['companyId' => $companyId]);
$stockValue = (float) $conn->fetchOne("
SELECT COALESCE(SUM(CAST(COALESCE(NULLIF(qty, ''), 0) AS DECIMAL(15,2)) * CAST(COALESCE(NULLIF(curr_purchase_price, ''), 0) AS DECIMAL(15,2))), 0)
FROM inv_products
WHERE company_id = :companyId AND status = 1
", ['companyId' => $companyId]);
$lowStockCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM inv_products
WHERE company_id = :companyId
AND status = 1
AND CAST(COALESCE(NULLIF(qty, ''), 0) AS DECIMAL(15,2)) <= CAST(COALESCE(NULLIF(reorder_level, ''), 0) AS DECIMAL(15,2))
AND CAST(COALESCE(NULLIF(reorder_level, ''), 0) AS DECIMAL(15,2)) > 0
", ['companyId' => $companyId]);
$zeroStockCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM inv_products
WHERE company_id = :companyId AND status = 1 AND CAST(COALESCE(NULLIF(qty, ''), 0) AS DECIMAL(15,2)) <= 0
", ['companyId' => $companyId]);
$overstockCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM inv_products
WHERE company_id = :companyId
AND status = 1
AND CAST(COALESCE(NULLIF(reorder_level, ''), 0) AS DECIMAL(15,2)) > 0
AND CAST(COALESCE(NULLIF(qty, ''), 0) AS DECIMAL(15,2)) > (CAST(COALESCE(NULLIF(reorder_level, ''), 0) AS DECIMAL(15,2)) * 3)
", ['companyId' => $companyId]);
$warehouseCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM warehouse
WHERE company_id = :companyId AND status = 1
", ['companyId' => $companyId]);
$receivedPending = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM stock_received_note
WHERE company_id = :companyId AND status = 1 AND COALESCE(approved, 0) = 0
", ['companyId' => $companyId]);
$transferPending = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM stock_transfer
WHERE company_id = :companyId AND status = 1 AND COALESCE(approved, 0) = 0
", ['companyId' => $companyId]);
$consumptionPending = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM stock_consumption_note
WHERE company_id = :companyId AND status = 1 AND COALESCE(approved, 0) = 0
", ['companyId' => $companyId]);
$receivedCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM stock_received_note
WHERE company_id = :companyId AND status = 1 AND approved = 1
", ['companyId' => $companyId]);
$transferCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM stock_transfer
WHERE company_id = :companyId AND status = 1 AND approved = 1
", ['companyId' => $companyId]);
$consumptionCount = (int) $conn->fetchOne("
SELECT COUNT(*)
FROM stock_consumption_note
WHERE company_id = :companyId AND status = 1 AND approved = 1
", ['companyId' => $companyId]);
$warehouseMap = \ApplicationBundle\Modules\Inventory\Inventory::WarehouseList($em, $companyId);
$warehouseRows = $conn->fetchAllAssociative("
SELECT warehouse_id, COALESCE(SUM(CAST(COALESCE(NULLIF(qty, ''), 0) AS DECIMAL(15,2))), 0) AS total_qty,
COALESCE(SUM(CAST(COALESCE(NULLIF(qty, ''), 0) AS DECIMAL(15,2)) * CAST(COALESCE(NULLIF(curr_purchase_price, ''), 0) AS DECIMAL(15,2))), 0) AS total_value
FROM inventory_storage
WHERE company_id = :companyId
GROUP BY warehouse_id
ORDER BY total_value DESC
LIMIT 5
", ['companyId' => $companyId]);
$productRows = $conn->fetchAllAssociative("
SELECT s.product_id, p.name, p.reorder_level,
COALESCE(SUM(CAST(COALESCE(NULLIF(s.qty, ''), 0) AS DECIMAL(15,2))), 0) AS total_qty,
COALESCE(SUM(CAST(COALESCE(NULLIF(s.qty, ''), 0) AS DECIMAL(15,2)) * CAST(COALESCE(NULLIF(s.curr_purchase_price, ''), 0) AS DECIMAL(15,2))), 0) AS total_value
FROM inventory_storage s
LEFT JOIN inv_products p ON p.id = s.product_id
WHERE s.company_id = :companyId
GROUP BY s.product_id, p.name, p.reorder_level
ORDER BY total_value DESC
LIMIT 5
", ['companyId' => $companyId]);
$movementBalance = $receivedCount - $consumptionCount;
$healthIndex = $skuCount > 0 ? max(0, 100 - round(($lowStockCount / $skuCount) * 100)) : 0;
$reorderPressure = $skuCount > 0 ? round(($lowStockCount / $skuCount) * 100, 1) : 0;
$stockCoverage = $warehouseCount > 0 ? round($totalUnits / $warehouseCount, 1) : 0;
$topWarehouseTotal = 0;
foreach ($warehouseRows as $row) {
$topWarehouseTotal += (float) $row['total_value'];
}
$topProductTotal = 0;
foreach ($productRows as $row) {
$topProductTotal += (float) $row['total_value'];
}
return array(
'kpis' => array(
array('label' => 'SKUs', 'value' => $skuCount, 'note' => 'Active item master', 'icon' => 'cube', 'color' => 'blue'),
array('label' => 'On-hand Units', 'value' => $totalUnits, 'note' => 'Current quantity snapshot', 'icon' => 'cubes', 'color' => 'green'),
array('label' => 'Stock Value', 'value' => $stockValue, 'note' => 'Book value at purchase cost', 'icon' => 'database', 'color' => 'teal'),
array('label' => 'Low Stock SKUs', 'value' => $lowStockCount, 'note' => 'At or below reorder point', 'icon' => 'exclamation-triangle', 'color' => 'red'),
array('label' => 'Warehouses', 'value' => $warehouseCount, 'note' => 'Active storage nodes', 'icon' => 'warehouse', 'color' => 'amber'),
array('label' => 'Pending Docs', 'value' => $receivedPending + $transferPending + $consumptionPending, 'note' => 'Awaiting approval', 'icon' => 'hourglass-half', 'color' => 'slate'),
),
'pending_total' => $receivedPending + $transferPending + $consumptionPending,
'pending_items' => array(
array('label' => 'Stock Received', 'value' => $receivedPending, 'note' => 'Incoming goods'),
array('label' => 'Stock Transfer', 'value' => $transferPending, 'note' => 'Warehouse movement'),
array('label' => 'Consumption Notes', 'value' => $consumptionPending, 'note' => 'Consumption posting'),
),
'hero' => array(
'labels' => $monthLabels,
'series' => array(
array('label' => 'Received', 'values' => $seriesFromRows($receivedRows, $monthKeys), 'color' => '#3D7DB8', 'fill' => 'rgba(61,125,184,0.14)'),
array('label' => 'Transfers', 'values' => $seriesFromRows($transferRows, $monthKeys), 'color' => '#D4A843', 'fill' => 'rgba(212,168,67,0.14)'),
array('label' => 'Consumed', 'values' => $seriesFromRows($consumptionRows, $monthKeys), 'color' => '#C05350', 'fill' => 'rgba(192,83,80,0.14)'),
),
),
'hero_summary' => array(
'headline' => $healthIndex . '%',
'headline_label' => 'Health index',
'subhead' => 'Low stock pressure at ' . $reorderPressure . '%',
),
'snapshot' => array(
'label' => 'Stock Health',
'ring_value' => $healthIndex,
'ring_note' => 'Health index',
'metrics' => array(
array('label' => 'Low Stock Pressure', 'value' => $reorderPressure . '%', 'note' => 'At or under reorder'),
array('label' => 'Movement Balance', 'value' => $movementBalance, 'note' => 'Received minus consumed'),
array('label' => 'Avg Units / Warehouse', 'value' => $stockCoverage, 'note' => 'Distribution breadth'),
array('label' => 'Zero Stock SKUs', 'value' => $zeroStockCount, 'note' => 'Immediate attention'),
),
),
'lower_cards' => array(
array(
'title' => 'Reorder Radar',
'subtitle' => 'What needs intervention now',
'type' => 'radar',
'items' => array(
array('label' => 'Low Stock', 'value' => $lowStockCount, 'bar' => $skuCount > 0 ? (($lowStockCount / $skuCount) * 100) : 0, 'note' => 'At reorder threshold'),
array('label' => 'Zero Stock', 'value' => $zeroStockCount, 'bar' => $skuCount > 0 ? (($zeroStockCount / $skuCount) * 100) : 0, 'note' => 'Unavailable items'),
array('label' => 'Overstock', 'value' => $overstockCount, 'bar' => $skuCount > 0 ? (($overstockCount / $skuCount) * 100) : 0, 'note' => 'Potential capital lock-up'),
array('label' => 'Movement Balance', 'value' => $movementBalance, 'bar' => max(0, min(100, 50 + ($movementBalance * 5))), 'note' => 'Inbound vs consumption'),
),
),
array(
'title' => 'Warehouse Atlas',
'subtitle' => 'Value concentration by store',
'type' => 'bars',
'items' => array_map(function ($row) use ($warehouseMap, $topWarehouseTotal) {
$warehouseId = (int) $row['warehouse_id'];
$warehouseName = isset($warehouseMap[$warehouseId]['name']) ? $warehouseMap[$warehouseId]['name'] : ('Warehouse #' . $warehouseId);
$total = (float) $row['total_value'];
return array(
'label' => $warehouseName,
'value' => $total,
'bar' => $topWarehouseTotal > 0 ? round(($total / $topWarehouseTotal) * 100, 1) : 0,
'note' => 'Stock value share',
);
}, $warehouseRows),
),
array(
'title' => 'Value Lens',
'subtitle' => 'Highest value stock pockets',
'type' => 'products',
'items' => array_map(function ($row) use ($topProductTotal) {
$total = (float) $row['total_value'];
$reorderLevel = isset($row['reorder_level']) ? (float) $row['reorder_level'] : 0;
return array(
'label' => $row['name'] ?: ('SKU #' . $row['product_id']),
'value' => $total,
'bar' => $topProductTotal > 0 ? round(($total / $topProductTotal) * 100, 1) : 0,
'note' => 'Reorder level ' . number_format($reorderLevel, 0, '.', ','),
);
}, $productRows),
),
),
'months' => $monthLabels
);
}
public function indexServiceAction(Request $request)
{
$session = $request->getSession();
$dashboardDataForUser = [];
$em = $this->getDoctrine()->getManager();
$companyId = $this->getLoggedUserCompanyId($request);
$userId = $request->getSession()->get(UserConstants::USER_ID);
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
'userId' => $userId,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
if (empty($dbQuery)) ///now get the defaults
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
// 'userId'=>$userId,
'defaultBoxFlag' => 1,
'status' => GeneralConstant::ACTIVE
),
array(
'sequence' => 'ASC'
)
);
foreach ($dbQuery as $entry) {
$dashboardDataForUser[] = array(
'id' => $entry->getId(),
'widgetId' => $entry->getId(),
'widgetSequence' => $entry->getSequence(),
'widgetStatus' => $entry->getStatus(),
'widgetData' => json_decode($entry->getData(), true),
'defaultBoxFlag' => $entry->getDefaultBoxFlag(),
'widgetSizeTiny' => $entry->getSizeTiny(),
'widgetSizeSmall' => $entry->getSizeSmall(),
'widgetSizeMedium' => $entry->getSizeMedium(),
'widgetSizeLarge' => $entry->getSizeLarge(),
'refreshInterval' => $entry->getRefreshInterval(),
);
}
return $this->render(
'@Application/pages/dashboard/index_sales.html.twig',
array(
'page_title' => 'Sales Dashboard',
'dashboardDataForUser' => $dashboardDataForUser
)
);
}
public function modifyDashboardAction(Request $request)
{
$session = $request->getSession();
$em = $this->getDoctrine()->getManager();
$companyId = $this->getLoggedUserCompanyId($request);
$userId = $request->getSession()->get(UserConstants::USER_ID);
if ($request->isMethod('post')) {
//update dashboard data
if ($request->request->has('widgetId')) {
foreach ($request->request->get('widgetId') as $k => $v) {
if ($v != 0) {
//exists sso edit
$widget = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findOneBy(
array(
'id' => $v,
)
);
} else
$widget = new DashboardWidget();
$widget->setData($request->request->get('widgetData')[$k]);
$widget->setSequence($request->request->get('widgetSequence')[$k]);
$widget->setStatus($request->request->has('widgetStatus') ? $request->request->get('widgetStatus')[$k] : GeneralConstant::ACTIVE);
$widget->setUserId($userId);
$widget->setCompanyId($companyId);
$widget->setDefaultBoxFlag($request->request->has('defaultBoxFlag') ? $request->request->get('defaultBoxFlag')[$k] : 0);
if ($v == 0)
$em->persist($widget);
$em->flush();
}
}
}
$dashboardDataForUser = [];
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
'userId' => $userId,
'status' => GeneralConstant::ACTIVE
)
);
if (empty($dbQuery)) ///now get the defaults
$dbQuery = $em->getRepository("ApplicationBundle\\Entity\\DashboardWidget")->findBy(
array(
'CompanyId' => $companyId,
// 'userId'=>$userId,
'defaultBoxFlag' => 1,
'status' => GeneralConstant::ACTIVE
)
);
foreach ($dbQuery as $entry) {
$dashboardDataForUser[] = array(
'id' => $entry->getId(),
'widgetId' => $entry->getId(),
'widgetSequence' => $entry->getSequence(),
'widgetStatus' => $entry->getStatus(),
'widgetData' => $entry->getData(),
'defaultBoxFlag' => $entry->getDefaultBoxFlag(),
'widgetSizeTiny' => $entry->getSizeTiny(),
'widgetSizeSmall' => $entry->getSizeSmall(),
'widgetSizeMedium' => $entry->getSizeMedium(),
'widgetSizeLarge' => $entry->getSizeLarge(),
'refreshInterval' => $entry->getRefreshInterval(),
);
}
//1st try to see if any exists
return $this->render(
'@Application/pages/dashboard/index_sales.html.twig',
array(
'page_title' => 'Modify Dashboard'
)
);
}
public function ChangeCompanyDashboardAction(Request $request, $id = 1)
{
$session = $request->getSession();
if ($request->query->has('sys_admin_panel'))
return $this->redirectToRoute('system_admin_dashboard');
else {
$session->set(UserConstants::USER_COMPANY_ID, $id);
return $this->redirectToRoute('dashboard');
}
}
public function ChangeLeftPanelDisplayStatusAction(Request $request)
{
$session = $request->getSession();
$curr_status = 1;
if ($session->has('HIDE_LEFT_PANEL'))
$curr_status = $session->get('HIDE_LEFT_PANEL');
if ($curr_status == 1)
$curr_status = 0;
else
$curr_status = 1;
$session->set('HIDE_LEFT_PANEL', $curr_status);
// return $this->redirectToRoute('dashboard');
return new Response($curr_status);
}
public function PermissionDeniedAction(Request $request)
{
$session = $request->getSession();
// Generic::debugMessage($session);
return $this->render(
'@System/pages/permission_denied.html.twig',
array(
'page_title' => 'Permission Denied'
)
);
}
public function MyDocumentsAction(Request $request)
{
$session = $request->getSession();
$em = $this->getDoctrine()->getManager();
/* DATE FILTER — issue register #14. A month token wins over a hand-typed range
(register #27: "i need another filter field where i can select only Month option"),
and an INVALID range is reported rather than silently ignored — a filter that
quietly drops your input is worse than one that says no. */
$period = PeriodFilterCore::resolve(
$request->query->get('month', ''),
$request->query->get('start_date', ''),
$request->query->get('end_date', '')
);
$periodError = $period['ok'] ? '' : $period['reason'];
$data = MiscActions::getDocumentsByUserId(
$em,
$session->get(UserConstants::USER_ID),
$period['ok'] ? $period['start'] : '',
$period['ok'] ? $period['end'] : ''
);
// DOC-001: the view calls path(item.documentViewPathName); if an entity's
// registered view-route name is stale/unregistered, path() throws and the
// whole page 500s the moment a user owns a document of that type. Validate
// each route against the router and fall back to a safe one.
$routes = $this->container->get('router')->getRouteCollection();
if (is_array($data)) {
foreach ($data as $k => $item) {
$rn = isset($item['documentViewPathName']) ? $item['documentViewPathName'] : '';
if ($rn === '' || $routes->get($rn) === null) {
$data[$k]['documentViewPathName'] = 'dashboard';
}
}
}
return $this->render(
'@Application/pages/dashboard/my_documents.html.twig',
array(
'page_title' => 'My Documents',
'data' => $data,
'filter_month' => $request->query->get('month', ''),
'filter_start' => $request->query->get('start_date', ''),
'filter_end' => $request->query->get('end_date', ''),
'filter_label' => $period['ok'] ? $period['label'] : '',
'filter_error' => $periodError,
'month_options' => PeriodFilterCore::recentMonths(date('Y-m-d'), 18),
)
);
}
// public function MyDocumentsListForAppAction(Request $request)
// {
// $session = $request->getSession();
// $em = $this->getDoctrine()->getManager();
// $absoluteUrlList = [];
// foreach (GeneralConstant::$Entity_list_details as $e => $d) {
// if (isset($d['entity_print_route_path_name']))
// $absoluteUrlList[$e] = $this->generateUrl($d['entity_print_route_path_name'], [], UrlGenerator::ABSOLUTE_URL);
// }
// $data = MiscActions::getDocumentsByUserIdForApp($em, $session->get(UserConstants::USER_ID), 1, $absoluteUrlList);
// // Generic::debugMessage($session);
// return new JsonResponse(array(
// 'data' => $data
// ));
// // return $this->render('ApplicationBundle:pages/dashboard:my_documents.html.twig',
// // array(
// // 'page_title' => 'My Documents',
// // 'data' => $data
// // )
// // );
// }
public function MyDocumentsListForAppAction(Request $request)
{
$session = $request->getSession();
$em = $this->getDoctrine()->getManager();
// Build absolute URL list
$absoluteUrlList = [];
foreach (GeneralConstant::$Entity_list_details as $e => $d) {
if (isset($d['entity_print_route_path_name'])) {
$absoluteUrlList[$e] = $this->generateUrl($d['entity_print_route_path_name'], [], UrlGenerator::ABSOLUTE_URL);
}
}
// Extract pagination parameters from the request
$page = $request->query->get('page', '_UNSET_');
$offset = $request->query->get('offset', 0);
$limit = $request->query->get('limit', 10); // default limit is 10
// Call the document list method with pagination
return new JsonResponse(
MiscActions::getDocumentsByUserIdForApp(
$em,
$session->get(UserConstants::USER_ID),
1,
$absoluteUrlList,
$page,
$offset,
$limit
)
);
}
public function ShowExceptionAction(Request $request, FlattenException $exception, DebugLoggerInterface $logger = null)
{
$session = $request->getSession();
$em = $this->getDoctrine()->getManager();
// Generic::debugMessage($session);
// $currentContent = $this->getAndCleanOutputBuffering($request->headers->get('X-Php-Ob-Level', -1));
// $showException = $request->attributes->get('showException', $this->debug); // As opposed to an additional parameter, this maintains BC
$code = $exception->getStatusCode();
// $data = MiscActions::getDocumentsByUserId($em, $session->get(UserConstants::USER_ID));
return $this->render(
'@Application/pages/error/error_' . $code . '.html.twig',
array(
'page_title' => 'Sorry',
// 'data' => $data
)
);
}
public function NotificationAction(Request $request)
{
$session = $request->getSession();
//'error', 'success', 'alert', 'information', 'warning', 'confirm'
$themes = array(
'error' => 'bg-red',
'success' => 'bg-green',
'alert' => 'bg-purple',
'information' => 'bg-grey',
'warning' => 'bg-orange',
'confirm' => 'bg-blue',
);
$fa = array(
'error' => 'fa fa-ban',
'success' => 'fa fa-check',
'alert' => 'fa fa-envelope',
'information' => 'fa fa-comments',
'warning' => 'fa fa-warning',
'confirm' => 'fa fa-crosshairs',
);
// Generic::debugMessage($session);
// ── DB-first notification list ─────────────────────────────────────────
// History is sourced from entity_notification (persistent DB), not the
// socket server's in-memory cache which is lost on restart / missing the
// /get_all_notification endpoint entirely.
$cgEm = $this->getDoctrine()->getManager('company_group');
$userId = (int) $request->getSession()->get(UserConstants::USER_ID, 0);
$companyId = (int) $request->getSession()->get(UserConstants::USER_COMPANY_ID, 0);
$filterUnread = $request->query->get('filter') === 'unread';
$qb = $cgEm->getRepository('CompanyGroupBundle\\Entity\\EntityNotification')
->createQueryBuilder('n')
->where('n.userId = :uid')
->setParameter('uid', $userId);
if ($filterUnread) {
$qb->andWhere('n.seenFlag = 0');
}
$notifications = $qb
->orderBy('n.notificationTs', 'DESC')
->setMaxResults(100)
->getQuery()
->getResult();
$unreadCount = (int) $cgEm->getConnection()->fetchOne(
'SELECT COUNT(*) FROM entity_notification WHERE user_id = :uid AND seen_flag = 0',
['uid' => $userId]
);
return $this->render(
'@Application/pages/dashboard/notification.html.twig',
array(
'page_title' => 'Notifications',
'dt' => $notifications,
'unreadCount' => $unreadCount,
'filterUnread' => $filterUnread,
'themes' => $themes,
'fa' => $fa,
)
);
}
public function NotificationDetailAction(Request $request, $id = 0)
{
$em = $this->getDoctrine()->getManager('company_group');
$notification = $em->getRepository('CompanyGroupBundle\\Entity\\EntityNotification')->find($id);
if (!$notification) {
return $this->render(
'@Application/pages/dashboard/notification_detail.html.twig',
array(
'page_title' => 'Notification Detail',
'notification' => null,
'detailError' => 'Notification not found.',
)
);
}
if ((int) $notification->getSeenFlag() !== 1) {
$notification->setSeenFlag(1);
}
if ((int) $notification->getReadFlag() !== 1) {
$notification->setReadFlag(1);
}
$em->flush();
return $this->render(
'@Application/pages/dashboard/notification_detail.html.twig',
array(
'page_title' => 'Notification Detail',
'notification' => $notification,
'detailError' => '',
)
);
}
public function EditAccountAction(Request $request)
{
$session = $request->getSession();
//'error', 'success', 'alert', 'information', 'warning', 'confirm'
$themes = array(
'error' => 'bg-red',
'success' => 'bg-green',
'alert' => 'bg-purple',
'information' => 'bg-aqua',
'warning' => 'bg-orange',
'confirm' => 'bg-blue',
);
$fa = array(
'error' => 'fa fa-ban',
'success' => 'fa fa-check',
'alert' => 'fa fa-envelope',
'information' => 'fa fa-comments',
'warning' => 'fa fa-warning',
'confirm' => 'fa fa-crosshairs',
);
$em = $this->getDoctrine()->getManager();
$g_path = '';
if ($request->isMethod('POST')) {
$post = $request->request;
$path = "";
foreach ($request->files as $uploadedFile) {
if ($uploadedFile != null) {
$fileName = md5(uniqid()) . '.' . $uploadedFile->guessExtension();
$path = $fileName;
$upl_dir = $this->container->getParameter('kernel.root_dir') . '/../web/uploads/FileUploads/' . $request->request->get('userId') . '/';
if (!file_exists($upl_dir)) {
mkdir($upl_dir, 0777, true);
}
$file = $uploadedFile->move($upl_dir, $path);
}
}
if ($path != "")
$file_path = 'uploads/FileUploads/' . $request->request->get('userId') . '/' . $path;
$g_path = $this->container->getParameter('kernel.root_dir') . '/../web/uploads/FileUploads/' . $request->request->get('userId') . '/' . $path;
// $img_file = file_get_contents($g_path);
// $image_data=base64_encode($img_file);
// $encoded_data=System::encryptSignature($image_data,$request->request->get('approvalHash'));
$query_here = $this->getDoctrine()
->getRepository('ApplicationBundle\\Entity\\SysUser')
->findOneBy(
array('userId' => $request->request->get('userId'))
);
if ($query_here) {
$new = $query_here;
if ($path != "") {
if ($request->request->has('check_pp')) {
$new->setImage($file_path);
$session->set(UserConstants::USER_IMAGE, $new->getImage());
} else
$new->setImage('');
}
$new->setName($request->request->get('name'));
$session->set(UserConstants::USER_NAME, $request->request->get('name'));
//now password
if ($request->request->get('oldPass') != '') {
//1st check if old pass valid
if (!$this->container->get('app.legacy_password_service')->verifyWithSalt($new->getPassword(), $request->request->get('oldPass'), $new->getSalt())) {
$this->addFlash(
'error',
'Your Old Password Was Wrong'
);
} else {
//old pass valid so now check if neww passes matches
if ($request->request->get('newPass') == $request->request->get('newPassAgain')) {
$new->setSalt(uniqid(mt_rand()));
$password = $this->container->get('app.legacy_password_service')->hashWithSalt($request->request->get('newPass'), $new->getSalt());
$new->setPassword($password);
$em->flush();
} else {
$this->addFlash(
'error',
'Passwords Did not Match'
);
}
}
}
} else {
// $new=new EncryptedSignature();
// $new->setData($encoded_data);
// $new->setUserId($request->request->get('userId'));
// $em->persist($new);
}
$em->flush();
// now deleting the file
}
$user_data = Users::getUserInfoByLoginId($em, $this->getLoggedUserLoginId($request));
// Generic::debugMessage($session);
return $this->render(
'@System/pages/settings/edit_account.html.twig',
array(
'page_title' => 'Edit Account',
'user_data' => $user_data,
// 'dt'=>json_decode(System::GetAllNotification($request->getSession()->get(UserConstants::USER_ID)),true),
'themes' => $themes,
'fa' => $fa,
)
);
}
public function indexApplicantAction(Request $request)
{
$systemType = $this->container->hasParameter('system_type') ? $this->container->getParameter('system_type') : '_ERP_';
$twig_file = '@Application/pages/login/applicant_login.html.twig';
$session = $request->getSession();
if ($systemType == '_BUDDYBEE_') {
if ($session->get('buddybeeAdminLevel', 0) > 1)
return $this->redirectToRoute("buddybee_admin_dashboard", []);
elseif ($session->get('isConsultant') == 1)
return $this->redirectToRoute("consultant_dashboard", []);
else
return $this->redirectToRoute("student_dashboard", []);
} elseif ($systemType == '_CENTRAL_') {
return $this->render(
'@Application/pages/dashboard/index_applicant.html.twig',
array(
'page_title' => 'Applicant Dashboard'
)
);
} else
return $this->render(
'@Application/pages/dashboard/consultant_dashboard.html.twig',
array(
'page_title' => 'Applicant Dashboard'
)
);
}
public function indexCentralLandingAction(Request $request)
{
$session = $request->getSession();
$userAccessList = $session->get('userAccessList', []);
$companyNames = array_map(function ($company) {
return $company['companyName'];
}, $userAccessList);
if ($request->isMethod('POST')) {
$employeeId = $request->request->get('employee_id');
$companyName = $request->request->get('company_name');
if ($employeeId && $companyName) {
return new JsonResponse([
'status' => 'success',
'message' => 'Data received successfully',
'employee_id' => $employeeId,
'company_name' => $companyName,
]);
} else {
return new JsonResponse([
'status' => 'error',
'message' => 'Employee ID or Company Name is missing',
]);
}
}
// If the request is not a POST request, render the normal page
return $this->render(
'@Application/pages/dashboard/index_central_landing.html.twig',
[
'page_title' => 'Central Landing',
'companyNames' => $companyNames
]
);
}
public function HummanResourceAction()
{
$em = $this->getDoctrine()->getManager();
$currentTime = new \Datetime();
$currDate = $currentTime->format('Y-m-d');
$queryBuilder = $em->createQueryBuilder();
$queryBuilder->select('A.employeeId', 'A.entry', 'A.lastIn', 'A.lastOut', 'A.currentLocation','A.createdAt', 'E.name','E.image','D.positionName')
->from('ApplicationBundle\\Entity\\EmployeeAttendance', 'A')
->where('A.createdAt >= :date')
->andWhere('A.createdAt <= :date_end')
->leftJoin('ApplicationBundle\\Entity\\Employee', 'E', 'WITH', 'A.employeeId = E.employeeId')
->leftJoin('ApplicationBundle\\Entity\\SysDepartmentPosition', 'D', 'WITH', 'E.positionId = D.positionId')
->setParameter('date', $currDate)
->setParameter('date_end', $currDate . ' 23:59:59')
->orderBy('A.createdAt', 'DESC') // latest records first
->setMaxResults(5); // limit to 5 records
$data = $queryBuilder->getQuery()->getResult();
$formattedData = array_map(function($item) {
return [
'employeeId' => $item['employeeId'],
'entry' => $item['entry'] ? $item['entry']->format('Y-m-d H:i:s') : null,
'lastIn' => $item['lastIn'] ? $item['lastIn']->format('Y-m-d H:i:s') : null,
'lastOut' => $item['lastOut'] ? $item['lastOut']->format('Y-m-d H:i:s') : null,
'currentLocation' => $item['currentLocation'],
'createdAt' => $item['createdAt'] ? $item['createdAt']->format('Y-m-d H:i:s') : null,
'name' => $item['name'],
'image' => $item['image'],
'positionName' => $item['positionName'],
];
}, $data);
$totalEmployees = $em->getRepository('ApplicationBundle\\Entity\\EmployeeDetails')
->createQueryBuilder('E')
->select('COUNT(E.emp_status)')
->where('E.emp_status = 1')
->getQuery()
->getSingleScalarResult();
$maleCount = $em->getRepository('ApplicationBundle\\Entity\\EmployeeDetails')
->createQueryBuilder('E')
->select('COUNT(E.id)')
->where('E.sex = 1')
->getQuery()
->getSingleScalarResult();
$femaleCount = $em->getRepository('ApplicationBundle\\Entity\\EmployeeDetails')
->createQueryBuilder('E')
->select('COUNT(E.id)')
->where('E.sex = 2')
->getQuery()
->getSingleScalarResult();
$fullTime = $em->getRepository('ApplicationBundle\\Entity\\EmployeeDetails')
->createQueryBuilder('E')
->select('COUNT(E.id)')
->where('E.empType = 1')
->getQuery()
->getSingleScalarResult();
$partTime = $em->getRepository('ApplicationBundle\\Entity\\EmployeeDetails')
->createQueryBuilder('E')
->select('COUNT(E.id)')
->where('E.empType = 2')
->getQuery()
->getSingleScalarResult();
$intern = $em->getRepository('ApplicationBundle\\Entity\\EmployeeDetails')
->createQueryBuilder('E')
->select('COUNT(E.id)')
->where('E.empType = 3')
->getQuery()
->getSingleScalarResult();
$temporary = $em->getRepository('ApplicationBundle\\Entity\\EmployeeDetails')
->createQueryBuilder('E')
->select('COUNT(E.id)')
->where('E.empType = 4')
->getQuery()
->getSingleScalarResult();
$contractual = $em->getRepository('ApplicationBundle\\Entity\\EmployeeDetails')
->createQueryBuilder('E')
->select('COUNT(E.id)')
->where('E.empType = 5')
->getQuery()
->getSingleScalarResult();
return $this->render(
'@Application/pages/dashboard/humanResource.html.twig',
[
'page_title' => 'Human Resource Dashboard',
'attendanceDetails' => $formattedData,
'totalEmployee' => $totalEmployees,
'maleCount' => $maleCount,
'femaleCount' => $femaleCount,
'fullTime' => $fullTime,
'partTime' => $partTime,
'temporary' => $temporary,
'contractual' => $contractual,
'intern' => $intern,
]
);
}
public function projectCashFlowReportAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$context = $request->query->get('context', 'finance'); // 'finance' | 'projects' — only tweaks the active nav item
$cashFlow = ['hasData' => false];
try {
$cashFlow = $this->buildCompanyCashFlow($em, 12);
} catch (\Throwable $e) {
$cashFlow = ['hasData' => false, 'error' => $e->getMessage()];
}
return $this->render(
'@Application/pages/dashboard/cash_flow_report.html.twig',
array(
'page_title' => 'Cash Flow Report',
'cashFlow' => $cashFlow,
'context' => $context,
)
);
}
/**
* Company cash-flow: real monthly cash inflow / outflow / net + running balance over the
* last N months, derived from posted GL movements on the Cash & Cash Equivalents head
* subtree (marker _CACEP_ + path_tree descendants) — the same definition the financial
* statements use. Base-currency amounts (amount x currency_multiply_rate). Fail-safe.
*/
private function buildCompanyCashFlow($em, $monthsBack = 12)
{
$conn = $em->getConnection();
$cashMarker = \ApplicationBundle\Constants\AccountsConstant::CASH_AND_CASH_EQUIVALENT_PARENT;
$result = [
'hasData' => false,
'labels' => [], 'inflow' => [], 'outflow' => [], 'net' => [], 'balance' => [],
'totalInflow' => 0, 'totalOutflow' => 0, 'totalNet' => 0,
'openingBalance' => 0, 'closingBalance' => 0, 'rows' => [],
];
// 1. Resolve cash & bank head ids: heads marked _CACEP_ plus their path_tree descendants.
$marked = $conn->fetchAllAssociative(
'SELECT accounts_head_id FROM acc_accounts_head WHERE marker_hash LIKE ?',
['%' . $cashMarker . '%']
);
$ids = [];
$likeParts = [];
$params = [];
foreach ($marked as $m) {
$hid = (int) $m['accounts_head_id'];
$ids[$hid] = $hid;
$likeParts[] = 'path_tree LIKE ?';
$params[] = '%/' . $hid . '/%';
}
if (!empty($likeParts)) {
foreach ($conn->fetchAllAssociative('SELECT accounts_head_id FROM acc_accounts_head WHERE ' . implode(' OR ', $likeParts), $params) as $d) {
$ids[(int) $d['accounts_head_id']] = (int) $d['accounts_head_id'];
}
}
$cashHeadIds = array_values($ids);
if (empty($cashHeadIds)) {
return $result; // hasData=false → the page shows a friendly "no cash heads marked" notice
}
$inClause = implode(',', array_map('intval', $cashHeadIds));
$baseExpr = "td.amount * COALESCE(NULLIF(td.currency_multiply_rate, ''), 1)";
$drCond = "(LOWER(td.position) LIKE 'dr%' OR LOWER(td.position) = 'debit')";
$start = new \DateTime('first day of this month 00:00:00');
$start->modify('-' . ($monthsBack - 1) . ' months');
$startStr = $start->format('Y-m-d 00:00:00');
// Opening cash balance (Dr - Cr) before the window.
$openRow = $conn->fetchAssociative(
"SELECT SUM(CASE WHEN $drCond THEN $baseExpr ELSE -($baseExpr) END) bal
FROM acc_transaction_details td
JOIN acc_transactions t ON t.transaction_id = td.transaction_id
WHERE td.accounts_head_id IN ($inClause) AND td.ledger_hit = 1 AND t.transaction_date < ?",
[$startStr]
);
$opening = (float) ($openRow['bal'] ?? 0);
// Monthly movements inside the window.
$rows = $conn->fetchAllAssociative(
"SELECT DATE_FORMAT(t.transaction_date, '%Y-%m') ym,
SUM(CASE WHEN $drCond THEN $baseExpr ELSE 0 END) inflow,
SUM(CASE WHEN NOT $drCond THEN $baseExpr ELSE 0 END) outflow
FROM acc_transaction_details td
JOIN acc_transactions t ON t.transaction_id = td.transaction_id
WHERE td.accounts_head_id IN ($inClause) AND td.ledger_hit = 1 AND t.transaction_date >= ?
GROUP BY ym",
[$startStr]
);
$byMonth = [];
foreach ($rows as $r) {
$byMonth[$r['ym']] = ['in' => (float) $r['inflow'], 'out' => (float) $r['outflow']];
}
$running = $opening;
$cursor = clone $start;
for ($i = 0; $i < $monthsBack; $i++) {
$ym = $cursor->format('Y-m');
$in = isset($byMonth[$ym]) ? $byMonth[$ym]['in'] : 0.0;
$out = isset($byMonth[$ym]) ? $byMonth[$ym]['out'] : 0.0;
$net = $in - $out;
$running += $net;
$result['labels'][] = $cursor->format('M Y');
$result['inflow'][] = round($in, 2);
$result['outflow'][] = round($out, 2);
$result['net'][] = round($net, 2);
$result['balance'][] = round($running, 2);
$result['rows'][] = ['label' => $cursor->format('M Y'), 'inflow' => round($in, 2), 'outflow' => round($out, 2), 'net' => round($net, 2), 'balance' => round($running, 2)];
$result['totalInflow'] += $in;
$result['totalOutflow'] += $out;
$cursor->modify('+1 month');
}
$result['hasData'] = true;
$result['openingBalance'] = round($opening, 2);
$result['closingBalance'] = round($running, 2);
$result['totalNet'] = round($result['totalInflow'] - $result['totalOutflow'], 2);
$result['totalInflow'] = round($result['totalInflow'], 2);
$result['totalOutflow'] = round($result['totalOutflow'], 2);
return $result;
}
public function PerformanceReviewDashboardAction()
{
return $this->render(
'@Application/pages/dashboard/performance_review_dashboard.html.twig',
array(
'page_title' => 'Performance Review Dashboard'
)
);
}
public function RecruitmentDashboardAction(Request $request)
{
$em = $this->getDoctrine()->getManager();
$em_goc = $this->getDoctrine()->getManager('company_group');
$companyId = $this->getLoggedUserCompanyId($request);
$company_data = Company::getCompanyData($em, $companyId);
// 1. Total Job Posts
$totalJobPosts = $em->getRepository('ApplicationBundle\\Entity\\JobRecruitment')
->createQueryBuilder('j')
->select('COUNT(j.jobRecruitmentId)')
->where('j.approved = 1')
->getQuery()
->getSingleScalarResult();
// 2. Active Job Posts
$activeJobPosts = $em->getRepository('ApplicationBundle\\Entity\\JobRecruitment')
->createQueryBuilder('j')
->select('COUNT(j.jobRecruitmentId)')
->where('j.approved = 1')
->andWhere('j.jobOpeningStatus = 2') // Assuming 2 is Active
->getQuery()
->getSingleScalarResult();
// 3. Total Applicants
$totalApplicants = $em_goc->getRepository("CompanyGroupBundle\\Entity\\EntityApplicantApplicationList")
->createQueryBuilder('a')
->select('COUNT(a.id)')
->where('a.CompanyId = :companyId')
->andWhere('a.appId = :appId')
->setParameter('companyId', $companyId)
->setParameter('appId', $company_data->getAppId())
->getQuery()
->getSingleScalarResult();
// 4. Simple recent activity for dashboard
$recentJobs = $em->getRepository('ApplicationBundle\\Entity\\JobRecruitment')
->findBy(['approved' => 1], ['createdAt' => 'DESC'], 5);
// 5. Jobs with applicant counts
$jobsWithApplicantCount = $em->getRepository('ApplicationBundle\\Entity\\JobRecruitment')
->createQueryBuilder('j')
->select('j.jobRecruitmentId', 'j.title', 'j.applicationClosingDate', 'j.jobOpeningStatus')
->where('j.approved = 1')
->orderBy('j.createdAt', 'DESC')
->setMaxResults(8)
->getQuery()
->getResult();
foreach ($jobsWithApplicantCount as &$job) {
$count = $em_goc->getRepository("CompanyGroupBundle\\Entity\\EntityApplicantApplicationList")
->createQueryBuilder('a')
->select('COUNT(a.id)')
->where('a.jobPostId = :jobId')
->setParameter('jobId', $job['jobRecruitmentId'])
->getQuery()
->getSingleScalarResult();
$job['applicantCount'] = $count;
}
return $this->render(
'@Application/pages/dashboard/recruitment_dashboard.html.twig',
array(
'page_title' => 'Recruitment Dashboard',
'totalJobPosts' => $totalJobPosts,
'activeJobPosts' => $activeJobPosts,
'totalApplicants' => $totalApplicants,
'recentJobs' => $recentJobs,
'jobsWithApplicantCount' => $jobsWithApplicantCount,
'jobOpeningStatusArr' => HumanResourceConstant::$jobOpeningStatus
)
);
}
public function TalentManagementDashboardAction()
{
return $this->render(
'@Application/pages/dashboard/talent_management_dashboard.html.twig',
array(
'page_title' => 'Talent Management Dashboard'
)
);
}
public function CreateCompanyAction(Request $request)
{
$systemType = $this->container->hasParameter('system_type') ? $this->container->getParameter('system_type') : '_ERP_';
$appId = $request->get('app_id', 0);
$post = $request;
$session = $request->getSession();
if ($request->isMethod('POST')) {
if ($systemType == '_CENTRAL_') {
$em_goc = $this->getDoctrine()->getManager('company_group');
$em_goc->getConnection()->connect();
$connected = $em_goc->getConnection()->isConnected();
$gocDataList = [];
if ($connected) {
$goc = null;
$serverList = MiscActions::getServerListById( $this->container->getParameter('database_user'), $this->container->getParameter('database_password'), $this->container->hasParameter('server_access_list') ?$this->container->getParameter('server_access_list') :[] );
$companyGroupHash = $post->get('company_short_code', '');
$defaultUsageDate = new \DateTime();
$defaultUsageDate->modify('+1 year');
$usageValidUpto = new \DateTime($post->get('usage_valid_upto_dt_str', $defaultUsageDate->format('Y-m-d')));
$companyGroupServerId = $post->get('server_id', 1);
$companyGroupServerAddress = $serverList[$companyGroupServerId]['absoluteUrl'];
$companyGroupServerPort = $serverList[$companyGroupServerId]['port'];
$companyGroupServerHash = $serverList[$companyGroupServerId]['serverMarker'];
// $dbUser=
if ($appId != 0)
$goc = $this->getDoctrine()->getManager('company_group')
->getRepository("CompanyGroupBundle\\Entity\\CompanyGroup")
->findOneBy(array(
'appId' => $appId
));
if (!$goc)
$goc = new CompanyGroup();
if ($appId == 0) {
$biggestAppIdCg = $this->getDoctrine()->getManager('company_group')
->getRepository("CompanyGroupBundle\\Entity\\CompanyGroup")
->findOneBy(array( // 'appId' => $appId
), array(
'appId' => 'desc'
));
if ($biggestAppIdCg)
$appId = 1 + $biggestAppIdCg->getAppId();
}
$goc->setName($post->get('company_name'));
$goc->setCompanyGroupHash($companyGroupHash);
$goc->setAppId($appId);
$goc->setActive(1);
$goc->setAddress($post->get('address'));
$goc->setShippingAddress($post->get('s_address'));
$goc->setBillingAddress($post->get('b_address'));
$goc->setMotto($post->get('motto'));
$goc->setInitiateFlag($post->get('initiate_flag', 3));
$goc->setInvoiceFooter($post->get('i_footer'));
$goc->setGeneralFooter($post->get('g_footer'));
$goc->setCompanyReg($post->get('company_reg', ''));
$goc->setCompanyTin($post->get('company_tin', ''));
$goc->setCompanyBin($post->get('company_bin', ''));
$goc->setCompanyTl($post->get('company_tl', ''));
$goc->setCompanyType($post->get('company_type', ''));
$goc->setCurrentSubscriptionPackageId($post->get('package', 1));
$goc->setUsageValidUptoDate($usageValidUpto);
$goc->setUsageValidUptoDateTs($usageValidUpto->format('U'));
// $goc->setCu($post->get('package', ''));
$goc->setAdminUserAllowed($post->get('number_of_admin_user', 1));
$goc->setUserAllowed($post->get('number_of_user', 2));
$goc->setSubscriptionMonth($post->get('subscription_month', 1));
$goc->setCompanyDescription($post->get('company_description', ''));
$goc->setDbUser($post->get('db_user'));
$goc->setDbPass($post->get('db_pass'));
$goc->setDbHost($post->get('db_host'));
$goc->setOwnerId($session->get(UserConstants::USER_ID));
$goc->setCompanyGroupServerId($companyGroupServerId);
$goc->setCompanyGroupServerAddress($companyGroupServerAddress);
$goc->setCompanyGroupServerPort($companyGroupServerPort);
$goc->setCompanyGroupServerHash($companyGroupServerHash);
foreach ($request->files as $uploadedFile) {
if ($uploadedFile != null) {
$fileName = 'company_image' . $appId . '.' . $uploadedFile->guessExtension();
$path = $fileName;
$upl_dir = $this->container->getParameter('kernel.root_dir') . '/../web/uploads/CompanyImage/';
if ($goc->getImage() != null && $goc->getImage() != '' && file_exists($this->container->getParameter('kernel.root_dir') . '/../web' . $goc->getImage())) {
unlink($this->container->getParameter('kernel.root_dir') . '/../web' . $goc->getImage());
}
if (!file_exists($upl_dir)) {
mkdir($upl_dir, 0777, true);
}
$file = $uploadedFile->move($upl_dir, $path);
if ($path != "")
$goc->setImage('/uploads/CompanyImage/' . $path);
}
}
$em_goc->persist($goc);
$em_goc->flush();
$goc->setDbName('cg_' . $appId . '_' . $companyGroupHash);
$goc->setDbUser($serverList[$companyGroupServerId]['dbUser']);
$goc->setDbPass($serverList[$companyGroupServerId]['dbPass']);
$goc->setDbHost('localhost');
$em_goc->flush();
$centralUser = $this->getDoctrine()->getManager('company_group')
->getRepository("CompanyGroupBundle\\Entity\\EntityApplicantDetails")
->findOneBy(array(
'applicantId' => $session->get(UserConstants::USER_ID, 0)
));
if ($centralUser) {
$userAppIds = json_decode($centralUser->getUserAppIds(), true);
$userTypesByAppIds = json_decode($centralUser->getUserTypesByAppIds(), true);
if ($userAppIds == null) $userAppIds = [];
if ($userTypesByAppIds == null) $userTypesByAppIds = [];
$userAppIds = array_merge($userAppIds, array_diff([$appId], $userAppIds));
if (!isset($userTypesByAppIds[$appId])) {
$userTypesByAppIds[$appId] = [];
}
$userTypesByAppIds[$appId] = array_merge($userTypesByAppIds[$appId], array_diff([UserConstants::USER_TYPE_SYSTEM], $userTypesByAppIds[$appId]));
$centralUser->setUserAppIds(json_encode($userAppIds));
$centralUser->setUserTypesByAppIds(json_encode($userTypesByAppIds));
$em_goc->flush();
}
$accessList = $session->get('userAccessList', []);
$d = array(
'userType' => UserConstants::USER_TYPE_SYSTEM,
'globalId' => $session->get(UserConstants::USER_ID, 0),
'serverId' => $companyGroupServerId,
'serverUrl' => $companyGroupServerAddress,
'serverPort' => $companyGroupServerPort,
'systemType' => '_ERP_',
'companyId' => 1,
'appId' => $appId,
'companyLogoUrl' => $goc->getImage(),
'companyName' => $goc->getName(),
'authenticationStr' => $this->get('url_encryptor')->encrypt(
json_encode(
array(
'globalId' => $session->get(UserConstants::USER_ID, 0),
'appId' => $appId,
'authenticate' => 1,
'userType' => UserConstants::USER_TYPE_SYSTEM
)
)
),
'userCompanyList' => []
);
$accessList[] = $d;
// MiscActions::UpdateCompanyListInSession($em_goc, $centralUser->getApplicantId(), 1, 1, 1, $d);
//temporary solution
MiscActions::UpdateCompanyListInSession($em_goc, $session->get(UserConstants::USER_ID), 1, 1, 1, $d);
$session->set('userAccessList', $accessList);
}
return new JsonResponse(array(
'success' => true,
"message" => "Company Created Successfully",
'user_access_data' => $d,
'app_id' => $appId
));
}
}
return $this->render('@HoneybeeWeb/pages/create_company.html.twig', array(
'page_title' => 'Company',
'serverList' => GeneralConstant::$serverListById
));
}
public function checkBinTinAction(Request $request)
{
$em_goc = $this->getDoctrine()->getManager('company_group');
$type = $request->request->get('type');
$value = trim($request->request->get('value'));
$repo = $em_goc->getRepository("CompanyGroupBundle\\Entity\\CompanyGroup");
if ($type === 'tin') {
$company = $repo->findOneBy(array(
'company_tin' => $value
));
} elseif ($type === 'bin') {
$company = $repo->findOneBy(array(
'company_bin' => $value
));
} elseif ($type === 'reg') {
$company = $repo->findOneBy(array(
'company_reg' => $value
));
} else {
return new JsonResponse(['exists' => false]);
}
return new JsonResponse([
'exists' => $company ? true : false
]);
}
public function createTicketAction(Request $request)
{
$em_goc = $this->getDoctrine()->getManager('company_group');
if ($request->isMethod('POST')) {
$ticket = new EntityTicket();
$ticket->setTitle($request->request->get('title', 0));
$ticket->setEmail($request->request->get('email'));
$ticket->setTicketBody($request->request->get('ticketBody'));
$em_goc->persist($ticket);
$em_goc->flush();
}
return new JsonResponse(array(
"success" => 'true',
"message" => "Ticket Created Successfully",
));
}
public function GetSessionDataForAppAction(
Request $request,
$remoteVerify = 0,
$version = 'latest',
$identifier = '_default_',
$refRoute = '',
$apiKey = '_ignore_'
)
{
$message = "";
$gocList = [];
$session = $request->getSession();
if ($request->request->has('token')) {
$em_goc = $this->getDoctrine()->getManager('company_group');
$to_set_session_data = MiscActions::GetSessionDataFromToken($em_goc, $request->request->get('token'))['sessionData'];
if ($to_set_session_data != null) {
foreach ($to_set_session_data as $k => $d) {
//check if mobile
$session->set($k, $d);
}
}
}
if ($request->request->has('sessionData')) {
$to_set_session_data = $request->request->get('sessionData');
foreach ($to_set_session_data as $k => $d) {
//check if mobile
$session->set($k, $d);
}
}
if ($version !== 'latest') {
$session_data = array(
'oAuthToken' => $session->get('oAuthToken'),
'locale' => $session->get('locale'),
'firebaseToken' => $session->get('firebaseToken'),
'token' => $session->get('token'),
UserConstants::USER_EMPLOYEE_ID => $session->get(UserConstants::USER_EMPLOYEE_ID),
UserConstants::USER_ID => $session->get(UserConstants::USER_ID),
UserConstants::LAST_SETTINGS_UPDATED_TS => $session->get(UserConstants::LAST_SETTINGS_UPDATED_TS),
UserConstants::USER_LOGIN_ID => $session->get(UserConstants::USER_LOGIN_ID),
UserConstants::USER_EMAIL => $session->get(UserConstants::USER_EMAIL),
UserConstants::USER_TYPE => $session->get(UserConstants::USER_TYPE),
UserConstants::USER_IMAGE => $session->get(UserConstants::USER_IMAGE),
UserConstants::USER_DEFAULT_ROUTE => $session->get(UserConstants::USER_DEFAULT_ROUTE),
UserConstants::USER_NAME => $session->get(UserConstants::USER_NAME),
UserConstants::USER_COMPANY_ID => $session->get(UserConstants::USER_COMPANY_ID),
UserConstants::USER_COMPANY_ID_LIST => $session->get(UserConstants::USER_COMPANY_ID_LIST),
UserConstants::USER_COMPANY_NAME_LIST => $session->get(UserConstants::USER_COMPANY_NAME_LIST),
UserConstants::USER_COMPANY_IMAGE_LIST => $session->get(UserConstants::USER_COMPANY_IMAGE_LIST),
UserConstants::USER_APP_ID => $session->get(UserConstants::USER_APP_ID),
UserConstants::USER_POSITION_LIST => $session->get(UserConstants::USER_POSITION_LIST),
UserConstants::USER_CURRENT_POSITION => $session->get(UserConstants::USER_CURRENT_POSITION),
UserConstants::ALL_MODULE_ACCESS_FLAG => $session->get(UserConstants::ALL_MODULE_ACCESS_FLAG),
UserConstants::USER_GOC_ID => $session->get(UserConstants::USER_GOC_ID),
UserConstants::USER_NOTIFICATION_ENABLED => GeneralConstant::NOTIFICATION_ENABLED == 1 ? ($this->getParameter('notification_enabled') == 1 ? 1 : 0) : 0,
UserConstants::USER_NOTIFICATION_SERVER => $this->getParameter('notification_server'),
UserConstants::PRODUCT_NAME_DISPLAY_TYPE => $session->get(UserConstants::PRODUCT_NAME_DISPLAY_TYPE),
UserConstants::APPLICATION_SECRET => $session->get(UserConstants::APPLICATION_SECRET),
//new addition
'appIdList' => $session->get('appIdList'),
'branchIdList' => $session->get('branchIdList', null),
'branchId' => $session->get('branchId', null),
'companyIdListByAppId' => $session->get('companyIdListByAppId'),
'companyNameListByAppId' => $session->get('companyNameListByAppId'),
'companyImageListByAppId' => $session->get('companyImageListByAppId'),
'userAccessList' => $session->get('userAccessList'),
'csToken' => $session->get('csToken'),
);
} else {
$session_data = array(
'oAuthToken' => $session->get('oAuthToken'),
'locale' => $session->get('locale'),
'firebaseToken' => $session->get('firebaseToken'),
'token' => $session->get('token'),
UserConstants::USER_EMPLOYEE_ID => $session->get(UserConstants::USER_EMPLOYEE_ID),
UserConstants::USER_ID => $session->get(UserConstants::USER_ID),
UserConstants::LAST_SETTINGS_UPDATED_TS => $session->get(UserConstants::LAST_SETTINGS_UPDATED_TS),
UserConstants::USER_LOGIN_ID => $session->get(UserConstants::USER_LOGIN_ID),
UserConstants::USER_EMAIL => $session->get(UserConstants::USER_EMAIL),
UserConstants::USER_TYPE => $session->get(UserConstants::USER_TYPE),
UserConstants::USER_IMAGE => $session->get(UserConstants::USER_IMAGE),
UserConstants::USER_DEFAULT_ROUTE => $session->get(UserConstants::USER_DEFAULT_ROUTE),
UserConstants::USER_NAME => $session->get(UserConstants::USER_NAME),
UserConstants::USER_COMPANY_ID => $session->get(UserConstants::USER_COMPANY_ID),
UserConstants::USER_COMPANY_ID_LIST => $session->get(UserConstants::USER_COMPANY_ID_LIST),
UserConstants::USER_COMPANY_NAME_LIST => $session->get(UserConstants::USER_COMPANY_NAME_LIST),
UserConstants::USER_COMPANY_IMAGE_LIST => $session->get(UserConstants::USER_COMPANY_IMAGE_LIST),
UserConstants::USER_APP_ID => $session->get(UserConstants::USER_APP_ID),
UserConstants::USER_POSITION_LIST => $session->get(UserConstants::USER_POSITION_LIST),
UserConstants::USER_CURRENT_POSITION => $session->get(UserConstants::USER_CURRENT_POSITION),
UserConstants::ALL_MODULE_ACCESS_FLAG => $session->get(UserConstants::ALL_MODULE_ACCESS_FLAG),
UserConstants::USER_GOC_ID => $session->get(UserConstants::USER_GOC_ID),
UserConstants::USER_NOTIFICATION_ENABLED => GeneralConstant::NOTIFICATION_ENABLED == 1 ? ($this->getParameter('notification_enabled') == 1 ? 1 : 0) : 0,
UserConstants::USER_NOTIFICATION_SERVER => $this->getParameter('notification_server'),
UserConstants::PRODUCT_NAME_DISPLAY_TYPE => $session->get(UserConstants::PRODUCT_NAME_DISPLAY_TYPE),
UserConstants::APPLICATION_SECRET => $session->get(UserConstants::APPLICATION_SECRET),
//new addition
'appIdList' => $session->get('appIdList'),
'branchIdList' => $session->get('branchIdList', null),
'branchId' => $session->get('branchId', null),
'companyIdListByAppId' => $session->get('companyIdListByAppId'),
'companyNameListByAppId' => $session->get('companyNameListByAppId'),
'companyImageListByAppId' => $session->get('companyImageListByAppId'),
'userAccessList' => $session->get('userAccessList'),
'csToken' => $session->get('csToken'),
);
}
$response = new JsonResponse(array(
"success" => empty($session->get(UserConstants::USER_ID)) ? false : true,
// 'session'=>$request->getSession(),
'session_data' => $session_data,
// 'session2'=>$_SESSION,
));
$response->headers->set('Access-Control-Allow-Origin', '*, null');
$response->headers->set('Access-Control-Allow-Methods', 'POST');
// $response->setCallback('FUNCTION_CALLBACK_NAME');
return $response;
}
public function EmployeeAddUsingQrCodeCentralAction(Request $request)
{
$session = $request->getSession();
if ($request->isMethod('post')) {
$em_goc = $this->getDoctrine()->getManager('company_group');
$entityApplicant = $em_goc->getRepository("CompanyGroupBundle\\Entity\\EntityApplicantDetails")
->findOneBy(
array(
'applicantId' => $request->request->get('userId'),
)
);
$app = $em_goc->getRepository("CompanyGroupBundle\\Entity\\CompanyGroup")
->findOneBy(
array(
'appId' => $request->request->get('appId'),
)
);
$userType = $request->request->get('userType');
$currAccessData = json_decode($entityApplicant->getUserTypesByAppIds(), true);
if ($currAccessData == null) $currAccessData = [];
if (!isset($currAccessData[$request->request->get('appId')]))
$currAccessData[$request->request->get('appId')] = [];
$currAccessData[$request->request->get('appId')] = array_merge($currAccessData[$request->request->get('appId')], array_diff([$request->request->get('userType', $userType)], $currAccessData[$request->request->get('appId')]));
$entityApplicant->setUserTypesByAppIds(json_encode($currAccessData));
$em_goc->flush();
$postData = [];
$postData['globalId'] = $entityApplicant->getApplicantId();
$postData['username'] = $entityApplicant->getUsername();
$postData['email'] = $entityApplicant->getEmail();
$postData['firstname'] = $entityApplicant->getFirstname();
$postData['lastname'] = $entityApplicant->getLastname();
$postData['appId'] = $request->request->get('appId');
$postData['companyId'] = $request->request->get('companyId');
$postData['userType'] = $userType;
$urlToCall = $app->getCompanyGroupServerAddress() . '/add_employee_by_qr_erp';
$userFiles = $postData;
$curl = curl_init();
curl_setopt_array($curl, array(
CURLOPT_RETURNTRANSFER => 1,
CURLOPT_POST => 1,
CURLOPT_URL => $urlToCall,
CURLOPT_CONNECTTIMEOUT => 10,
CURLOPT_SSL_VERIFYPEER => false,
CURLOPT_SSL_VERIFYHOST => false,
CURLOPT_HTTPHEADER => array( // "Accept: multipart/form-data",
),
// ),
CURLOPT_POSTFIELDS => $userFiles
));
$retData = curl_exec($curl);
$errData = curl_error($curl);
curl_close($curl);
if ($errData) {
// $returnData['message']='';
} else {
$retDataObj = json_decode($retData, true);
if ($retDataObj['status'] == 'success') {
$newAccess = [
'userType' => 1,
'userTypeName' => 'Admin',
'globalId' => $entityApplicant->getApplicantId(),
'serverId' => $app->getCompanyGroupServerId(),
'serverUrl' => $app->getCompanyGroupServerAddress(),
'serverPort' => $app->getCompanyGroupServerPort(),
'systemType' => '_ERP_',
'companyId' => 1,
'appId' => $app->getAppId(),
'companyLogoUrl' => '/uploads/CompanyImage/company_image' . $request->request->get('appId') . '.png',
'companyName' => $app->getName(),
'authenticationStr' => $this->get('url_encryptor')->encrypt(json_encode(
array(
'globalId' => $entityApplicant->getApplicantId(),
'appId' => $app->getAppId(),
'authenticate' => 1,
'userType' => $userType,
'userTypeName' => UserConstants::$userTypeName[$userType]
)
)
),
'userCompanyList' => [],
];
// $token = $session->get('token');
// $em_goc = $this->getDoctrine()->getManager('company_group');
$to_set_session_data = MiscActions::GetSessionDataFromToken($em_goc, $session->get(UserConstants::USER_TOKEN, ''))['sessionData'];
$currentUserAccessListInSession = $to_set_session_data['userAccessList'];
$isAlreadyJoined = false;
foreach ($currentUserAccessListInSession as $acc) {
if (isset($acc['appId']) && $acc['appId'] == $request->request->get('appId')) {
$isAlreadyJoined = true;
break;
}
}
// if(isset($currentUserAccessListInSession[$request->request->get('appId')]))
// $isAlreadyJoined=true;
if ($isAlreadyJoined) {
return new JsonResponse([
'status' => 'false',
'message' => 'Already joined.',
'currentUserAccessListInSession' => $currentUserAccessListInSession
]);
}else{
$currentUserAccessListInSession[] = $newAccess;
$session->set('userAccessList', $currentUserAccessListInSession);
$to_set_session_data['userAccessList'] = $currentUserAccessListInSession;
$updatedToken = MiscActions::CreateTokenFromSessionData($em_goc,
$to_set_session_data,
1,
1,
1, 1
);
return new JsonResponse([
'status' => 'success',
'message' => 'User successfully added to company.',
'currentUserAccessListInSession' => $currentUserAccessListInSession
]);
}
}
}
// MiscActions::UpdateCompanyListInSession($em_goc, $entityApplicant->getApplicantId(), 1, 1, 1, $newAccess);
} else {
$company = $request->get('company');
return new JsonResponse(array(
'status' => 'error',
'message' => 'Something went wrong while onboarding.'
));
}
return new JsonResponse(array(
'status' => 'error',
'message' => 'Something went wrong while onboarding.'
));
}
public function EmployeeAddUsingQrCodeAction(Request $request)
{
$post = $request;
$em = $this->getDoctrine()->getManager('company_group');
$appid = $request->query->get('appid', $request->request->get('appid', null));
$session = $request->getSession();
$CurrentRoute = $request->attributes->get('_route');
$systemType = $this->container->hasParameter('system_type') ? $this->container->getParameter('system_type') : '_ERP_';
if ($request->isMethod('POST')) {
$userId = $request->request->get('userId', null);
if ($userId === null) {
$companyId = $request->request->get('company',1);
$email = $request->request->get('email');
$qrImage = $request->files->get('qr_image');
if (!$email) {
return new JsonResponse(['status' => 'error', 'message' => 'Invalid request'], 400);
}
if ($qrImage) {
$uploadsDir = $this->getParameter('kernel.project_dir') . '/public/uploads/';
$fileName = 'qr_' . $companyId . '_' . time() . '.png';
$qrImage->move($uploadsDir, $fileName);
$qrImagePath = $uploadsDir . $fileName;
$bodyTemplate = '@Application/email/user/applicant_confirm.html.twig';
$bodyData = [
'name' => $email,
'companyData' => $companyId,
'companyName' => $request->request->get('company'),
'userType' => 2,
'appId' => $appid,
// 'qr_code_url' => '/uploads/' . $fileName
];
$userAccessList = $session->get('userAccessList', []);
$companyName = array_map(function ($company) {
return $company['companyName'];
}, $userAccessList);
$new_mail = $this->get('mail_module');
$new_mail->sendMyMail([
'senderHash' => '_CUSTOM_',
'forwardToMailAddress' => $email,
'fromAddress' => 'accounts@ourhoneybee.eu',
'userName' => 'accounts@ourhoneybee.eu',
'password' => \ApplicationBundle\Helper\MailerConfig::buddybeePassword(),
'smtpServer' => \ApplicationBundle\Helper\MailerConfig::host(),
'smtpPort' => \ApplicationBundle\Helper\MailerConfig::port(),
'subject' => 'User Registration on HoneyBee Ecosystem under Entity ',
'toAddress' => $email,
'mailTemplate' => $bodyTemplate,
'templateData' => $bodyData,
'companyId' => $companyId,
]);
return $this->render(
'@Application/pages/dashboard/index_central_landing.html.twig',
[
'page_title' => 'Central Landing',
'companyNames' => $companyName
]
);
} else {
$userAccessList = $session->get('userAccessList', []);
$companyName = array_map(function ($company) {
return $company['companyName'];
}, $userAccessList);
return $this->render(
'@Application/pages/dashboard/index_central_landing.html.twig',
[
'page_title' => 'Central Landing',
'companyNames' => $companyName
]
);
}
} else {
if ($systemType == '_CENTRAL_') {
$userAccessList = $session->get('userAccessList', []);
$companyName = array_map(function ($company) {
return $company['companyName'];
}, $userAccessList);
$modifiedRequest = $request->duplicate(
null,
array_merge($request->request->all(), ['appId' => $request->request->get('appid')])
);
$response = $this->EmployeeAddUsingQrCodeCentralAction($modifiedRequest);
// if ($CurrentRoute == 'employee_add_by_qr_api')
{
return $response;
}
// return $this->render(
// 'ApplicationBundle:pages/dashboard:index_central_landing.html.twig',
// [
// 'page_title' => 'Central Landing',
// 'companyNames' => $companyName
// ]
// );
}
}
} else {
$company = $request->get('company');
$em_goc = $this->getDoctrine()->getManager('company_group');
$company = $em_goc->getRepository("CompanyGroupBundle\\Entity\\CompanyGroup")->findOneBy(['appId' => $request->get('appid')]);
return $this->render(
'@Application/pages/dashboard/employee_add_by_qr.html.twig',
[
'page_title' => 'Company Invitation',
'company' => $company
]
);
}
}
public function EmployeeOnboardUsingQrAction(Request $request): JsonResponse
{
$email = $request->get('email');
$isAjax = $request->isXmlHttpRequest();
$currentRoute = $request->attributes->get('_route');
if (!$email) {
if ($isAjax) {
return new JsonResponse(['status' => 'error', 'message' => 'Email is required.']);
} else {
throw $this->createNotFoundException("Email is missing.");
}
}
$em_goc = $this->getDoctrine()->getManager('company_group');
$user = $em_goc->getRepository("CompanyGroupBundle\\Entity\\EntityApplicantDetails")->findOneBy(['email' => $email]);
if (!$user) {
if ($isAjax) {
return new JsonResponse(['status' => 'error', 'message' => 'Applicant not found.']);
} else {
throw $this->createNotFoundException("Applicant not found.");
}
}
$payloadUrl = $this->generateUrl('employee_onboard_by_qr_api', [
'email' => $user->getEmail(),
'applicantId' => $user->getApplicantId(),
'employeeQr' => 1
], UrlGeneratorInterface::ABSOLUTE_URL);
if ($currentRoute === 'employee_onboard') {
return new JsonResponse([
'status' => 'success',
'data' => [
'applicantId' => $user->getApplicantId(),
'username' => $user->getUsername(),
'email' => $user->getEmail(),
'firstName' => $user->getFirstname(),
'lastName' => $user->getLastname(),
'link' => $payloadUrl
]
]);
}
if ($isAjax) {
return new JsonResponse([
'status' => 'success',
'data' => [
'id' => $user->getApplicantId(),
'email' => $user->getEmail(),
'name' => $user->getFirstname() . ' ' . $user->getLastname(),
'dob' => $user->getDob() ? $user->getDob()->format('Y-m-d') : null,
]
]);
}
$companies = $request->getSession()->get('userAccessList', []);
return $this->render('@Application/pages/dashboard/qr_onboard_result.html.twig', [
'page_title' => 'Onboarding',
'applicant' => $user,
'companies' => $companies
]);
}
public function OnboardUserToCompanyAction(Request $request)
{
$systemType = $this->container->hasParameter('system_type') ? $this->container->getParameter('system_type') : '_ERP_';
$currentRoute = $request->attributes->get('_route');
if ($systemType !== '_CENTRAL_') {
throw new \Exception("This action must be called from CENTRAL system.");
}
$email = $request->request->get('email');
$applicantId = $request->request->get('applicant_id');
$appId = $request->request->get('company_id');
$userType = $request->request->get('user_type');
if (!$email || !$appId || !$userType) {
$this->addFlash('error', 'Missing required fields.');
return new JsonResponse(['status' => 'error', 'message' => 'Missing required fields.']);
}
$em = $this->getDoctrine()->getManager('company_group');
$applicant = $em->getRepository("CompanyGroupBundle\\Entity\\EntityApplicantDetails")->findOneBy(['email' => $email]);
if (!$applicant) {
$this->addFlash('error', 'Applicant not found.');
return $this->redirectToRoute('central_landing');
}
$userAppIds = json_decode($applicant->getUserAppIds(), true);
if (!is_array($userAppIds)) {
$userAppIds = [];
}
if (!in_array($appId, $userAppIds)) {
$userAppIds[] = $appId;
$applicant->setUserAppIds(json_encode($userAppIds));
}
$userTypesByAppIds = json_decode($applicant->getUserTypesByAppIds(), true) ?: [];
$userTypesByAppIds[$appId] = array((int)$userType);
$applicant->setUserTypesByAppIds(json_encode($userTypesByAppIds));
$em->persist($applicant);
$em->flush();
$companyRepo = $this->getDoctrine()->getManager('company_group')->getRepository("CompanyGroupBundle\\Entity\\CompanyGroup");
$targetCompany = $companyRepo->findOneBy(['appId' => $appId]);
if (!$targetCompany) {
$this->addFlash('error', 'Target company not found.');
return $this->redirectToRoute('central_landing');
}
$serverList = MiscActions::getServerListById( $this->container->getParameter('database_user'), $this->container->getParameter('database_password'), $this->container->hasParameter('server_access_list') ?$this->container->getParameter('server_access_list') :[] );
$serverId = $targetCompany->getCompanyGroupServerId();
$appId = $targetCompany->getAppId();
if (!isset($serverList[$serverId])) {
$this->addFlash('error', 'Server configuration not found.');
return $this->redirectToRoute('central_landing');
}
// Find correct companyId from session userAccessList using appId
$session = $request->getSession();
$userAccessList = $session->get('userAccessList', []);
$resolvedCompanyId = null;
foreach ($userAccessList as $access) {
if (isset($access['appId']) && $access['appId'] == $appId) {
$resolvedCompanyId = $access['companyId'];
break;
}
}
if (!$resolvedCompanyId) {
$this->addFlash('error', 'Could not resolve companyId from session for the given appId.');
return $this->redirectToRoute('central_landing');
}
$imagePath = $this->container->getParameter('kernel.root_dir') . '/../web/' . $applicant->getImage();
$imageFile = null;
if ($applicant->getImage() && file_exists($imagePath)) {
$mime = mime_content_type($imagePath);
$name = pathinfo($imagePath, PATHINFO_BASENAME);
$imageFile = new \CURLFile($imagePath, $mime, $name);
}
// Prepare userData
$userData = [];
$getters = array_filter(get_class_methods($applicant), function ($method) {
return strpos($method, 'get') === 0;
});
foreach ($getters as $getter) {
if (in_array($getter, ['getCreatedAt', 'getUpdatedAt', 'getImage'])) continue;
$value = $applicant->$getter();
$userData[$getter] = $value instanceof \DateTime ? $value->format('Y-m-d') : $value;
}
$userData['getUserAppIds'] = json_encode($userAppIds);
$userData['getUserTypesByAppIds'] = json_encode($userTypesByAppIds);
$postFields = [
'userData' => json_encode([$userData]),
'appIds' => $appId,
'userIds' => $applicantId,
'companyId' => $resolvedCompanyId,
'userType' => $userType,
'imageFile' => $imageFile
];
if ($imageFile) {
$postFields['file_' . $applicant->getApplicantId()] = $imageFile;
}
$url = $serverList[$serverId]['absoluteUrl'] . '/OnBoardCentralUserToErp';
$curl = curl_init();
curl_setopt_array($curl, [
CURLOPT_RETURNTRANSFER => 1,
CURLOPT_POST => 1,
CURLOPT_URL => $url,
CURLOPT_POSTFIELDS => $postFields,
CURLOPT_CONNECTTIMEOUT => 10,
CURLOPT_SSL_VERIFYPEER => false,
CURLOPT_SSL_VERIFYHOST => false,
]);
$response = curl_exec($curl);
$curlError = curl_error($curl);
curl_close($curl);
$responseObj = json_decode($response, true);
if ($currentRoute === 'employee_onboard_api') {
return new JsonResponse(
$responseObj
);
}
if (!empty($responseObj['success'])) {
$this->addFlash('success', 'Applicant onboarded and synced to ERP.');
} else {
$this->addFlash('warning', 'Onboarded, but ERP sync may have failed. Check logs.');
}
return $this->redirectToRoute('central_landing');
}
public function OnBoardCentralUserToErpAction(Request $request)
{
$systemType = $this->container->hasParameter('system_type') ? $this->container->getParameter('system_type') : '_ERP_';
if ($systemType === '_CENTRAL_') {
return new JsonResponse(['success' => false, 'message' => 'Invalid system type.']);
}
$userDataJson = $request->get('userData');
$userDataList = json_decode($userDataJson, true);
if (!$userDataList || !is_array($userDataList)) {
return new JsonResponse(['success' => false, 'message' => 'Invalid or missing userData.']);
}
$userData = $userDataList[0] ?? null;
if (!$userData || empty($userData['getApplicantId'])) {
return new JsonResponse(['success' => false, 'message' => 'Missing applicant ID.']);
}
$em_goc = $this->getDoctrine()->getManager('company_group');
$globalId = $userData['getApplicantId'];
$appIds = $request->get('appIds');
$userIds = $request->get('userIds');
$companyId = $request->get('companyId');
$company = $em_goc->getRepository("CompanyGroupBundle\\Entity\\CompanyGroup")->findOneBy(['appId' => $appIds]);
if (!$company) {
return new JsonResponse(['success' => false, 'message' => 'Target company not found.']);
}
$connector = $this->container->get('application_connector');
$connector->resetConnection(
'default',
$company->getDbName(),
$company->getDbUser(),
$company->getDbPass(),
$company->getDbHost(),
true
);
$em = $this->getDoctrine()->getManager();
$user = $em->getRepository('ApplicationBundle\\Entity\\SysUser')->findOneBy([
'globalId' => $globalId
]);
if (!$user) {
$user = new \ApplicationBundle\Entity\SysUser();
$user->setGlobalId($globalId);
} else {
return new JsonResponse([
'status' => 'info',
'message' => 'User is already onboarded',
'userId' => $user->getUserId(),
'redirectUrl' => 'dashboard'
]);
}
$setterMap = [
'getUsername' => 'setUserName',
'getEmail' => 'setEmail',
'getPassword' => 'setPassword',
'getDeviceId' => 'setDeviceId',
'getDeviceIdLockedFlag' => 'setDeviceIdLockedFlag',
'getLockDeviceIdOnNextLoginFlag' => 'setLockDeviceIdOnNextLoginFlag',
'getOAuthUniqueId' => 'setOAuthUniqueId',
'getOAuthEmail' => 'setOAuthEmail',
'getSyncFlag' => 'setSyncFlag',
'getFirstname' => 'setName',
];
$user->setUserAppId($appIds);
$user->setUserAppIdList(json_encode([$appIds]));
$user->setStatus(1);
foreach ($setterMap as $getter => $setter) {
if (isset($userData[$getter]) && method_exists($user, $setter)) {
$user->$setter($userData[$getter]);
}
}
$fullName = trim(($userData['getFirstname'] ?? '') . ' ' . ($userData['getLastname'] ?? ''));
if (!empty($fullName)) {
$user->setName($fullName);
}
$imagePathToSet = '';
$uploadedFile = $request->files->get('file_' . $globalId);
if ($uploadedFile) {
$uploadDir = $this->getParameter('kernel.project_dir') . '/web/uploads/UserImage/';
if (!file_exists($uploadDir)) {
mkdir($uploadDir, 0777, true);
}
$filename = 'user_' . $globalId . '.' . $uploadedFile->guessExtension();
$uploadedFile->move($uploadDir, $filename);
$imagePathToSet = 'uploads/UserImage/' . $filename;
$user->setImage($imagePathToSet);
}
$user->setUserType(1);
$em->persist($user);
$em->flush();
$employee = $em->getRepository('ApplicationBundle\\Entity\\Employee')->findOneBy([
'userId' => $user->getUserId()
]);
if (!$employee) {
$employee = new \ApplicationBundle\Entity\Employee();
$employee->setUserId($user->getUserId());
$employee->setDateOfAddition(new \DateTime());
}
$employee->setFirstName($userData['getFirstname'] ?? null);
$employee->setLastName($userData['getLastname'] ?? null);
$employee->setName(trim(($userData['getFirstname'] ?? '') . ' ' . ($userData['getLastname'] ?? '')));
$employee->setEmail($userData['getEmail'] ?? null);
$employee->setEmmContact($userData['getEmmContact'] ?? null);
$employee->setCurrentAddress($userData['getCurrAddr'] ?? null);
$employee->setPermanentAddress($userData['getPermAddr'] ?? null);
$employee->setCompanyId((int)$companyId);
$employee->setDepartmentId($userData['getDept'] ?? null);
$employee->setBranchId($userData['getBranch'] ?? null);
$employee->setPositionId($userData['getDesg'] ?? null);
$employee->setSupervisorId($userData['getSupervisor'] ?? null);
if (!empty($imagePathToSet)) {
$employee->setImage($imagePathToSet);
}
$employee->setStatus("1");
$employee->setLastModifiedDate(new \DateTime());
$em->persist($employee);
$em->flush();
$employeeDetails = $em->getRepository('ApplicationBundle\\Entity\\EmployeeDetails')
->findOneBy(['userId' => $user->getUserId()]);
if (!$employeeDetails) {
$employeeDetails = new \ApplicationBundle\Entity\EmployeeDetails();
$employeeDetails->setUserId($user->getUserId());
}
$employeeDetails->setId($employee->getEmployeeId());
$employeeDetails->setFirstname($userData['getFirstname'] ?? null);
$employeeDetails->setLastname($userData['getLastname'] ?? null);
$employeeDetails->setUsername($userData['getUsername'] ?? null);
$employeeDetails->setEmail($userData['getEmail'] ?? null);
$employeeDetails->setPassword($userData['getPassword'] ?? null);
$employeeDetails->setNid($userData['getNid'] ?? null);
$employeeDetails->setSex($userData['getSex'] ?? null);
$employeeDetails->setFather($userData['getFather'] ?? null);
$employeeDetails->setMother($userData['getMother'] ?? null);
$employeeDetails->setSpouse($userData['getSpouse'] ?? null);
$employeeDetails->setChild1($userData['getChild1'] ?? null);
$employeeDetails->setChild2($userData['getChild2'] ?? null);
$employeeDetails->setPhone($userData['getPhone'] ?? null);
$employeeDetails->setOfficialPhone($userData['getOfficailPhone'] ?? null);
$employeeDetails->setCurrAddr($userData['getCurrAddr'] ?? null);
$employeeDetails->setPermAddr($userData['getPermAddr'] ?? null);
$employeeDetails->setEmmContact($userData['getEmmContact'] ?? null);
if (!empty($userData['getDob'])) {
$employeeDetails->setDob(new \DateTime($userData['getDob']));
}
if (!empty($userData['getJoiningDate'])) {
$employeeDetails->setJoiningDate(new \DateTime($userData['getJoiningDate']));
}
if (!empty($userData['getEmpValidTill'])) {
$employeeDetails->setEmpValidTill(new \DateTime($userData['getEmpValidTill']));
}
if (!empty($userData['getTinValidTill'])) {
$employeeDetails->setTinValidTill(new \DateTime($userData['getTinValidTill']));
}
if (!empty($userData['getMedInsValidTill'])) {
$employeeDetails->setMedInsValidTill(new \DateTime($userData['getMedInsValidTill']));
}
$employeeDetails->setEmpType($userData['getEmpType'] ?? null);
$employeeDetails->setEmpCode($userData['getEmpCode'] ?? null);
$employeeDetails->setEmployeeLevel($userData['getEmployeeLevel'] ?? null);
$employeeDetails->setTin($userData['getTin'] ?? null);
$employeeDetails->setSkill($userData['getSkill'] ?? null);
$employeeDetails->setEducationData($userData['getEducationData'] ?? null);
$employeeDetails->setWorkExperienceData($userData['getWorkExperienceData'] ?? null);
$employeeDetails->setApplicationText($userData['getApplicationText'] ?? null);
$employeeDetails->setCurrentEmployment($userData['getCurrentEmployment'] ?? null);
$employeeDetails->setEmergencyContactNumber($userData['getEmergencyContactNumber'] ?? null);
$employeeDetails->setPostalCode($userData['getPostalCode'] ?? null);
$employeeDetails->setCountry($userData['getCountry'] ?? null);
if (!empty($imagePathToSet)) {
$employeeDetails->setImage($imagePathToSet);
}
$em->persist($employeeDetails);
$em->flush();
$uploadedFile = $request->files->get('file_' . $globalId);
return new JsonResponse([
'success' => true,
'globalId' => $globalId,
'appIds' => $appIds,
'userIds' => $userIds,
'hasFile' => $uploadedFile !== null,
]);
}
public function getIndustryListForAppAction()
{
$industryList = GeneralConstant::$industryList;
return new JsonResponse($industryList);
}
public function getFeatureListAction()
{
$featureList = GeneralConstant::$featureList;
return new JsonResponse($featureList);
}
public function chooseYourPlanAction()
{
$plan = GeneralConstant::$chooseYourPlan;
return new JsonResponse($plan);
}
// public function getLogindataFromTokenAction(Request $request)
// {
// $em_goc = $this->getDoctrine()->getManager('company_group');
// $sessionData = MiscActions::GetSessionDataFromToken($em_goc, $request->query->get('hbeeSessionToken'))['sessionData'];
// return new JsonResponse($sessionData);
// $sessionDataa = MiscActions::GetSessionDataFromToken($em_goc, $request->query->get('hbeeSessionToken'))['sessionData'];
// return new JsonResponse($sessionDataa);
// }
public function getLogindataFromTokenAction(Request $request)
{
$em_goc = $this->getDoctrine()->getManager('company_group');
$token = $request->query->get('hbeeSessionToken');
if (!$token) {
return new JsonResponse(['error' => 'Missing session token'], 400);
}
$sessionResult = MiscActions::GetSessionDataFromToken($em_goc, $token);
if (!$sessionResult || !isset($sessionResult['sessionData'])) {
return new JsonResponse(['error' => 'Invalid session token or session not found'], 401);
}
$sessionData = $sessionResult['sessionData'];
return new JsonResponse($sessionData);
}
public function packageDetailsAction()
{
$packageDetails = GeneralConstant::$packageDetails;
return new JsonResponse([
'success' => true,
'message' => 'Data fetched successfully.',
'data' => $packageDetails
]);
}
public function employeeRangeAction()
{
$employeeRangeForApp = GeneralConstant::$employeeRange;
return new JsonResponse($employeeRangeForApp);
}
public function paymentMethodAction()
{
$paymentMethod = GeneralConstant::$paymentMethod;
return new JsonResponse($paymentMethod);
}
public function companyTypeAction()
{
$companyType = GeneralConstant::$companyType;
return new JsonResponse(
[
'status' => 'success',
'message' => 'Company types retrieved successfully.',
'data' => $companyType,
]
);
}
public function getCountryListAction()
{
$em_goc = $this->getDoctrine()->getManager('company_group');
$countryList = $em_goc->getRepository('CompanyGroupBundle\\Entity\\EntityCountries')
->createQueryBuilder('C')
->select('C.CountryId', 'C.code', 'C.nameEn')
->getQuery()
->getArrayResult();
// $formattedList = array_map(function ($country) {
// return [
// 'countryId' => $country['countryId'],
// 'countryName' => $country['nameEn'],
// 'countryCode' => $country['code'],
// ];
// }, $countryList);
return new JsonResponse([
'status' => true,
'message' => 'Country list fetched successfully',
'data' => $countryList,
]);
}
public function spotLightSearchAction(Request $request,$queryStr = ''){
$em = $this->getDoctrine()->getManager();
$data = [];
$data_by_id = [];
$session = $request->getSession();
$entityDetails = GeneralConstant::$Entity_list_details;
if ($queryStr == '_DEFAULT_')
$queryStr = '';
if ($request->request->has('query') && $queryStr == '')
$queryStr = $request->request->get('query');
if ($queryStr == '_DEFAULT_')
$queryStr = '';
$queryStr = str_ireplace('_FSLASH_', '/', $queryStr);
//module
$filterQryForCriteria = "select * from sys_module where 1=1 and module_name like '%" . $queryStr . "%' limit 10";
$get_kids_sql = $filterQryForCriteria;
$stmt = $em->getConnection()->fetchAllAssociative($get_kids_sql);
$get_kids = $stmt;
$productId = 0;
$absoluteUrl = $this->generateUrl('dashboard', [], UrlGenerator::ABSOLUTE_URL);
if (!empty($get_kids)) {
foreach ($get_kids as $product) {
$router = $this->container->get('router');
$routeName = $product['module_route'];
if ($router->getRouteCollection()->get($routeName)) {
$pa = array();
$pa['id'] = $product['module_id'];
$pa['name'] = $product['module_name'];
$pa['type'] = 'module';
// $pa['url'] = $absoluteUrl . '/' . $product['module_route'];
$pa['url'] = $this->generateUrl($product['module_route'], [], UrlGenerator::ABSOLUTE_URL);
$data[] = $pa;
} else {
// treat as non-route
// $pa['url'] = $routeName; // or custom fallback
}
}
}
// supplier
$get_kids = $em->getConnection()->fetchAllAssociative(
"select * from acc_suppliers where supplier_name like '%" . $queryStr . "%' limit 10"
);
$url = $this->generateUrl('supplier_profile');
if (!empty($get_kids)) {
foreach ($get_kids as $product) {
$pa = array();
$pa['id'] = $product['supplier_id'];
$pa['name'] = $product['supplier_name'];
$pa['type'] = 'supplier';
$pa['url'] = rtrim($absoluteUrl, '/') . '/' . ltrim($url, '/') . '?ex_s_id=' . $product['supplier_id'];
$data[] = $pa;
}
}
// client
$get_kids = $em->getConnection()->fetchAllAssociative(
"select * from acc_clients where client_name like '%" . $queryStr . "%' limit 10"
);
$url = $this->generateUrl('client_profile');
if (!empty($get_kids)) {
foreach ($get_kids as $product) {
$pa = array();
$pa['id'] = $product['client_id'];
$pa['name'] = $product['client_name'];
$pa['type'] = 'client';
$pa['url'] = rtrim($absoluteUrl, '/') . '/' . trim($url, '/') . '/' . $product['client_id'];
$data[] = $pa;
}
}
// document (approval)
$get_kids = $em->getConnection()->fetchAllAssociative(
"select * from approval where document_hash like '%" . $queryStr . "%' limit 10"
);
if (!empty($get_kids)) {
foreach ($get_kids as $product) {
try {
$viewUrl = $this->generateUrl(
$entityDetails[$product['entity']]['entity_view_route_path_name']
);
$pa = array();
$pa['id'] = $product['id'];
$pa['name'] = $product['document_hash'];
$pa['type'] = 'document';
$pa['url'] = rtrim($absoluteUrl, '/') . '/' . trim($viewUrl, '/') . '/' . $product['entity_id'];
$data[] = $pa;
} catch (\Exception $e) { /* skip if route undefined */ }
}
}
return new JsonResponse(array(
'success' => true,
'data' => $data,
));
}
public function getMlDataAction()
{
$data = GeneralConstant::$dataFromMl;
return new JsonResponse($data);
}
public function UploadDocumentsAction(Request $request)
{
$session = $request->getSession();
$em = $this->getDoctrine()->getManager();
return $this->render(
'@Application/pages/dashboard/upload_documents.html.twig',
array(
'page_title' => 'Upload Documents',
)
);
}
}

src/ApplicationBundle/Controller/DashboardController.php line 2126