Symfony Profiler

<?php
namespace App\Controller\ProjectManagement;
use App\Entity\Client;
use App\Entity\ClientFeedback;
use App\Entity\User;
use App\Entity\Project;
use App\Entity\Department;
use App\Entity\ProjectDeliverable;
use App\Entity\ProjectDeliverableFile;
use App\Entity\ProjectLeadStatus;
use App\Service\LogService;
use App\Service\UploadService;
use App\Service\UtilsService;
use Symfony\Component\Stopwatch\Stopwatch;
use Doctrine\ORM\ORMException;
use App\Service\ProjectService;
use App\Service\WebhookService;
use App\Service\CurrencyService;
use App\Entity\ProjectSalesOrder;
use App\Entity\RevenuePlanning;
use App\Form\ClientFeedbackType;
use App\Form\ProjectClientFeedbackTemplateType;
use App\Form\ProjectClientPersonInChargeType;
use App\Form\ProjectDeliverableType;
use App\Form\ProjectManagement\ClientContactType;
use App\Form\ProjectManagement\ProjectType;
use App\Form\ProjectManagement\SalesOrderType;
use App\Form\ProjectManagement\ProjectNewLeadType;
use App\Form\ProjectManagement\ProjectNewConfirmedType;
use App\Form\ProjectRevenuePlanningType;
use App\Form\MattermostType;
use App\Repository\TaskRepository;
use App\Repository\UserRepository;
use App\Repository\ProjectRepository;
use App\Repository\TimeSpentRepository;
use App\Repository\SalesOrderRepository;
use App\Repository\InvoiceRepository;
use Doctrine\ORM\OptimisticLockException;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use App\Repository\VendorInvoiceRepository;
use Psr\Log\LoggerInterface;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use App\Repository\ProjectSalesOrderRepository;
use Symfony\Component\Routing\Annotation\Route;
use Symfony\Component\HttpFoundation\JsonResponse;
use App\Repository\ProjectAllocatedHoursRepository;
use App\Repository\VendorQuotationPlanningRepository;
use Symfony\Component\Serializer\SerializerInterface;
use App\Form\ProjectManagement\ProjectNewClientType;
use App\Form\ProjectManagement\ProjectNewInternalType;
use Symfony\Contracts\Translation\TranslatorInterface;
use App\Repository\ChecklistRepository;
use App\Repository\ClientContactRepository;
use App\Repository\ClientFeedbackRepository;
use App\Repository\ClientFeedbackTemplateRepository;
use App\Repository\IndustryClassificationRepository;
use App\Repository\ProjectClassificationRepository;
use App\Repository\ProjectDeliverableRepository;
use App\Repository\RevenuePlanningRepository;
use App\Repository\VendorContactRepository;
use App\Service\ChecklistService;
use App\Service\MattermostService;
use App\Service\FileService;
use App\Service\GoogleDriveService;
use App\Service\S3Service;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\DependencyInjection\ParameterBag\ParameterBagInterface;
use Symfony\Component\HttpFoundation\RedirectResponse;
use Symfony\Component\HttpFoundation\RequestStack;
use Symfony\Component\Security\Core\Security;
class ProjectController extends AbstractController
{
#[Route(path: '/project', name: 'app_project')]
public function index(): Response
{
return $this->render('private/project-management/project/project-index.html.twig', [
'controller_name' => 'ProjectController',
]);
}
/**
* @param int $_id
* @param ProjectRepository $projectRepository
* @param TimeSpentRepository $timeSpentRepository
* @param VendorInvoiceRepository $vendorInvoiceRepository
* @param SalesOrderRepository $projectSalesOrderRepository
* @param VendorQuotationPlanningRepository $vendorQuotationPlanningRepository
* @param TranslatorInterface $translator
* @param UserRepository $userRepository
* @return Response
* @throws \Exception
* @throws \GuzzleHttp\Exception\GuzzleException
*/
#[Route(path: '/project-management/project/view/{_id}', defaults: ['_id' => 0], name: 'view_project')]
public function viewProject(int $_id, CurrencyService $currencyService, ProjectRepository $projectRepository, TimeSpentRepository $timeSpentRepository, VendorInvoiceRepository $vendorInvoiceRepository, ProjectSalesOrderRepository $projectSalesOrderRepository, VendorQuotationPlanningRepository $vendorQuotationPlanningRepository, TranslatorInterface $translator, UserRepository $userRepository, RevenuePlanningRepository $revenuePlanningRepository, ProjectService $projectService, ClientFeedbackTemplateRepository $clientFeedbackTemplateRepository): Response
{
$user = $this->getUser();
$project = $projectRepository->find($_id);
$formProject = $this->createForm(ProjectType::class, $project);
// $formProject->add('clientContact', ProjectClientPersonInChargeType::class);
$employeeTimeRecords = $timeSpentRepository->getEmployeeTimeRecord($_id);
$totalTasks = $timeSpentRepository->getTotalTasks($_id);
$totalHours = $timeSpentRepository->getTotalHours($_id);
$totalHoursAll = 0;
$totalCostAll = 0;
$projectStartDate = $project && $project->getStartDate() != null ? $project->getStartDate()->format('Y-m-d') : null;
$projectEndDate = $project && $project->getEndDate() != null ? $project->getEndDate()->format('Y-m-d') : null;
$departments = $timeSpentRepository->getDepartmentsForProject($_id, $projectStartDate, $projectEndDate);
$salesOrdersListForDropDown = $projectSalesOrderRepository->findByPage(0, 9999, "", 'ASC', null, 0, 0);
$salesOrdersListForProject = $projectSalesOrderRepository->findByPage(0, 9999, "", 'ASC', null, $_id, 0);
$monthlyRevenues = $projectService->monthlyRevenues($project);
$monthlyRevenuesRecognition = $projectService->monthlyRevenues($project, false);
// dump($project);
// dd($projectRepository->findInvoices($project));
//dd($salesOrdersList);
//$totalManpowerCost = 0;
for ($i = 0; $i < sizeof($departments); $i++) {
$employeeSummaryList = $userRepository->findTaskSummary($_id, $departments[$i]['id'], $projectStartDate, $projectEndDate);
$i2 = 0;
foreach ($employeeSummaryList as $employee) {
$projectMember = $project->getProjectMember($employee['id']);
if ($projectMember) {
$employeeSummaryList[$i2]['hourly'] = $projectMember->getHourlyRateUsd();
$i2++;
}
}
$departments[$i]['employeeSummaryList'] = $employeeSummaryList;
$tmpHours = 0;
$tmpCost = 0;
for ($j = 0; $j < sizeof($employeeSummaryList); $j++) {
$tmpHours = $tmpHours + $employeeSummaryList[$j]['hours'];
$tmpCost = $tmpCost + $employeeSummaryList[$j]['cost'];
}
//$totalManpowerCost += $tmpCost;
$departments[$i]['totalCost'] = $tmpCost;
$departments[$i]['totalHours'] = $tmpHours;
}
$projectDeliverable = null;
if(!$project->getProjectDeliverable()){
$projectDeliverable = new ProjectDeliverable();
$projectDeliverable->setCreatedAt(new \DateTime());
$project->setProjectDeliverable($projectDeliverable);
$entityManager = $this->getDoctrine()->getManager();
$entityManager->flush();
}else{
$projectDeliverable = $project->getProjectDeliverable();
}
$generetedRevenuePlanning = $projectService->generateRevenuePlanning($project);
if($generetedRevenuePlanning['status'] == 'OK'){
$project = $generetedRevenuePlanning['data'];
}
$formRevenuePlanning = $this->createForm(ProjectRevenuePlanningType::class, $project);
//$manpowerCost = $totalManpowerCost;
if (!$project) {
throw new \Exception($translator->trans('messages.security.forbidden'));
} else {
if (count($departments) > 0) {
foreach ($departments as $department) {
$totalHoursAll += $department['totalHours'];
$totalCostAll += $department['totalCost'];
}
}
return $this->render('private/project-management/project/view-project.html.twig', [
'project' => $project,
'monthlyRevenues' => $monthlyRevenues,
'monthlyRevenuesRecognition' => $monthlyRevenuesRecognition,
'formProject' => $formProject->createView(),
'totalTasks' => $totalTasks,
'totalHours' => $totalHours,
'totalHoursAll' => $totalHoursAll,
'totalCostAll' => $totalCostAll,
'timeRecordsByDepartments' => $departments,
'soListDd' => $salesOrdersListForDropDown,
'soList' => $salesOrdersListForProject,
'formRevenuePlanning' => $formRevenuePlanning->createView(),
'generateRevenuePlanning' => $generetedRevenuePlanning,
'projectDeliverable' => $projectDeliverable
]);
}
}
/**
* @param int $_id
* @param ProjectRepository $projectRepository
* @param InvoiceRepository $invoiceRepository
* @return Response
* @throws \Exception
* @throws \GuzzleHttp\Exception\GuzzleException
*/
#[Route(path: '/ajax/project/refresh-overview/{_id}/{_section}', defaults: ['_id' => 0, '_section' => ''], name: 'ajax_project_overview')]
public function ajaxProjectOverview(int $_id, string $_section, CurrencyService $currencyService, ProjectRepository $projectRepository, TimeSpentRepository $timeSpentRepository, VendorInvoiceRepository $vendorInvoiceRepository, SalesOrderRepository $salesOrderRepository, VendorQuotationPlanningRepository $vendorQuotationPlanningRepository, TranslatorInterface $translator, ProjectService $projectService, UserRepository $userRepository): Response
{
$project = $projectRepository->find($_id);
$result['status'] = 'OK';
$result['section'] = [];
//$totalTasks = $timeSpentRepository->getTotalTasks($_id);
$totalHours = $timeSpentRepository->getTotalHours($_id);
$totalHoursAll = 0;
$totalCostAll = 0;
$projectStartDate = $project && $project->getStartDate() != null ? $project->getStartDate()->format('Y-m-d') : null;
$projectEndDate = $project && $project->getEndDate() != null ? $project->getEndDate()->format('Y-m-d') : null;
$departments = $timeSpentRepository->getDepartmentsForProject($_id, $projectStartDate, $projectEndDate);
for ($i = 0; $i < sizeof($departments); $i++) {
$employeeSummaryList = $userRepository->findTaskSummary($_id, $departments[$i]['id'], $projectStartDate, $projectEndDate);
$departments[$i]['employeeSummaryList'] = $employeeSummaryList;
$tmpHours = 0;
$tmpCost = 0;
for ($j = 0; $j < sizeof($employeeSummaryList); $j++) {
$tmpHours = $tmpHours + $employeeSummaryList[$j]['hours'];
$tmpCost = $tmpCost + $employeeSummaryList[$j]['cost'];
}
$departments[$i]['totalCost'] = $tmpCost;
$departments[$i]['totalHours'] = $tmpHours;
}
if (count($departments) > 0) {
foreach ($departments as $department) {
$totalHoursAll += $department['totalHours'];
$totalCostAll += $department['totalCost'];
}
}
// Response
if ($_section == null || $_section == 'infoPayment') {
$result['section']['infoPayment'] = $this->renderView('private/project-management/project/components/info-project-payment.html.twig', [
'project' => $project,
]);
}
if ($_section == null || $_section == 'project') {
$result['section']['project'] = $this->renderView('private/project-management/project/components/overview-detail-project.html.twig', [
'project' => $project,
'totalHours' => $totalHours
]);
}
if ($_section == null || $_section == 'salesOrder') {
$result['section']['salesOrder'] = $this->renderView('private/project-management/sales-order/components/overview-sales-order.html.twig', [
'project' => $project
]);
}
if ($_section == null || $_section == 'employee') {
$result['section']['employee'] = $this->renderView('private/project-management/employee/components/overview-time-record.html.twig', [
'timeRecordsByDepartments' => $departments,
'totalHoursAll' => $totalHoursAll,
'totalCostAll' => $totalCostAll
]);
}
if ($_section == null || $_section == 'vendor') {
$result['section']['vendor'] = $this->renderView('private/project-management/vendor-quotation-planning/components/overview-vendor-quotation-planning.html.twig', [
'project' => $project
]);
}
if ($_section == null || $_section == 'revenue') {
$monthlyRevenues = $projectService->monthlyRevenues($project);
$monthlyRevenuesRecognition = $projectService->monthlyRevenues($project, false);
$result['section']['revenue'] =$this->renderView('private/project-management/project-revenue-planning/components/overview-revenue-planning.html.twig', [
'project' => $project,
'monthlyRevenues' => $monthlyRevenues,
'monthlyRevenuesRecognition' => $monthlyRevenuesRecognition
]);
$result['section']['revenueDetail'] =$this->renderView('private/project-management/project-revenue-planning/components/detail-revenue-planning.html.twig', [
'project' => $project,
'monthlyRevenues' => $monthlyRevenues,
'monthlyRevenuesRecognition' => $monthlyRevenuesRecognition
]);
}
return new JsonResponse($result);
}
/**
* @param int $_id
* @param ProjectRepository $projectRepository
* @return Response
*/
#[Route(path: '/ajax/project-revenue/refresh', name: 'ajax_project_revenue_refresh')]
public function ajaxProjectRevenue(ProjectRepository $projectRepository, ProjectService $projectService, Request $request): Response
{
$_id = $request->get("id");
$project = $projectRepository->find($_id);
$generateRevenuePlanning = $projectService->generateRevenuePlanning($project);
$monthlyRevenues = $projectService->monthlyRevenues($project);
$monthlyRevenuesRecognition = $projectService->monthlyRevenues($project, false);
if($generateRevenuePlanning['status'] == 'OK'){
$project = $generateRevenuePlanning['data'];
}
$result['projectRevenueDetail'] = $this->renderView('private/project-management/project-revenue-planning/components/detail-revenue-planning.html.twig', [
'project' => $project,
'monthlyRevenues' => $monthlyRevenues,
'monthlyRevenuesRecognition' => $monthlyRevenuesRecognition,
'waitForPage' => false
]);
// $result['projectRevenueOverview'] = $this->renderView('private/project-management/project-revenue-planning/components/overview-revenue-planning.html.twig', [
// 'project' => $project,
// 'waitForPage' => false
// ]);
$result['status'] = 'OK';
return new JsonResponse($result);
}
/**
* @param ProjectRepository $projectRepository
* @param Request $request
* @return Response
*/
#[Route(path: '/ajax/project/list', name: 'ajax_list_projects')]
public function ajaxListProjects(ProjectRepository $projectRepository, Request $request, LoggerInterface $logger = null): Response
{
// Example params:
// page=0&limit=20&keyword=t&orderBy=name&order=asc&waitForPage=true&client=1
$user = $this->getUser();
$page = intval($request->query->get('page') - 1);
if ($page < 0) $page = 0;
$limit = intval($request->query->get('limit'));
if ($limit === 0) $limit = 20;
$keyword = $request->get('keyword');
$order = $request->get('order');
$orderBy = $request->get('orderBy');
$startDate = $request->get('start');
$endDate = $request->get('end');
$client = $request->get('client');
$status = $request->get('status');
$type = $request->get('type');
$retainer = $request->get('retainer');
$department = $request->get('department');
$project = $request->get('project');
$projectClassification = $request->get('projectType');
$industryClassification = $request->get('projectIndustry');
$assignedUser = $request->get('assignedUser');
$pic = $request->get('pic');
$period = $request->get('period');
$year = $request->get('year');
$picClient = $request->get('picClient');
$vendor = $request->get('vendor');
$picVendor = $request->get('picVendor');
$waitForPage = $request->query->get('waitForPage');
$waitForPage === null ? $result['waitForPage'] = 'false' : $result['waitForPage'] = $waitForPage;
// $stopwatch = new Stopwatch();
// $stopwatch->start('query');
// $logger->error('Start query');
$projectsList = $projectRepository->findByPage($page, $limit, $keyword, $order, $orderBy, $startDate, $endDate, $type, $client, $status, $project, $department, $retainer, $assignedUser, false, $pic, $period, $year, $picClient, $vendor, $picVendor, $projectClassification, $industryClassification);
$totalCount = $projectRepository->countByPage($keyword, $startDate, $endDate, $type, $client, $status, $project, $department, $retainer, $assignedUser, false, $pic, $period, $year, $picClient, $vendor, $picVendor, $projectClassification, $industryClassification);
// $event = $stopwatch->stop('query');
// $logger->error($event);
// $stopwatch = new Stopwatch();
// $stopwatch->start('render');
// $logger->error('Start render2');
$body = $this->renderView('private/project-management/project/components/list-project.html.twig', [
'projectsList' => $projectsList,
'type' => $type
]);
// $event = $stopwatch->stop('render');
// $logger->error($event);
$result['content'] = [
'html' => $body,
'total' => $totalCount
];
// $result['projectDiagram'] = $this->renderView('private/project-management/project/components/diagram-project.html.twig', [
// 'projectsList' => $projectsList
// ]);
$result['status'] = 'OK';
// return new Response($body);
return new JsonResponse($result);
}
/**
* @param ProjectRepository $projectRepository
* @param Request $request
* @param LogService $log
* @return Response
*/
#[Route(path: '/ajax/project/persist', name: 'ajax_persist_project')]
public function ajaxPersistProject(ProjectRepository $projectRepository, UploadService $uploadService, ParameterBagInterface $params, Request $request, ProjectService $projectService, LogService $log, WebhookService $webhook, MattermostService $mattermostService, RequestStack $requestStack, ChecklistService $checklistService, ChecklistRepository $checklistRepository): Response
{
// $baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$baseUrl = $requestStack->getCurrentRequest()->getSchemeAndHttpHost();
$currentUrl = $requestStack->getCurrentRequest()->getUri();
$id = intval($request->get('id'));
$type = $request->get('type');
$user = $this->getUser();
$client = $request->get('client');
$project = new Project();
$result['status'] = 'OK';
$result['type'] = $type;
if ($id > 0) {
$project = $projectRepository->find($id);
$oldData = clone $project;
$oldChecklist = $oldData->getChecklists()->toArray();
$result['id'] = $project->getId();
$result['update'] = true;
$type = null;
$result['type'] = $type;
} else {
$oldData = null;
$project->setLead(1);
// if($project->getType() != 'lead') $project->setStatus('In-Planning');
}
// $_type = $type;
// if (is_array($request->request->get('project'))) {
// $_type = '';
// }
switch ($type) {
// case 'lead':
// $form = $this->createForm(ProjectNewLeadType::class, $project);
// break;
case 'client':
$form = $this->createForm(ProjectNewClientType::class, $project);
break;
case 'internal':
$form = $this->createForm(ProjectNewInternalType::class, $project);
break;
// case 'confirmed':
// $form = $this->createForm(ProjectNewConfirmedType::class, $project);
// break;
default:
$form = $this->createForm(ProjectType::class, $project);
break;
}
$form->handleRequest($request);
$formok = true;
if ($form->isSubmitted() && !$form->isValid()) {
$result['status'] = 'ERROR';
foreach ($form->getErrors(true) as $key => $error) {
$result['message'][$key] = $error->getMessage();
}
$formok = false;
}
/*if(!is_array($request->request->get('project'))){
$formok=false;
}*/
$checklistsForm = null;
if ($form->isSubmitted() && $form->isValid()) {
$sendHookConfirmedProject = false;
if ($type != 'internal' && $form->get('lead')->getData() == 9) {
$project->setStartDate(null);
$project->setEndDate(null);
}
if ($type === 'internal' || $form->get('lead')->getData() !== 8) {
$project->setWinDate(null);
} elseif ($form->get('winDate')->getData() == null) {
$project->setWinDate(new \DateTime());
}
if ($id === 0) {
// if ($projectRepository->findDuplicate($project->getName()) > 0) {
// $result['status'] = 'ERROR';
// $result['message'] = 'Project with that name already exist!';
// return new JsonResponse($result);
// }
if ($type == 'internal') {
$projectType = 'INTERNAL';
} else {
$projectType = $form->get('lead')->getData() == 8 ? 'CONFIRMED' : 'LEAD';
}
$project->setCurrency($form->get('currency')->getData());
$result['projectType'] = $projectType;
$project->setCreatedBy($user);
$project->setCreatedAt(new \DateTime());
$project->setType($projectType);
$projectDeliverable = new ProjectDeliverable();
$projectDeliverable->setProject($project);
$projectDeliverable->setCreatedAt(new \DateTime());
if($checklists = $form->get('checklists')->getData()){
foreach($checklists as $checklist){
if($checklist){
$checklistService->createChecklistFromTemplate(null, $project, $checklist);
}
}
}
} else {
// if ($project->getType() == 'LEAD' && $project->getLead() == 8) {
// $project->setType('CONFIRMED');
// };
// if($project->getStatus() == 'Finished' && empty($project->getClientFeedbackTemplate())){
// $result['status'] = 'ERROR';
// $result['message'] = 'Please select client feedback template';
// $result['code'] = 'feedback_template';
// return new JsonResponse($result);
// }
if($project->getLead() != 9) {
$project->setLeadFailDate(null);
$project->setLeadFailNote(null);
$project->setLeadFailRemindDate(null);
}
if($form->get('checklists')->getData()){$checklistsForm = $form->get('checklists')->getData()->toArray();}
// else {
// if($request->request->get('project')['leadFailDate']) $project->setLeadFailDate(\DateTimeImmutable::createFromFormat('d/m/Y H:i:s',$request->request->get('project')['leadFailDate'].' 00:00:00'));
// if($request->request->get('project')['leadFailRemindDate']) $project->setLeadFailRemindDate(\DateTimeImmutable::createFromFormat('d/m/Y H:i:s',$request->request->get('project')['leadFailRemindDate'].' 00:00:00'));
// }
$project->setUpdatedAt(new \DateTime());
// get date from form then convert to datetimeimmutable
$result['update'] = true;
}
if(($oldData != null && ($oldData->getStartDate() != $project->getStartDate())) || ($oldData != null && $oldData->getEndDate() != $project->getEndDate())){
$projectService->generateRevenuePlanning($project);
$result['updateRevenuePlanning'] = true;
} else {
$result['updateRevenuePlanning'] = false;
}
if($oldData != null && $oldData->getStatus() != $project->getStatus()){
$result['newProjectStatus'] = $project->getStatus();
} else {
$result['newProjectStatus'] = null;
}
$result['lead'] = $project->getLead();
$result['hasFeedbackTemplate'] = $project->getClientFeedbackTemplate() ? true : false;
$uploadFolder = $params->get('projectPlanFile');
$planFilepath = $form->get('planFilepath')->getData();
//dd($request->request->get('project'));
if ($planFilepath !== null) {
$uploadResponse = $uploadService->uploadFileToS3($uploadFolder, 'planFilepath', $request, $form);
if ($uploadResponse['status'] == 'ERROR') {
return new JsonResponse($uploadResponse);
} else {
$project->setPlanFilename($uploadResponse['fileName']);
$project->setPlanFilepath($uploadResponse['filePath']);
$project->setPlanFiletype($uploadResponse['fileType']);
}
}
//webhook confirmed project
if(($oldData != null && $oldData->getType() != 'CONFIRMED' && $project->getType() == 'CONFIRMED')){
$sendHookConfirmedProject = true;
}
//$project->setEstimatedVendorCost($request->request->get('project')['estimatedVendorCost']);
//$project->setPlanURL($request->request->get('project')['planURL']);
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($project);
$entityManager->flush();
$entityManager->refresh($project);
$result['id'] = $project->getId();
$checklists = $project->getChecklists() ? $project->getChecklists()->toArray() : null;
if($checklistsForm && $checklistsForm != $checklists){
if(count($checklistsForm) > count($checklists)){
foreach($checklistsForm as $checklist){
if(!in_array($checklist, $checklists)){
if($checklist->isIsTemplate() == true){
$checklistService->createChecklistFromTemplate(null, $project, $checklist);
}else{
$project->addChecklist($checklist);
}
}
}
}elseif(count($checklistsForm) < count($checklists)){
foreach($checklists as $checklist){
if(!in_array($checklist, $checklistsForm)){
$project->removeChecklist($checklist);
}
}
}
$result['updateChecklist'] = true;
$result['checklist'] = $this->renderView('private/project-management/project/components/list-checklist.html.twig', [
'checklists' => $project->getChecklists()
]);
}
if ($project->getGeneratedId() == null && $project->getType() != 'PROSPECT') {
$project->setGeneratedId($project->getCreatedAt()->format('ym') . '-' . sprintf('%04d', $project->getId()));
$entityManager->flush();
$entityManager->refresh($project);
}
if($_SERVER['APP_ENV'] == "prod"){
$result['webhook'] = $webhook->checkboxWebhook('projects', $id === 0 ? 'POST' : 'PUT', $id === 0 ? $project->getId() : $id, [
'id' => $project->getId(),
'name' => $project->getName(),
'description' => $project->getDescription(),
'type' => $project->getType(),
// 'clientURL' => $baseUrl.'/project-management/client/view/'.$project->getClient()->getId(),
'client' => $project->getClient()->getName(),
'projectURL' => $baseUrl.'/project-management/project/view/'.$project->getId(),
// 'projectName' => $project->getName(),
'createdBy' => $project->getCreatedBy() ? $project->getCreatedBy()->getPersonalInfo()->getFirstName().' '.$project->getCreatedBy()->getPersonalInfo()->getLastName() : ''
]);
}
if( $sendHookConfirmedProject == true){
$baseurl = $requestStack->getCurrentRequest()->getSchemeAndHttpHost();
$mattermostMessage = "### :star2: Woohoo! Exciting news – we've won a new project!".
"\n**Project ID:** ".$project->getGeneratedId().
"\n**Project Name:** [".$project->getName().']('.$baseurl.'/project-management/project/view/'.$project->getId(). ")".
"\n**Currency:** ".$project->getCurrency()->getName().' ('.$project->getCurrency()->getSymbol().')';
if($project->getPlannedRevenue()){
$mattermostMessage .= "\n**Est. Project Billing:** ".$project->getCurrency()->getSymbol().number_format($project->getPlannedRevenue(), 2, '.', ',');
};
if($project->getEstimatedVendorCost()){
$mattermostMessage .= "\n**Est. Vendor Cost:** ".$project->getCurrency()->getSymbol().number_format($project->getEstimatedVendorCost(), 2, '.', ',');
};
if($project->getEstimatedProfit()){
$mattermostMessage .= "\n**Est. Agency Rev:** ".$project->getCurrency()->getSymbol().number_format($project->getEstimatedProfit(), 2, '.', ',');
};
$mattermostService->sendMessage('OPS', $mattermostMessage);
// $result['webhook'] = $webhook->checkboxWebhook('projects', $id === 0 ? 'POST' : 'PUT', $id === 0 ? $project->getId() : $id, [
// 'id' => $project->getId(),
// 'name' => $project->getName(),
// 'description' => $project->getDescription(),
// 'type' => $project->getType(),
// // 'clientURL' => $baseUrl.'/project-management/client/view/'.$project->getClient()->getId(),
// 'client' => $project->getClient()->getName(),
// 'projectURL' => $baseUrl.'/project-management/project/view/'.$project->getId(),
// // 'projectName' => $project->getName(),
// 'createdBy' => $project->getCreatedBy() ? $project->getCreatedBy()->getPersonalInfo()->getFirstName().' '.$project->getCreatedBy()->getPersonalInfo()->getLastName() : ''
// ]);
}
$log->save($user, [
'owner' => $user,
'message' => $id === 0 ? 'log.project.add' : 'log.project.edit',
'old_data' => $id === 0 ? null : $oldData,
'new_data' => $project
]);
$projectsList = $projectRepository->findByPage(0, 1, null, null, null, null, null, null, null, null, $project->getId());
$n = 0;
foreach ($projectsList as $projectDetail) {
$projectsList[$n]['cost'] = $projectDetail[0]->getProjectCost();
$projectsList[$n]['profit'] = $projectDetail[0]->getProjectProfit1();
$projectsList[$n]['leadStatusText'] = $projectDetail[0]->getLeadStatus() ? $projectDetail[0]->getLeadStatus()->getName() : null;
$n++;
};
if ($client) {
$result['content'] = $this->renderView('private/project-management/client/components/row-project-client.html.twig', [
'project' => $projectsList[0],
'waitForPage' => false
]);
} else {
if ($id) {
$result['projectDetail'] = $this->renderView('private/project-management/project/components/detail-project.html.twig', [
'project' => $project,
'waitForPage' => false
]);
$result['projectStatus'] = $this->renderView('private/project-management/project/components/info-project-status.html.twig', [
'project' => $project,
'waitForPage' => false
]);
$result['content'] = $this->renderView('private/project-management/project/components/row-project.html.twig', [
'loadCostAndProfit' => true,
'project' => $projectsList[0],
'waitForPage' => false
]);
} else {
$result['content'] = $this->renderView('private/project-management/project/components/row-project.html.twig', [
'loadCostAndProfit' => true,
'project' => $projectsList[0],
'waitForPage' => false
]);
$result['project'] = [
'client' => $project->getClient() ? $project->getClient()->getId() : null,
'startDate' => $project->getStartDate() ? $project->getStartDate()->format('d-M-Y') : null,
'endDate' => $project->getEndDate() ? $project->getEndDate()->format('d-M-Y') : null,
'text' => $project->fullName(),
'value' => $project->getId(),
];
}
$result['prepend'] = $id == null;
}
// Email Reminder
if($oldData != null && $oldData->getProposalDate() != $project->getProposalDate() || $oldData == null && $project->getProposalDate() != null){
$projectService->projectDatesEmail($project, 'proposal_sent_date');
}
if($oldData != null && $oldData->getPresentationDate() != $project->getPresentationDate() || $oldData == null && $project->getPresentationDate() != null){
$projectService->projectDatesEmail($project, 'client_present_date');
}
if($oldData != null && $oldData->getProposalFollowUpDate() != $project->getProposalFollowUpDate() || $oldData == null && $project->getProposalFollowUpDate() != null){
$projectService->projectDatesEmail($project, 'proposal_followup_date');
}
// hide email reminder for now
// if($id && $request->request->get('project')['leadFailRemindDate'] != null){
// $projectService->projectDatesEmail($project, 'recontact_date');
// }
if($oldData != null && $oldData->getStatus() != 'Finished' && $project->getStatus() == 'Finished'){
$projectService->projectFinishedEmail($project);
}
// return new Response($result['content']);
return new JsonResponse($result);
}
$page = intval($request->query->get('page'));
$limit = intval($request->query->get('limit'));
if ($limit === 0) {
$limit = 20;
}
if ($type == null) {
$result['form'] = $this->renderView('private/project-management/project/components/project-option.html.twig', [
'id' => $project->getId()
]);
} else {
switch ($type) {
// case 'lead':
// $formType = 'form-project-new-lead.html.twig';
// break;
case 'client':
$formType = 'form-project-new-client.html.twig';
break;
case 'internal':
$formType = 'form-project-new-internal.html.twig';
break;
// case 'confirmed':
// $formType = 'form-project-new-confirmed.html.twig';
// break;
default:
$formType = 'form-project.html.twig';
break;
}
$result['form'] = $this->renderView('private/project-management/project/components/' . $formType, [
'form' => $form->createView(),
'projectsList' => $projectRepository->findByPage($page, $limit),
'id' => $project->getId(),
]);
}
//return new Response($result['form']);
return new JsonResponse($result);
}
/**
* @param ProjectRepository $projectRepository
* @param Request $request
* @param LogService $log
* @return Response
*/
#[Route(path: '/ajax/project-revenue/persist', name: 'ajax_persist_project_revenue')]
public function ajaxPersistProjectRevenue(ProjectRepository $projectRepository, UploadService $uploadService, ParameterBagInterface $params, ProjectService $projectService, Request $request, LogService $log, WebhookService $webhook, RequestStack $requestStack): Response
{
$baseUrl = $requestStack->getCurrentRequest()->getSchemeAndHttpHost();
$currentUrl = $requestStack->getCurrentRequest()->getUri();
$id = intval($request->get('id'));
// $type = $request->get('type');
$user = $this->getUser();
$project = new Project();
$result['status'] = 'OK';
if ($id > 0) {
$project = $projectRepository->find($id);
$oldData = clone $project;
$result['id'] = $project->getId();
} else {
$oldData = null;
}
$form = $this->createForm(ProjectRevenuePlanningType::class, $project);
$form->handleRequest($request);
if ($form->isSubmitted() && !$form->isValid()) {
$result['status'] = 'ERROR';
foreach ($form->getErrors(true) as $key => $error) {
$result['message'][$key] = $error->getMessage();
}
}
if ($form->isSubmitted() && $form->isValid()) {
if ($id === 0) {
} else {
foreach($project->getRevenuePlannings() as $revenuePlan){
$revenuePlan->setAmountUsd($revenuePlan->getAmountToUsd());
$revenuePlan->setUpdatedAt(new \DateTimeImmutable());
}
$result['id'] = $project->getId();
$result['update'] = true;
}
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($project);
$entityManager->flush();
$entityManager->refresh($project);
$log->save($user, [
'owner' => $user,
'message' => $id === 0 ? 'log.project-revenue.add' : 'log.project-revenue.edit',
'old_data' => $id === 0 ? null : $oldData,
'new_data' => $project
]);
$monthlyRevenues = $projectService->monthlyRevenues($project);
$monthlyRevenuesRecognition = $projectService->monthlyRevenues($project, false);
$result['projectRevenueDetail'] = $this->renderView('private/project-management/project-revenue-planning/components/detail-revenue-planning.html.twig', [
'project' => $project,
'monthlyRevenues' => $monthlyRevenues,
'monthlyRevenuesRecognition' => $monthlyRevenuesRecognition,
'waitForPage' => false
]);
$result['projectRevenueOverview'] = $this->renderView('private/project-management/project-revenue-planning/components/overview-revenue-planning.html.twig', [
'project' => $project,
'monthlyRevenues' => $monthlyRevenues,
'monthlyRevenuesRecognition' => $monthlyRevenuesRecognition,
'waitForPage' => false
]);
return new JsonResponse($result);
}
$result['form'] = $this->renderView('private/project-management/project-revenue-planning/components/form-project-revenue-planning.html.twig', [
// 'project' => $project,
'id' => $project->getId(),
'formProject' => $form->createView(),
]);
return new JsonResponse($result);
}
/**
* @param ProjectRepository $projectRepository
* @param Request $request
* @param LogService $log
* @return Response
*/
#[Route(path: '/ajax/project-deliverable/persist', name: 'ajax_persist_project_deliverable')]
public function ajaxPersistProjectDeliverable(ProjectDeliverableRepository $projectDeliverableRepository, UploadService $uploadService, ParameterBagInterface $params, ProjectService $projectService, Request $request, LogService $log, RequestStack $requestStack, S3Service $s3Service, FileService $fileService): Response
{
$baseUrl = $requestStack->getCurrentRequest()->getSchemeAndHttpHost();
$currentUrl = $requestStack->getCurrentRequest()->getUri();
$id = intval($request->get('id'));
$user = $this->getUser();
if($id){
$projectDeliverable = $projectDeliverableRepository->find($id);
}else{
$projectDeliverable = new ProjectDeliverable();
// $projectDeliverableFile = new ProjectDeliverableFile();
$oldData = null;
}
$result['status'] = 'OK';
$form = $this->createForm(ProjectDeliverableType::class, $projectDeliverable);
$form->handleRequest($request);
if ($form->isSubmitted() && !$form->isValid()) {
$result['status'] = 'ERROR';
$result['type'] = 'danger';
foreach ($form->getErrors(true) as $key => $error) {
$fieldName = $error->getOrigin()->getName();
$result['message'][$key] = ucwords($fieldName).': '. $error->getMessage();
$view = $error->getOrigin()->createView();
$id = $view->vars['id'];
$result['target'][$key] = $id;
}
}
if ($form->isSubmitted() && $form->isValid()) {
for ($i = 1; $i <= 3; $i++) {
$key = "removeImage$i";
if ($request->request->has($key)) {
if($request->request->get($key)){
$filename = explode('/', $projectDeliverable->getThumbnails()[$i]);
$s3Result = $s3Service->deleteFromS3($filename[2], $this->getParameter('documentFile'));
if ($s3Result['@metadata']['statusCode'] == 204) {
$thumbnails = $projectDeliverable->getThumbnails();
unset($thumbnails[$i]);
$projectDeliverable->setThumbnails($thumbnails);
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($projectDeliverable);
$entityManager->flush();
} else {
$this->addFlash(
'danger',
$s3Result['@metadata']['statusCode']
);
}
}
}
}
for ($i = 1; $i <= 3; $i++) {
$key = 'thumbnail'.$i;
if ($thumbnails = $form->get($key)->getData()) {
if ($thumbnails->getError() > 0) {
$errorMessage = sprintf(
'Error uploading thumbnail %d: %s',
$i,
$thumbnails->getErrorMessage()
);
$this->addFlash('danger', $errorMessage);
// $this->addFlash(
// 'danger',
// $thumbnails->getErrorMessage()
// );
return $this->redirect($request->getUri());
}
if (!file_exists($this->getParameter('documentFile')))
mkdir($this->getParameter('documentFile'), 0755, true);
$originalName = $thumbnails->getClientOriginalName();
$extension = $thumbnails->guessExtension();
$fileName = $fileService->generateFilename($originalName, $extension);
$srcPath = $this->getParameter('documentFile') . '/' . $fileName;
if(is_null($projectDeliverable->getThumbnails())){
$thumbnailsData = [
$i => $srcPath,
];
$projectDeliverable->setThumbnails($thumbnailsData);
}else{
$thumbnailsData = $projectDeliverable->getThumbnails();
$thumbnailsData[$i] = $srcPath;
$projectDeliverable->setThumbnails($thumbnailsData);
}
// Local
try {
$thumbnails->move(
$this->getParameter('documentFile'),
$fileName
);
} catch (\FileException $e) {
$this->addFlash(
'danger',
'Upload failed'
);
return $this->redirect($request->getUri());
}
// S3
$s3Result = $s3Service->sendMediaToS3($srcPath, $fileName, $this->getParameter('documentFile'));
if ($s3Result['@metadata']['statusCode'] == 200) {
gc_collect_cycles();
if (file_exists($srcPath))
unlink($srcPath);
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($projectDeliverable);
$entityManager->flush();
} else {
$this->addFlash(
'danger',
$s3Result['@metadata']['statusCode']
);
}
}
}
foreach($projectDeliverable->getProjectDeliverableFiles() as $projectDeliverableFile){
if(!$projectDeliverableFile->getProjectDeliverable()){
$projectDeliverableFile->setProjectDeliverable($projectDeliverable);
}
if($projectDeliverableFile->getId() == null)
{
$projectDeliverableFile->setProjectDeliverable($projectDeliverable);
if($projectDeliverableFile->getCreatedAt() == null){
$projectDeliverableFile->setCreatedAt(new \DateTimeImmutable());
$projectDeliverableFile->setCreatedBy($user->getId());
}
}
}
$inputBag = $request->request->get('project_deliverable');
$fileBag = $request->files->get('project_deliverable');
$inputFiles = is_array($inputBag) ? $inputBag['projectDeliverableFiles'] : null;
$uploadedFiles = is_array($inputBag) ? $fileBag['projectDeliverableFiles'] : null;
if($uploadedFiles){
foreach($uploadedFiles as $key => $fileUpload){
if(!$fileUpload['file']) continue;
$fileUploadUuid = $inputFiles[$key]['uuid'];
$projectDeliverableFile = $projectDeliverable->getProjectDeliverableFileByUuid($fileUploadUuid);
if($fileUpload['file']){
if(!empty($projectDeliverableFile->getFile())){
$oldFileName = explode('/', $projectDeliverableFile->getFile());
$s3Service->deleteFromS3($oldFileName[2], $this->getParameter('documentFile'));
}
if ($fileUpload['file']->getError() > 0) {
$this->addFlash(
'danger',
$fileUpload->getErrorMessage()
);
return $this->redirect($request->getUri());
}
if (!file_exists($this->getParameter('documentFile')))
mkdir($this->getParameter('documentFile'), 0755, true);
$originalName = $fileUpload['file']->getClientOriginalName();
$extension = $fileUpload['file']->guessExtension();
$fileName = $fileService->generateFilename($originalName, $extension);
$srcPath = $this->getParameter('documentFile') . '/' . $fileName;
$projectDeliverableFile->setFile($srcPath);
if($projectDeliverableFile->getCreatedAt() == null){
$projectDeliverableFile->setCreatedAt(new \DateTimeImmutable());
$projectDeliverableFile->setCreatedBy($user->getId());
}else{
$projectDeliverableFile->setUpdatedAt(new \DateTimeImmutable());
$projectDeliverableFile->setUpdatedBy($user->getId());
}
if(!$projectDeliverableFile->getProjectDeliverable()){
$projectDeliverableFile->setProjectDeliverable($projectDeliverable);
}
// Local
try {
$fileUpload['file']->move(
$this->getParameter('documentFile'),
$fileName
);
} catch (\FileException $e) {
$this->addFlash(
'danger',
'Upload failed'
);
return $this->redirect($request->getUri());
}
// S3
$s3Result = $s3Service->sendMediaToS3($srcPath, $fileName, $this->getParameter('documentFile'));
if ($s3Result['@metadata']['statusCode'] == 200) {
gc_collect_cycles();
if (file_exists($srcPath))
unlink($srcPath);
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($projectDeliverableFile);
$entityManager->flush();
} else {
$this->addFlash(
'danger',
$s3Result['@metadata']['statusCode']
);
}
}
}
}
$result['id'] = $projectDeliverable->getId();
$result['update'] = true;
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($projectDeliverable);
$entityManager->flush();
$entityManager->refresh($projectDeliverable);
foreach($projectDeliverable->getProjectDeliverableFiles() as $projectDeliverableFile){
if($projectDeliverableFile->isIsDeleted() == true){
$projectDeliverableFile->setProjectDeliverable(null);
if(!empty($projectDeliverableFile->getFile())){
$oldFileName = explode('/', $projectDeliverableFile->getFile());
$s3Service->deleteFromS3($oldFileName[2], $this->getParameter('documentFile'));
}
$entityManager->remove($projectDeliverableFile);
}
}
$entityManager->flush();
$log->save($user, [
'owner' => $user,
'message' => 'log.project-deliverable.edit',
'old_data' => null,
'new_data' => $projectDeliverable
]);
$result['thumbnails'] = $this->renderView('private/project-management/project-deliverable/components/project-deliverable-thumbnails.html.twig', [
'projectDeliverable' => $projectDeliverable,
]);
$result['documents'] = $this->renderView('private/project-management/project-deliverable/components/list-project-deliverable.html.twig', [
'projectDeliverable' => $projectDeliverable,
'waitForPage' => false
]);
return new JsonResponse($result);
}
$result['form'] = $this->renderView('private/project-management/project-deliverable/components/form-project-deliverable.html.twig', [
// 'project' => $project,
'id' => $projectDeliverable->getId(),
'projectDeliverable' => $projectDeliverable,
'formProjectDeliverable' => $form->createView(),
]);
return new JsonResponse($result);
}
#[Route(path: '/ajax/project-client-feedback-temp/persist', name: 'ajax_persist_project_client_feedback_temp')]
public function ajaxPersistProjectClientFeedbackTemplate(ProjectRepository $projectRepository, Request $request, LogService $log): Response
{
$id = intval($request->get('id'));
$project = $projectRepository->find($id);
$oldData = clone $project;
$user = $this->getUser();
$result['status'] = 'OK';
$form = $this->createForm(ProjectClientFeedbackTemplateType::class, $project);
$form->handleRequest($request);
if ($form->isSubmitted() && !$form->isValid()) {
$result['status'] = 'ERROR';
foreach ($form->getErrors(true) as $key => $error) {
$result['message'][$key] = $error->getMessage();
}
}
if ($form->isSubmitted() && $form->isValid()) {
$result['id'] = $project->getId();
$result['update'] = true;
$entityManager = $this->getDoctrine()->getManager();
if(!empty($project->getClientFeedbackTemplate())){
if($oldData->getClientFeedbackTemplate() && $project->getClientFeedbackTemplate() != $oldData->getClientFeedbackTemplate()){
$feedbacks = $oldData->getActiveClientFeedback($oldData->getClientFeedbackTemplate()->getId());
foreach($feedbacks as $feedback){
$project->removeClientFeedback($feedback);
}
}
if(!empty($project->getClientFeedbackTemplate()->getClientFeedbackForms())){
$feedbackForms = $project->getClientFeedbackTemplate()->getClientFeedbackForms();
foreach($feedbackForms as $feedbackForm){
$clientFeedback = new ClientFeedback();
$clientFeedback->setFeedbackArea($feedbackForm->getAreaFeedback());
$clientFeedback->setFeedbackAreaDesc($feedbackForm->getDescription());
$clientFeedback->setFeedbackType($feedbackForm->getFeedbackType());
$clientFeedback->setFeedbackTemplate($project->getClientFeedbackTemplate()->getId());
$clientFeedback->setCreatedAt(new \DateTimeImmutable());
$clientFeedback->setCreatedBy($user->getId());
$project->addClientFeedback($clientFeedback);
}
}
}
// Old code
/*
if($project->checkFilledFeedbackTemplate()){
foreach($project->getClientFeedback() as $feedback){
$feedback->setDeletedAt(new \DateTimeImmutable());
}
}*/
// $project->setScore($project->getFeedbackScore());
$entityManager->persist($project);
$entityManager->flush();
$entityManager->refresh($project);
$log->save($user, [
'owner' => $user,
'message' => 'log.project.edit',
'old_data' => null,
'new_data' => $project
]);
$result['score'] = $project->getScore();
$result['projectStatus'] = $project->getStatus();
$result['clientFeedback'] = $this->renderView('private/project-management/client-feedback/client-feedback.html.twig', [
'project' => $project,
]);
$result['clientFeedbackList'] = $this->renderView('private/project-management/client-feedback/components/list-client-feedback.html.twig', [
'clientFeedbacks' => $project->getActiveClientFeedback(),
]);
return new JsonResponse($result);
}
$warning = null;
if($project->checkFilledFeedbackTemplate()){
$warning = "Your feedback template has been filled out by the client. Changing the filled feedback template will result in all feedback from the client being lost.";
}
$result['form'] = $this->renderView('private/project-management/client-feedback/components/form-project-client-feedback-template.html.twig', [
'id' => $project->getId(),
'project' => $project,
'formProject' => $form->createView(),
'warning' => $warning
]);
return new JsonResponse($result);
}
#[Route(path: '/ajax/crud/project-client-feedback', name: 'ajax_crud_client_feedback')]
public function ajaxCrudClientFeedback(ClientFeedbackRepository $clientFeedbackRepository, Request $request, LogService $log): Response
{
$id = intval($request->get('id'));
$crud = $request->get("crud");
$project = $request->get('project');
$user = $this->getUser();
$result['status'] = 'OK';
if (empty($id)) {
$clientFeedback = new ClientFeedback();
$id = null;
} else {
$clientFeedback = $clientFeedbackRepository->find($id);
$oldData = clone $clientFeedback;
}
$waitForPage = $request->query->get('waitForPage');
$waitForPage === null ? $result['waitForPage'] = 'false' : $result['waitForPage'] = $waitForPage;
$form = $this->createForm(ClientFeedbackType::class, $clientFeedback);
$form->handleRequest($request);
if ($form->isSubmitted() && !$form->isValid()) {
$result['status'] = 'ERROR';
foreach ($form->getErrors(true) as $key => $error) {
$result['message'][$key] = $error->getMessage();
}
return new JsonResponse($result);
}
if ($form->isSubmitted() && $form->isValid()) {
if($project !== null){
$project = $this->getDoctrine()->getRepository(Project::class)->find($project);
$clientFeedback->setProject($project);
}
$result['id'] = $clientFeedback->getId();
$result['update'] = true;
$entityManager = $this->getDoctrine()->getManager();
if ($id === null) {
$clientFeedback->setCreatedBy($user->getId());
$clientFeedback->setCreatedAt(new \DateTimeImmutable());
} else {
$clientFeedback->setUpdatedAt(new \DateTimeImmutable());
$clientFeedback->setUpdatedBy($user->getId());
}
$entityManager->persist($clientFeedback);
$entityManager->flush();
$entityManager->refresh($clientFeedback);
$log->save($user, [
'owner' => $user,
'message' => 'log.client_feedback.edit',
'old_data' => null,
'new_data' => $clientFeedback
]);
$result['id'] = $clientFeedback->getId();
$result['content'] = $this->renderView('private/project-management/client-feedback/components/row-client-feedback.html.twig', [
'clientFeedback' => $clientFeedback,
'waitForPage' => $waitForPage,
'hideAction' => false
]);
$result['update'] = $id === null ? false : true;
return new JsonResponse($result);
}
if ($crud === 'delete' && $clientFeedback !== null) {
if ($result['status'] == 'OK') {
$clientFeedback->setDeletedAt(new \DateTimeImmutable());
$entityManager = $this->getDoctrine()->getManager();
$entityManager->flush();
$log->save($user, [
'owner' => $user,
'message' => 'log.client_feedback.delete',
'old_data' => $oldData,
'new_data' => null
]);
}
return new JsonResponse($result);
}
if ($crud === 'create' || $crud === 'update') {
$result['form'] = $this->renderView('private/project-management/client-feedback/components/form-client-feedback.html.twig', [
'id' => $clientFeedback->getId(),
'clientFeedback' => $clientFeedback,
'project' => $project,
'formClientFeedback' => $form->createView(),
]);
return new JsonResponse($result);
}
$order = $request->get('order');
$orderBy = $request->get('orderBy');
// $office = $request->get('office');
$keyword = $request->get('keyword');
$page = intval($request->query->get('page'));
$limit = intval($request->query->get('limit'));
if ($limit === 0) $limit = 20;
$clientFeedbacks = $clientFeedbackRepository->findByPage($page, $limit, $order, $orderBy, $keyword, $project);
$totalCount = $clientFeedbackRepository->countByPage($keyword, $project);
$body = $this->renderView('private/project-management/client-feedback/components/list-client-feedback.html.twig', [
'clientFeedbacks' => $clientFeedbacks,
]);
$result['content'] = [
'html' => $body,
'total' => $totalCount
];
return new JsonResponse($result);
}
/**
* @param ProjectRepository $projectRepository
* @param Request $request
* @param LogService $log
* @return Response
*/
#[Route(path: '/ajax/project/sales-order/persist', name: 'ajax_persist_project_so')]
public function ajaxPersistProjectSO(SalesOrderRepository $salesOrderRepository, ProjectSalesOrderRepository $projectSalesOrderRepository, ProjectRepository $projectRepository, Request $request, ProjectService $projectService, LogService $log): Response
{
$soId = intval($request->get('so_id'));
$projectId = intval($request->get('p_id'));
$result['status'] = 'OK';
$salesOrder = $salesOrderRepository->find($soId);
$project = $projectRepository->find($projectId);
$pso = $projectSalesOrderRepository->findOneBy(['project' => $project, 'salesOrder' => $salesOrder]);
if (!$salesOrder || !$project) {
$result['status'] = 'ERROR';
$result['message'] = 'SO no found';
return new JsonResponse($result);
} else {
$oldData = clone $project->getProjectSalesOrders();
}
$clientId = null;
$clientId = $project->getClient()->getId();
$form = $this->createForm(SalesOrderType::class, $salesOrder,array('projectId' => $projectId, 'clientId' => $clientId));
$result['form'] = $this->renderView('private/project-management/project/components/form-project-sales-order.html.twig', [
'form' => $form->createView(),
'salesOrder' => $salesOrder,
'so_id' => $soId,
'p_id' => $projectId,
'pso' => $pso,
'project' => $project
]);
$form->handleRequest($request);
// dd($form->isSubmitted());
if ($form->isSubmitted() && !$form->isValid()) {
// dd('test');
// dd($form->getErrors());
$result['status'] = 'ERROR';
foreach ($form->getErrors(true) as $key => $error) {
dd($error);
$result['message'][$key] = $error->getMessage();
}
dd($result['message']);
}
if ($form->isSubmitted() && $form->isValid()) {
$entityManager = $this->getDoctrine()->getManager();
$pso = $projectSalesOrderRepository->findOneBy(['project' => $project, 'salesOrder' => $salesOrder]);
if ($pso) {
foreach ($form->get('salesOrderPurchaseOrders') as $purchaseOrder) {
if ($purchaseOrder->getData()->getProject() === null) {
$purchaseOrder->getData()->setProject($project);
}
}
$pso->setAmount($form->get('amount')->getData());
$entityManager->persist($pso);
$soInvoices = $pso->getSalesOrder()->getSalesOrderInvoices();
foreach ($soInvoices as $soInvoice) {
if ($soInvoice->getProject() === null) {
$soInvoice->setProject($project);
$entityManager->persist($soInvoice);
$projectService->projectSalesHasAllocatedEmail($soInvoice->getInvoice(), $project);
}
}
$entityManager->flush();
} else {
$projectSalesOrder = new ProjectSalesOrder();
$projectSalesOrder->setProject($project);
$projectSalesOrder->setSalesOrder($salesOrder);
$projectSalesOrder->setAmount($form->get('amount')->getData());
$entityManager->persist($projectSalesOrder);
$soInvoices = $projectSalesOrder->getSalesOrder()->getSalesOrderInvoices();
foreach ($soInvoices as $soInvoice) {
if ($soInvoice->getProject() === null) {
$soInvoice->setProject($project);
$entityManager->persist($soInvoice);
}
}
$entityManager->flush();
}
$entityManager->refresh($salesOrder);
$user = $this->getUser();
$result['content'] = $this->renderView('private/project-management/sales-order/components/row-sales-order.html.twig', [
'salesOrder' => $salesOrder,
'waitForPage' => false,
'project' => $project->getId(),
'projectObj' => $project
]);
$result['id'] = $salesOrder->getId();
$result['update'] = true;
$log->save($user, [
'owner' => $user,
'message' => $soId === 0 ? 'log.project.addso' : 'log.project.updateso',
'old_data' => $oldData,
'new_data' => $project->getProjectSalesOrders()
]);
}
// else if (!$form->isSubmitted() && $salesOrder && $request->get('save_so') === 'true') {
// $entityManager = $this->getDoctrine()->getManager();
//// $project->addSalesOrder($salesOrder);
// $entityManager->persist($project);
// $entityManager->flush();
// $user = $this->getUser();
//
// $log->save($user, [
// 'owner' => $user,
// 'message' => $soId === 0 ? 'log.project.addso' : 'log.project.updateso',
// 'old_data' => $oldData,
// 'new_data' => $project->getSalesOrders()
// ]);
// };
foreach ($form->getErrors(true) as $key => $error) {
$result['message'][$key] = $error->getMessage();
}
// dd($result);
//return new Response($result['form']);
// Type::class, $project);
return new JsonResponse($result);
}
/**
* @param Request $request
* @param ProjectRepository $projectRepository
* @param TranslatorInterface $translator
* @param LogService $log
* @return Response
*/
#[Route(path: '/ajax/project/delete', name: 'ajax_delete_project', methods: 'POST')]
public function ajaxDeleteProject(Request $request, ProjectRepository $projectRepository, TranslatorInterface $translator, LogService $log, WebhookService $webhook, ProjectService $projectService): Response
{
$project = $projectRepository->find(intval($request->get('id')));
$oldData = clone $project;
if (!$project) {
$result['status'] = 'ERROR';
$result['message'] = $translator->trans('messages.project.not_found');
return new JsonResponse($result);
} else {
$user = $this->getUser();
try {
$entityManager = $this->getDoctrine()->getManager();
if ($project->getDeletedAt() === null) {
$project->setDeletedAt(new \DateTimeImmutable());
$log->save($user, [
'owner' => $user,
'message' => ' log.project.soft_delete',
'old_data' => $oldData,
'new_data' => null
]);
$projectService->projectDeletedEmail($project, $user);
} else {
$projectRepository->remove($project);
$log->save($user, [
'owner' => $user,
'message' => ' log.project.delete',
'old_data' => $oldData,
'new_data' => null
]);
}
$entityManager->flush();
} catch (OptimisticLockException | ORMException $e) {
$result['status'] = 'ERROR';
$result['message'] = $translator->trans('messages.project.delete.error');
return new JsonResponse($result);
}
if($_SERVER['APP_ENV'] == "prod"){
$result['webhook'] = $webhook->checkboxWebhook('projects', 'DELETE', $oldData->getId());
}
}
$result['status'] = 'OK';
return new JsonResponse($result);
}
/**
* @param Request $request
* @param UploadService $uploadService
* @param ParameterBagInterface $params
* @return JsonResponse
*/
#[Route(path: '/ajax/project/upload', name: 'upload_project_plan', methods: ['POST'])]
public function upload_file(Request $request, UploadService $uploadService, ParameterBagInterface $params)
{
$uploadFolder = $params->get('projectPlanFile');
$response = $uploadService->uploadFileToS3($uploadFolder, 'file', $request);
return new JsonResponse($response);
}
/**
* @param Request $request
* @param MattermostService $mattermostService
* @return JsonResponse
*/
#[Route(path: '/ajax/mattermost/create-channel', name: 'ajax_mattermost_create_channel', methods: ['POST'])]
public function createMattermostChannel(Request $request, MattermostService $mattermostService): Response
{
$project = $request->get('project');
$crud = $request->get("crud");
$result['status'] = 'OK';
// add form
$form = $this->createForm(MattermostType::class);
$form->handleRequest($request);
if ($form->isSubmitted() && !$form->isValid()) {
$result['status'] = 'ERROR';
foreach ($form->getErrors(true) as $key => $error) {
$result['message'][$key] = $error->getMessage();
}
}
if ($form->isSubmitted() && $form->isValid()) {
$name = $form->get('name')->getData();
$response = $mattermostService->createChannel($name);
if($response['status'] == 'OK'){
$result = $response;
$project = $this->getDoctrine()->getRepository(Project::class)->find($project);
if($project){
$project->setMattermost($result['url']);
$entityManager = $this->getDoctrine()->getManager();
$entityManager->flush();
};
}
// $result['url'] = 'https://xxxmattermost.mediatropy.com/mediatropy/channels/test-mm-integration-adhit';
// $result['name'] = 'test-mm-integration-adhit';
return new JsonResponse($result);
};
if($crud === 'create'){
$result['form'] = $this->renderView('private/project-management/project/components/form-mattermost.html.twig', [
'form' => $form->createView(),
]);
return new JsonResponse($result);
}
}
/**
* @param int $_id
*/
#[Route(path: '/project/export-sales-orders/{_id}', defaults: ['_id' => 0], name: 'export_project_sales_orders')]
public function exportProjectSalesOrders(int $_id, SalesOrderRepository $salesOrderRepository, TranslatorInterface $translator, ProjectRepository $projectRepository)
{
$project = $projectRepository->find($_id);
if (!$project) {
return new Response($translator->trans('messages.project.not_found'));
}
// dd($employeeSummaryList);
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'SO Number');
$sheet->setCellValue('B1', 'Currency');
$sheet->setCellValue('C1', 'Amount');
$sheet->setCellValue('D1', 'Reference');
$sheet->setCellValue('E1', 'Invoice');
$sheet->setCellValue('F1', 'Amount');
$sheet->setCellValue('G1', 'Due');
$sheet->setCellValue('H1', 'Status');
$soList = $salesOrderRepository->findByPage(0, 9999, "", 'ASC', null, $_id, 0);
$filename = $_id . "_" . urlencode($project->getName()) . "_" . date('Y-m-d') . '.xlsx';
foreach ($soList as $salesOrder) {
$row = $sheet->getHighestRow() + 1;
$sheet->insertNewRowBefore($row);
$sheet->setCellValue('A' . $row, $salesOrder->getSalesOrderNo());
$sheet->setCellValue('B' . $row, $salesOrder->getCurrency()->getIso());
$sheet->setCellValue('C' . $row, $salesOrder->getTotal());
//Todo this one need to link to the actual PO in HR when we have the page
$sheet->setCellValue('D' . $row, $salesOrder->getPurchaseOrder() ? $salesOrder->getPurchaseOrder()->getPurchaseOrderNo() : "");
$invoices = $salesOrder->getSalesOrderInvoices($_id);
$i = 0;
foreach ($invoices as $soInvoice) {
if ($i > 0) {
$row = $sheet->getHighestRow() + 1;
$sheet->insertNewRowBefore($row);
}
$invoice = $soInvoice->getInvoice();
$sheet->setCellValue('E' . $row, $invoice->getInvoiceNo());
$sheet->setCellValue('F' . $row, $soInvoice->getAmount());
$sheet->setCellValue('G' . $row, $invoice->getDueDate() ? $invoice->getDueDate()->format('Y-m-d') : "");
$sheet->setCellValue('H' . $row, $invoice->getXeroStatus());
$i++;
}
}
$writer = new Xlsx($spreadsheet);
$writer->save($filename);
// Set the content-type:
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Length: ' . filesize($filename));
readfile($filename); // send file
unlink($filename); // delete file
exit;
}
#[Route(path: '/project/export-list/{_target}', defaults: ['_target' => ''], name: 'export_project_list')]
public function exportProjectList(string $_target, Request $request, ProjectService $projectService)
{
$keyword = $request->get('keyword');
$startDate = $request->get('start');
$endDate = $request->get('end');
$client = $request->get('client');
$status = $request->get('status');
$type = $request->get('type');
$retainer = $request->get('retainer');
$department = $request->get('department');
$project = $request->get('project');
$assignedUser = $request->get('assignedUser');
$pic = $request->get('pic');
$showDeleted = false;
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$user = $this->getUser();
$userRoles = $user->getRoles();
$isAdmin = false;
if ($this->isGranted('ROLE_MARCOM', $user->getRoles()) or $this->isGranted('ROLE_FINANCE', $user->getRoles())) {
$isAdmin = true;
}
if($type == 'LEAD'){
return $projectService->exportProjectLead($isAdmin, $keyword, $startDate, $endDate, $client, $status, $type, $retainer, $department, $project, $assignedUser, $pic, $showDeleted, $baseurl, $_target);
}elseif($type == 'CONFIRMED'){
return $projectService->exportProjectConfirmed($isAdmin, $keyword, $startDate, $endDate, $client, $status, $type, $retainer, $department, $project, $assignedUser, $pic, $showDeleted, $baseurl, $_target);
}else{
return $projectService->exportProjectInternal($isAdmin, $keyword, $startDate, $endDate, $client, $status, $type, $retainer, $department, $project, $assignedUser, $pic, $showDeleted, $baseurl, $_target);
}
}
#[Route(path: '/project/export-list-monthly/{_target}', defaults: ['_target' => ''], name: 'export_project_list_monthly')]
public function exportProjectListMonthly(string $_target, ProjectRepository $projectRepository, ProjectAllocatedHoursRepository $allocatedHoursRepository, Request $request, GoogleDriveService $googleDriveService)
{
$user = $this->getUser();
$userRoles = $user->getRoles();
// if ((!in_array("ROLE_HR", $userRoles)) && (!in_array("ROLE_FINANCE", $userRoles))) return new Response('You are not allowed to access this page!');
if(!$this->isGranted("ROLE_FINANCE")) return new Response('You are not allowed to access this page!');
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$month = $request->get('month');
$year = $request->get('year');
$monthlyStartDate = new \DateTimeImmutable($year . '-' . $month . '-01');
$monthlyEndDate = $monthlyStartDate->modify('last day of this month');
// get end of month date from year and month
$monthYear = date("MY", strtotime($monthlyStartDate->format("Y-m-d")));
$keyword = $request->get('keyword');
$startDate = $request->get('start');
$endDate = $request->get('end');
$client = $request->get('client');
$status = $request->get('status');
$type = $request->get('type');
$retainer = $request->get('retainer');
$department = $request->get('department');
$project = $request->get('project');
$assignedUser = null;
$spreadsheet = new Spreadsheet();
// WORKSHEET: Monthy Report
//$monthlyReportSheet = $spreadsheet->getActiveSheet()->setTitle('MonthlyReport_'.\DateTime::createFromFormat('!m', $month)->format('M').$year);
$monthlyReportSheet = $spreadsheet->getActiveSheet()->setTitle('MonthlyReport_' . \DateTime::createFromFormat('!m', $month)->format('M') . $year);
//$monthlyReportSheet= new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'MonthlyReport_'.\DateTime::createFromFormat('!m', $month)->format('M').$year);
//$spreadsheet->addSheet($monthlyReportSheet, 0);
$monthlyReportSheet->getDefaultColumnDimension()->setWidth(25);
$monthlyReportSheet->setCellValue('A1', 'ID');
$monthlyReportSheet->setCellValue('B1', 'Client Name');
$monthlyReportSheet->setCellValue('C1', 'Project Name');
$monthlyReportSheet->setCellValue('D1', 'Project Status');
$monthlyReportSheet->setCellValue('E1', 'Score');
$monthlyReportSheet->setCellValue('F1', 'Project Type');
$monthlyReportSheet->setCellValue('G1', 'Retainer');
$monthlyReportSheet->setCellValue('H1', 'PIC');
$monthlyReportSheet->setCellValue('I1', 'PIC Client');
$monthlyReportSheet->setCellValue('J1', 'Plan File');
$monthlyReportSheet->setCellValue('K1', 'Plan URL');
$monthlyReportSheet->setCellValue('L1', 'Start Date');
$monthlyReportSheet->setCellValue('M1', 'End Date');
$monthlyReportSheet->setCellValue('N1', 'SO Number(s)');
$monthlyReportSheet->setCellValue('O1', 'Invoice Number(s)');
$monthlyReportSheet->setCellValue('P1', 'Estimated Project Billings USD');
$monthlyReportSheet->setCellValue('Q1', 'Total SO USD');
$monthlyReportSheet->setCellValue('R1', 'Total INV USD');
$monthlyReportSheet->setCellValue('S1', 'Total Invoice Since Start USD');
$monthlyReportSheet->setCellValue('T1', 'Total Invoice ' . $monthYear .' USD');
$monthlyReportSheet->setCellValue('U1', 'Est. Hours');
$monthlyReportSheet->setCellValue('V1', 'Est. Hours Cost');
$monthlyReportSheet->setCellValue('W1', 'Planned Vendor Costs USD');
$monthlyReportSheet->setCellValue('X1', 'Total Cost Budget USD');
$monthlyReportSheet->setCellValue('Y1', 'Timesheets Hours Since Start');
$monthlyReportSheet->setCellValue('Z1', 'Timesheets Cost Since Start USD');
$monthlyReportSheet->setCellValue('AA1', 'Timesheets Hours ' . $monthYear);
$monthlyReportSheet->setCellValue('AB1', 'Timesheets Cost ' . $monthYear . ' USD');
$monthlyReportSheet->setCellValue('AC1', 'Vendor Costs Since Start USD');
$monthlyReportSheet->setCellValue('AD1', 'Vendor Costs ' . $monthYear . ' USD');
$monthlyReportSheet->setCellValue('AE1', 'Total Cost Since Start USD');
$monthlyReportSheet->setCellValue('AF1', 'Total Cost ' . $monthYear . ' USD');
$monthlyReportSheet->getStyle("A1:".$monthlyReportSheet->getHighestColumn()."1")->getFont()->setBold(true);
$monthlyReportSheet->getStyle("A1:".$monthlyReportSheet->getHighestColumn()."1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$monthlyReportSheet->getStyle("A1:".$monthlyReportSheet->getHighestColumn()."1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$monthlyReportSheet->getStyle("A2:".$monthlyReportSheet->getHighestColumn()."2")->getFont()->setBold(false);
$monthlyReportSheet->getStyle("A2:".$monthlyReportSheet->getHighestColumn()."2")->getAlignment()->setWrapText(true);
$monthlyReportSheet->getStyle("A2:".$monthlyReportSheet->getHighestColumn()."2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$monthlyReportSheet->freezePane('A2');
$monthlyReportSheet->getColumnDimension('F')->setWidth(10);
$departmentName = null;
if ($department) {
$departmentData = $this->getDoctrine()->getRepository(Department::class)->find($department);
if ($departmentData) {
$departmentName = $departmentData->getName();
};
};
$filename = "Project_Monthly_Report_";
$filename .= $month ? strtoupper(\DateTime::createFromFormat('!m', $month)->format('F')) : "";
$filename .= $year ? "_" . $year : "";
$filename .= $departmentName ? "-" . urlencode($departmentName) : "";
$filename .= $keyword ? "-" . urlencode($keyword) : "";
$filename .= $status ? "-" . urlencode($status) : "";
$filename .= $type ? "-" . urlencode($type) : "";
$filename .= $startDate ? "_" . urlencode($startDate) : "";
$filename .= $endDate ? "-" . urlencode($endDate) : "";
$filename .= '.xlsx';
$projectsList = $projectRepository->findByPage(0, 9999, $keyword, "ASC", null, $startDate, $endDate, $type, $client, $status, $project, $department, $retainer, $assignedUser);
$n = 0;
foreach ($projectsList as $projectDetail) {
$projectsList[$n]['cost'] = $projectDetail[0]->getProjectCost();
$projectsList[$n]['profit'] = $projectDetail[0]->getProjectProfit1();
$projectsList[$n]['leadStatusText'] = $projectDetail[0]->getLeadStatus() ? $projectDetail[0]->getLeadStatus()->getName() : null;
$n++;
};
$row = $monthlyReportSheet->getHighestRow();
foreach ($projectsList as $project) {
$picName = $project['picMiddleName'] ? $project['picFirstName'] . ' ' . $project['picMiddleName'] . ' ' . $project['picLastName'] : $project['picFirstName'] . ' ' . $project['picLastName'];
$picClientName = $project[0]->getClientPersonInCharge() ? $project[0]->getClientPersonInCharge()->getFirstName().' '.$project[0]->getClientPersonInCharge()->getLastName() : '-';
//$monthlyReportSheet->insertNewRowBefore($row);
$monthlyReportSheet->setCellValue('A' . $row, $project['generatedId']);
$monthlyReportSheet->setCellValue('B' . $row, $project['clientName']);
$monthlyReportSheet->setCellValue('C' . $row, $project['name']);
$monthlyReportSheet->getCell('C' . $row)->getHyperlink()->setUrl($baseurl . '/project-management/project/view/' . $project['id']);
$monthlyReportSheet->setCellValue('D' . $row, $project['type'] == 'LEAD' ? $project['lead'] . ' - ' . $project[0]->getLeadStatus()->getName() : ($project['status'] ? $project['status'] : '-'));
$monthlyReportSheet->setCellValue('E' . $row, $project[0]->getScore());
$monthlyReportSheet->setCellValue('F' . $row, $project['type']);
$monthlyReportSheet->setCellValue('G' . $row, $project['retainer'] ? 'Yes' : 'No');
$monthlyReportSheet->setCellValue('H' . $row, $picName);
$monthlyReportSheet->setCellValue('I' . $row, $picClientName);
$monthlyReportSheet->setCellValue('J' . $row, $project['planFilename'] ? $project['planFilename'] : "-");
if ($project['planFilename']) {
$monthlyReportSheet->getCell('J' . $row)->getHyperlink()->setUrl($project['planFilepath']);
}
$monthlyReportSheet->setCellValue('K' . $row, $project['planURL'] ? $project['planURL'] : "-");
if ($project['planURL']) {
$monthlyReportSheet->getCell('K' . $row)->getHyperlink()->setUrl($project['planURL']);
}
$monthlyReportSheet->setCellValue('L' . $row, $project['startDate'] ? $project['startDate']->format('Y-m-d') : '-');
$monthlyReportSheet->setCellValue('M' . $row, $project['endDate'] ? $project['endDate']->format('Y-m-d') : '-');
$proSalesOrders = $project[0]->getProjectSalesOrders();
$proSales = [];
$proSalesInvoices = [];
foreach ($proSalesOrders as $so) {
array_push($proSales, $so->getSalesOrder()->getSalesOrderNo());
$invoices = $so->getSalesOrder()->getSalesOrderInvoices($project['id']);
foreach ($invoices as $invoice) {
array_push($proSalesInvoices, $invoice->getInvoice()->getInvoiceNo());
}
}
$monthlyReportSheet->setCellValue('N' . $row, implode(', ', $proSales));
$monthlyReportSheet->setCellValue('O' . $row, implode(', ', $proSalesInvoices));
// $multiRow = $row;
// $proSalesOrders = $project[0]->getProjectSalesOrders();
// foreach ($proSalesOrders as $so){
// $monthlyReportSheet->setCellValue('J' . $multiRow, $so->getSalesOrder()->getSalesOrderNo());
// $invoices = $so->getSalesOrder()->getSalesOrderInvoices();
// $multiMultiRow = $multiRow;
// foreach($invoices as $invoice){
// $monthlyReportSheet->setCellValue('K' . $multiMultiRow, $invoice->getInvoice()->getInvoiceNo());
// $multiMultiRow++;
// }
// $multiRow = $multiMultiRow;
// }
if ($project['type'] == 'INTERNAL') {
$monthlyReportSheet->setCellValue('P' . $row, '-');
} else {
//$monthlyReportSheet->setCellValue('L' . $row, $project['type'] == 'LEAD' ? $project['estimatedProfit'] : $project['plannedRevenue']);
// $monthlyReportSheet->setCellValue('M' . $row, $project['type'] == 'LEAD' ? $project[0]->getEstimatedProfitUsd() : $project[0]->getPlannedRevenueUsd());
$monthlyReportSheet->setCellValue('P' . $row, $project[0]->getPlannedRevenueUsd());
}
$monthlyReportSheet->setCellValue('Q' . $row, $project[0]->getSoTotalUsd());
$monthlyReportSheet->setCellValue('R' . $row, $project[0]->getInvoicesTotalUsd());
$resourceAllocations = $allocatedHoursRepository->findByProject('', '', $project['id']);
$totalEstimatedProjectHours = 0;
$totalEstProjectHoursCost = 0;
foreach ($resourceAllocations as $allocation) {
$totalEstimatedProjectHours += intval($allocation->getHours());
$totalEstProjectHoursCost += intval($allocation->getHours() * $allocation->getUserRole()->getCost());
}
$estimatedProjectHours = !empty($totalEstimatedProjectHours) ? $totalEstimatedProjectHours : '0';
$estimatedProjectHoursCost = !empty($totalEstProjectHoursCost) ? $totalEstProjectHoursCost : '0';
$totalCostBudget = (!empty($totalEstProjectHoursCost) ? $totalEstProjectHoursCost : 0) + $project[0]->getVendorPlannedTotalUsd();
$totalInvoiceSinceStart = $project[0]->getInvoicesTotalUsd($monthlyEndDate);
$totalInvoiceMonthly = $project[0]->getInvoicesTotalUsdByMonth($monthlyStartDate, $monthlyEndDate);
$recordCost = $project[0]->getProjectManhourCost($monthlyEndDate);
$recordMonthlyCost = $project[0]->getProjectManhourCostByMonth($monthlyStartDate, $monthlyEndDate);
$totalCostSinceStart = $recordCost + $project[0]->getVendorInvoicesTotalUsd();
$monthlyReportSheet->setCellValue('S' . $row, $totalInvoiceSinceStart);
$monthlyReportSheet->setCellValue('T' . $row, $totalInvoiceMonthly);
$monthlyReportSheet->setCellValue('U' . $row, $estimatedProjectHours);
$monthlyReportSheet->setCellValue('V' . $row, $estimatedProjectHoursCost);
$monthlyReportSheet->setCellValue('W' . $row, $project[0]->getVendorPlannedTotalUsd());
$monthlyReportSheet->setCellValue('X' . $row, $totalCostBudget);
$monthlyReportSheet->setCellValue('Y' . $row, $project[0]->getProjectManhour($monthlyEndDate));
$monthlyReportSheet->setCellValue('Z' . $row, $recordCost);
$monthlyReportSheet->setCellValue('AA' . $row, $project[0]->getProjectManhourByMonth($monthlyStartDate, $monthlyEndDate));
$monthlyReportSheet->setCellValue('AB' . $row, $recordMonthlyCost);
$monthlyReportSheet->setCellValue('AC' . $row, $project[0]->getVendorInvoicesTotalUsd($monthlyEndDate));
$monthlyReportSheet->setCellValue('AD' . $row, $project[0]->getVendorInvoicesTotalUsdByMonth($monthlyStartDate, $monthlyEndDate));
$monthlyReportSheet->setCellValue('AE' . $row, $totalCostSinceStart);
$monthlyReportSheet->setCellValue('AF' . $row, $recordMonthlyCost + $project[0]->getVendorInvoicesTotalUsdByMonth($monthlyStartDate, $monthlyEndDate));
$row++;
}
$monthlyReportSheet->setAutoFilter('A1:AE' . $monthlyReportSheet->getHighestRow());
$monthlyReportSheet->getStyle("C1:C" . $monthlyReportSheet->getHighestRow())->getFont()->setUnderline(true);
// $monthlyReportSheet->getStyle('O2:S' . $monthlyReportSheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
// $monthlyReportSheet->getStyle('U2:W' . $monthlyReportSheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
// $monthlyReportSheet->getStyle('Y2:Y' . $monthlyReportSheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
// $monthlyReportSheet->getStyle('AA2:AE' . $monthlyReportSheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$monthlyReportSheet->getStyle('P2:T' . $monthlyReportSheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$monthlyReportSheet->getStyle('V2:X' . $monthlyReportSheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$monthlyReportSheet->getStyle('Z2:Z' . $monthlyReportSheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$monthlyReportSheet->getStyle('AB2:AD' . $monthlyReportSheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
/*
$monthlyReportSheet->getStyle('L2:M'. $monthlyReportSheet->getHighestRow())->getNumberFormat()
->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
$monthlyReportSheet->getStyle('O2:V'. $monthlyReportSheet->getHighestRow())->getNumberFormat()
->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_CURRENCY_USD_SIMPLE);
*/
// WORKSHEET: Detail Vendor Invoice (id, project name, vendor name, vendor invoice date, vendor invoice number, allocated amount)
$detailVendorInvoiceSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Detail-VendorInvoices');
$spreadsheet->addSheet($detailVendorInvoiceSheet, 1);
$detailVendorInvoiceSheet->getDefaultColumnDimension()->setWidth(35);
$detailVendorInvoiceSheet->setCellValue('A1', 'ID');
$detailVendorInvoiceSheet->setCellValue('B1', 'Project Name');
$detailVendorInvoiceSheet->setCellValue('C1', 'Vendor Name');
$detailVendorInvoiceSheet->setCellValue('D1', 'Vendor Invoice Date');
$detailVendorInvoiceSheet->setCellValue('E1', 'Vendor Invoice Number');
$detailVendorInvoiceSheet->setCellValue('F1', 'Allocated Amount');
// $detailVendorInvoiceSheet->setCellValue('G1', 'Paid Amount');
// $detailVendorInvoiceSheet->setCellValue('H1', 'Paid Date');
//set style of spreadsheet header
$detailVendorInvoiceSheet->getStyle("A1:F1")->getFont()->setBold(true);
$detailVendorInvoiceSheet->getStyle("A1:F1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$detailVendorInvoiceSheet->getStyle("A1:F1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$detailVendorInvoiceSheet->getStyle("A2:F2")->getFont()->setBold(false);
$detailVendorInvoiceSheet->getStyle("A2:F2")->getAlignment()->setWrapText(true);
$detailVendorInvoiceSheet->getStyle("A2:F2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$detailVendorInvoiceSheet->freezePane('A2');
$row = 2;
$vendorInvoiceIds = [];
foreach ($projectsList as $project) {
foreach ($project[0]->getVendorQuotationPlannings() as $vendorQuotationPlanning) {
$vqpInvoices = $vendorQuotationPlanning->getVendorQuotationPlanningVendorInvoices();
foreach ($vqpInvoices as $vqpInvoice) {
$vendorInvoice = $vqpInvoice->getVendorInvoice();
if ($vendorInvoice->getXeroStatus() === "PAID" && !in_array($vendorInvoice->getId(), $vendorInvoiceIds)) {
array_push($vendorInvoiceIds, $vendorInvoice->getId());
$detailVendorInvoiceSheet->setCellValue('A' . $row, $project['generatedId']);
$detailVendorInvoiceSheet->setCellValue('B' . $row, $project[0]->getName());
$detailVendorInvoiceSheet->setCellValue('C' . $row, $vendorQuotationPlanning->getVendor()->getName());
$detailVendorInvoiceSheet->setCellValue('D' . $row, $vendorInvoice->getInvoiceDate()->format('d/m/Y'));
$detailVendorInvoiceSheet->setCellValue('E' . $row, $vendorInvoice->getInvoiceNo());
$detailVendorInvoiceSheet->setCellValue('F' . $row, $vendorInvoice->getSubTotalUsd());
// $detailVendorInvoiceSheet->setCellValue('G' . $row, $vendorInvoice->getPaidAmount());
// $detailVendorInvoiceSheet->setCellValue('H' . $row, $vendorInvoice->getPaidDate() ? $vendorInvoice->getPaidDate()->format('d/m/Y') : '');
$row++;
};
}
}
}
$detailVendorInvoiceSheet->setAutoFilter('A1:F' . $detailVendorInvoiceSheet->getHighestRow());
// $detailVendorInvoiceSheet->getStyle('F2:F' . $detailVendorInvoiceSheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$detailVendorInvoiceSheet->getStyle('F2:F' . $detailVendorInvoiceSheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
// WORKSHEET: Detail Timesheet per project (id, project name, date, name, hours, task, hours spent, cost/hour cost)
$detailTimesheetSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Detail-Timesheets');
$spreadsheet->addSheet($detailTimesheetSheet, 2);
$detailTimesheetSheet->getDefaultColumnDimension()->setWidth(25);
$detailTimesheetSheet->setCellValue('A1', 'ID');
$detailTimesheetSheet->setCellValue('B1', 'Project Name');
$detailTimesheetSheet->setCellValue('C1', 'Date');
$detailTimesheetSheet->setCellValue('D1', 'Name');
$detailTimesheetSheet->setCellValue('E1', 'Task');
$detailTimesheetSheet->setCellValue('F1', 'Hours Spent');
$detailTimesheetSheet->setCellValue('G1', 'Hourly Cost');
$detailTimesheetSheet->setCellValue('H1', 'Total Cost');
//set style of spreadsheet header
$detailTimesheetSheet->getStyle("A1:H1")->getFont()->setBold(true);
$detailTimesheetSheet->getStyle("A1:H1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$detailTimesheetSheet->getStyle("A1:H1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$detailTimesheetSheet->getStyle("A2:H2")->getFont()->setBold(false);
$detailTimesheetSheet->getStyle("A2:H2")->getAlignment()->setWrapText(true);
$detailTimesheetSheet->getStyle("A2:H2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$detailTimesheetSheet->freezePane('A2');
$row = 2;
foreach ($projectsList as $project) {
foreach ($project[0]->getTasks() as $task) {
$timeSpents = $task->getTimeSpents();
foreach ($timeSpents as $timeSpent) {
$detailTimesheetSheet->setCellValue('A' . $row, $project['generatedId']);
$detailTimesheetSheet->setCellValue('B' . $row, $project[0]->getName());
$detailTimesheetSheet->setCellValue('C' . $row, $timeSpent->getDate()->format('d/m/Y'));
$detailTimesheetSheet->setCellValue('D' . $row, $task->getUser()->getPersonalInfo()->getFullName());
$detailTimesheetSheet->setCellValue('E' . $row, $task->getTaskType()->getName());
$detailTimesheetSheet->setCellValue('F' . $row, $timeSpent->getHours());
$detailTimesheetSheet->setCellValue('G' . $row, $timeSpent->getUser()->getHourlyRate());
$detailTimesheetSheet->setCellValue('H' . $row, $timeSpent->getUser()->getHourlyRate() * $timeSpent->getHours());
$row++;
}
}
}
// $detailTimesheetSheet->getStyle('G2:H' . $detailTimesheetSheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$detailTimesheetSheet->getStyle('G2:H' . $detailTimesheetSheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$detailTimesheetSheet->setAutoFilter('A1:H' . $detailTimesheetSheet->getHighestRow());
$spreadsheet->setActiveSheetIndex(0);
// Write the file
$writer = new Xlsx($spreadsheet);
$writer->save($filename);
if($_target == 'google'){
$gsheetURL = $googleDriveService->uploadToGoogleDrive($filename);
if($gsheetURL){
unlink($filename);
return new RedirectResponse($gsheetURL, 302);
}
}else{
$response = new Response();
$response->headers->set('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', sprintf('attachment; filename="%s"', $filename));
$response->setContent(file_get_contents($filename));
$response->setStatusCode(\Symfony\Component\HttpFoundation\Response::HTTP_OK);
$response->headers->set('Content-Transfer-Encoding', 'binary');
$response->headers->set('Pragma', 'no-cache');
$response->headers->set('Expires', '0');
unlink($filename);
return $response;
exit;
}
}
#[Route(path: '/project/export-sales-pipeline/{_target}', defaults: ['_target' => ''], name: 'export_project_sales_pipeline')]
public function exportProjectSalesPipeline(string $_target, TranslatorInterface $translator, ProjectRepository $projectRepository, TimeSpentRepository $timeSpentRepository, UserRepository $userRepository, ProjectAllocatedHoursRepository $allocatedHoursRepository, Request $request, GoogleDriveService $googleDriveService)
{
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$keyword = $request->get('keyword');
// $startDate = $request->get('start') ?: date("Y-m-d", strtotime('monday -3 week'));
$startDate = $request->get('start') ?: date("Y-m-d", strtotime('monday this week'));
$endDate = $request->get('end') ?: date("Y-m-d", strtotime('sunday this week'));
$startDateRange = '';
$endDateRange = '';
if (strpos($startDate, '/') !== false) {
$startDate = str_replace('/', '-', $startDate);
$startDate = date("Y-m-d", strtotime($startDate));
$startDateRange = date("d-M-Y", strtotime($startDate));
}
if (strpos($endDate, '/') !== false) {
$endDate = str_replace('/', '-', $endDate);
$endDate = date("Y-m-d", strtotime($endDate));
$endDateRange = date("d-M-Y", strtotime($endDate));
}
$client = $request->get('client');
$status = $request->get('status');
$type = $request->get('type');
$retainer = $request->get('retainer');
$department = $request->get('department');
$project = $request->get('project');
$assignedUser = null;
$user = $this->getUser();
$userRoles = $user->getRoles();
$spreadsheet = new Spreadsheet();
// WORKSHEET: PERIOD - OVERVIEW
$salesPipelineOverviewSheet = $spreadsheet->getActiveSheet()->setTitle('PERIOD-OVERVIEW');
$salesPipelineOverviewSheet->getColumnDimension('A')->setWidth(50);
$salesPipelineOverviewSheet->setCellValue('A1', 'OVERVIEW OF THE PERIOD');
$salesPipelineOverviewSheet->setCellValue('A2', 'From');
$salesPipelineOverviewSheet->setCellValue('A3', 'To');
$salesPipelineOverviewSheet->getStyle("A1:A3")->getFont()->setBold(true);
$salesPipelineOverviewSheet->setCellValue('A5', 'NEW LEADS');
$salesPipelineOverviewSheet->getStyle("A5")->getFont()->setBold(true);
$salesPipelineOverviewSheet->setCellValue('A6', '# of leads during that Period');
$salesPipelineOverviewSheet->setCellValue('A8', 'PROPOSALS SENT');
$salesPipelineOverviewSheet->getStyle("A8")->getFont()->setBold(true);
$salesPipelineOverviewSheet->setCellValue('A9', '# of proposals sent during that Period');
$salesPipelineOverviewSheet->setCellValue('A10', 'Total revenue of proposals sent during that Period');
$salesPipelineOverviewSheet->setCellValue('A11', 'Total 3rd party cost of proposals sent during that Period');
$salesPipelineOverviewSheet->setCellValue('A12', 'Total profit of proposals sent during that Period');
$salesPipelineOverviewSheet->setCellValue('A14', 'HOT LEADS');
$salesPipelineOverviewSheet->getStyle("A14")->getFont()->setBold(true);
$salesPipelineOverviewSheet->setCellValue('A15', '# of proposals with a win probability % Above 80%');
$salesPipelineOverviewSheet->setCellValue('A16', 'Revenue');
$salesPipelineOverviewSheet->setCellValue('A17', '3rd party cost');
$salesPipelineOverviewSheet->setCellValue('A18', 'Profit');
$salesPipelineOverviewSheet->setCellValue('A20', '# of proposals with a win probability % between 50% to 80%');
$salesPipelineOverviewSheet->setCellValue('A21', 'Revenue');
$salesPipelineOverviewSheet->setCellValue('A22', '3rd party cost');
$salesPipelineOverviewSheet->setCellValue('A23', 'Profit');
$salesPipelineOverviewSheet->setCellValue('A25', '# of proposals with a win probability % below 50%');
$salesPipelineOverviewSheet->setCellValue('A26', 'Revenue');
$salesPipelineOverviewSheet->setCellValue('A27', '3rd party cost');
$salesPipelineOverviewSheet->setCellValue('A28', 'Profit');
$salesPipelineOverviewSheet->setCellValue('A30', 'PROJECTS WON');
$salesPipelineOverviewSheet->getStyle("A30")->getFont()->setBold(true);
$salesPipelineOverviewSheet->setCellValue('A31', '# of WINS during that Period');
$salesPipelineOverviewSheet->setCellValue('A32', 'Total revenue of WON projects during that Period');
$salesPipelineOverviewSheet->setCellValue('A33', 'Total 3rd party cost of WON project during that Period');
$salesPipelineOverviewSheet->setCellValue('A34', 'Total profit of WON projects during that Period');
$salesPipelineOverviewSheet->setCellValue('A36', 'PROJECTS LOST');
$salesPipelineOverviewSheet->getStyle("A36")->getFont()->setBold(true);
$salesPipelineOverviewSheet->setCellValue('A37', '# of LOST during that Period ');
$salesPipelineOverviewSheet->setCellValue('A38', 'Total revenue of LOST projects during that Period');
$salesPipelineOverviewSheet->setCellValue('A39', 'Total 3rd party cost of LOST project during that Period');
$salesPipelineOverviewSheet->setCellValue('A40', 'Total profit of LOST projects during that Period');
$salesPipelineOverviewSheet->getColumnDimension('B')->setWidth(50);
$newLead = $projectRepository->findNewLeadOverview($startDate, $endDate);
$proposalSent = $projectRepository->findProposalSentOverview($startDate, $endDate);
$hotLeadAbove80 = $projectRepository->findHotLeadOverviewAbove80($startDate, $endDate);
$hotLeadBetween50to80 = $projectRepository->findHotLeadOverviewBetween50to80($startDate, $endDate);
$hotLeadUnder50 = $projectRepository->findHotLeadOverviewUnder50($startDate, $endDate);
$projectWon = $projectRepository->findProjectWonOverview($startDate, $endDate);
$projectLost = $projectRepository->findProjectLostOverview($startDate, $endDate);
$salesPipelineOverviewSheet->setCellValue('B2', $startDateRange);
$salesPipelineOverviewSheet->setCellValue('B3', $endDateRange);
$salesPipelineOverviewSheet->getStyle("B2:B3")->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_RIGHT);
$salesPipelineOverviewSheet->setCellValue('B6', $newLead);
$salesPipelineOverviewSheet->setCellValue('B9', $proposalSent ? $proposalSent[0]['projectCount'] : 0);
$salesPipelineOverviewSheet->setCellValue('B10', $proposalSent ? $proposalSent[0]['plannedRevenue'] : 0);
$salesPipelineOverviewSheet->setCellValue('B11', $proposalSent ? $proposalSent[0]['estimatedVendorCost'] : 0);
$salesPipelineOverviewSheet->setCellValue('B12', $proposalSent ? $proposalSent[0]['estimatedMargin'] : 0);
// $salesPipelineOverviewSheet->getStyle('B10:B12')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$salesPipelineOverviewSheet->getStyle('B10:B12')->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$salesPipelineOverviewSheet->setCellValue('B15', $hotLeadAbove80 ? $hotLeadAbove80[0]['projectCount'] : 0);
$salesPipelineOverviewSheet->setCellValue('B16', $hotLeadAbove80 ? ($hotLeadAbove80[0]['plannedRevenue'] ?: 0): 0);
$salesPipelineOverviewSheet->setCellValue('B17', $hotLeadAbove80 ? ($hotLeadAbove80[0]['estimatedVendorCost']?: 0): 0);
$salesPipelineOverviewSheet->setCellValue('B18', $hotLeadAbove80 ? ($hotLeadAbove80[0]['estimatedMargin']?: 0): 0);
// $salesPipelineOverviewSheet->getStyle('B16:B18')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$salesPipelineOverviewSheet->getStyle('B16:B18')->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$salesPipelineOverviewSheet->setCellValue('B20', $hotLeadBetween50to80 ? $hotLeadBetween50to80[0]['projectCount']: 0);
$salesPipelineOverviewSheet->setCellValue('B21', $hotLeadBetween50to80 ? ($hotLeadBetween50to80[0]['plannedRevenue']?: 0): 0);
$salesPipelineOverviewSheet->setCellValue('B22', $hotLeadBetween50to80 ? ($hotLeadBetween50to80[0]['estimatedVendorCost']?: 0): 0);
$salesPipelineOverviewSheet->setCellValue('B23', $hotLeadBetween50to80 ? ($hotLeadBetween50to80[0]['estimatedMargin']?: 0): 0);
// $salesPipelineOverviewSheet->getStyle('B21:B23')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$salesPipelineOverviewSheet->getStyle('B21:B23')->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$salesPipelineOverviewSheet->setCellValue('B25', $hotLeadUnder50 ? $hotLeadUnder50[0]['projectCount']: 0);
$salesPipelineOverviewSheet->setCellValue('B26', $hotLeadUnder50 ? ($hotLeadUnder50[0]['plannedRevenue']?: 0): 0);
$salesPipelineOverviewSheet->setCellValue('B27', $hotLeadUnder50 ? ($hotLeadUnder50[0]['estimatedVendorCost']?: 0): 0);
$salesPipelineOverviewSheet->setCellValue('B28', $hotLeadUnder50 ? ($hotLeadUnder50[0]['estimatedMargin']?: 0): 0);
// $salesPipelineOverviewSheet->getStyle('B26:B28')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$salesPipelineOverviewSheet->getStyle('B26:B28')->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$salesPipelineOverviewSheet->setCellValue('B31', $projectWon ? $projectWon[0]['projectCount'] : 0);
$salesPipelineOverviewSheet->setCellValue('B32', $projectWon ? $projectWon[0]['plannedRevenue'] : 0);
$salesPipelineOverviewSheet->setCellValue('B33', $projectWon ? $projectWon[0]['estimatedVendorCost'] : 0);
$salesPipelineOverviewSheet->setCellValue('B34', $projectWon ? $projectWon[0]['estimatedMargin'] : 0);
// $salesPipelineOverviewSheet->getStyle('B32:B34')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$salesPipelineOverviewSheet->getStyle('B32:B34')->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$salesPipelineOverviewSheet->setCellValue('B37', $projectLost ? $projectLost[0]['projectCount'] : 0);
$salesPipelineOverviewSheet->setCellValue('B38', $projectLost ? (is_null($projectLost[0]['plannedRevenue'])? 0: $projectLost[0]['plannedRevenue']) : 0);
$salesPipelineOverviewSheet->setCellValue('B39', $projectLost ? (is_null($projectLost[0]['estimatedVendorCost'])? 0: $projectLost[0]['estimatedVendorCost']) : 0);
$salesPipelineOverviewSheet->setCellValue('B40', $projectLost ? (is_null($projectLost[0]['estimatedMargin'])? 0: $projectLost[0]['estimatedMargin']) : 0);
// $salesPipelineOverviewSheet->getStyle('B38:B40')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$salesPipelineOverviewSheet->getStyle('B38:B40')->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
if($_SERVER['APP_ENV'] != "prod"){
$salesPipelineOverviewSheet->setCellValue('D6', '[leadDate in date range]');
$salesPipelineOverviewSheet->setCellValue('D9', '[sentDate in date range]');
$salesPipelineOverviewSheet->setCellValue('D15', '[sentDate in date range and % Above 80% where sentDate != winDate and lead != 8]');
$salesPipelineOverviewSheet->setCellValue('D20', '[sentDate in date range and % between 50% to 80% where sentDate != winDate and lead != 8]');
$salesPipelineOverviewSheet->setCellValue('D25', '[sentDate in date range and % below 50% where sentDate != winDate and lead != 8]');
$salesPipelineOverviewSheet->setCellValue('D31', '[winDate in date range and lead = 8 (Won)]');
$salesPipelineOverviewSheet->setCellValue('D37', '[leadFailDate in date range and lead = 9 (Lost)]');
}
$filename = "Sales_Pipeline_Report";
$filename .= $startDate ? "_" . urlencode($startDate) : "";
$filename .= $endDate ? "-" . urlencode($endDate) : "";
$filename .= $startDate ? '.xlsx' : "_" . date('Y-m-d') . '.xlsx';
// WORKSHEET: Detail Sales Pipeline
$detailSheet = ['allLeadSheet' => 'PERIOD-ALL LEADS', 'allSentSheet' => 'PERIOD-ALL SENT', 'allWinSheet' => 'PERIOD-ALL WIN', 'allLostSheet' => 'PERIOD-ALL LOST'];
$sheetIdx = 1;
foreach($detailSheet as $varSheet => $descSheet){
${$varSheet} = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, $descSheet);
$spreadsheet->addSheet(${$varSheet}, $sheetIdx);
$sheetIdx++;
}
foreach($detailSheet as $varSheet => $descSheet){
${$varSheet}->getDefaultColumnDimension()->setWidth(20);
${$varSheet}->getColumnDimension('B')->setWidth(35);
${$varSheet}->getColumnDimension('C')->setWidth(35);
${$varSheet}->getColumnDimension('I')->setWidth(30);
${$varSheet}->getColumnDimension('J')->setWidth(35);
${$varSheet}->getColumnDimension('L')->setWidth(35);
${$varSheet}->setCellValue('A1', 'ID');
${$varSheet}->setCellValue('B1', 'Client Name');
${$varSheet}->setCellValue('C1', 'Project Name');
${$varSheet}->setCellValue('D1', 'Estimated Project Billings (USD)');
${$varSheet}->setCellValue('E1', 'Total 3rd Party Cost (USD)');
${$varSheet}->setCellValue('F1', 'Total Profit (USD)');
${$varSheet}->setCellValue('G1', 'New Lead Date (DDMMYYYY)');
${$varSheet}->setCellValue('H1', 'Sent Date (DDMMYYYY)');
${$varSheet}->setCellValue('I1', 'Win Date (DDMMYYYY)');
${$varSheet}->setCellValue('J1', 'Lost Date (DDMMYYYY)');
${$varSheet}->setCellValue('K1', 'Project Status');
${$varSheet}->setCellValue('L1', 'PIC');
${$varSheet}->setCellValue('M1', 'Description');
${$varSheet}->setCellValue('N1', '% Likely To Win');
$lastCol = 'N';
if($varSheet == 'allLostSheet'){
${$varSheet}->setCellValue('O1', 'Lost Reason');
$lastCol = 'O';
}
${$varSheet}->getStyle("A1:".$lastCol."1")->getFont()->setBold(true);
${$varSheet}->getStyle("A1:".$lastCol."1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
${$varSheet}->getStyle("A1:".$lastCol."1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
${$varSheet}->getStyle("A2:".$lastCol."2")->getFont()->setBold(false);
${$varSheet}->getStyle("A2:".$lastCol."2")->getAlignment()->setWrapText(true);
${$varSheet}->getStyle("A2:".$lastCol."2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
/*
if($_SERVER['APP_ENV'] != "prod"){
${$varSheet}->setCellValue('N1', 'Type (temporary)');
${$varSheet}->setCellValue('O1', 'FROM');
}
//set style of spreadsheet header
if($_SERVER['APP_ENV'] != "prod"){
${$varSheet}->getStyle("A1:O1")->getFont()->setBold(true);
${$varSheet}->getStyle("A1:O1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
${$varSheet}->getStyle("A1:O1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
${$varSheet}->getStyle("A2:O2")->getFont()->setBold(false);
${$varSheet}->getStyle("A2:O2")->getAlignment()->setWrapText(true);
${$varSheet}->getStyle("A2:O2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
}else{
${$varSheet}->getStyle("A1:M1")->getFont()->setBold(true);
${$varSheet}->getStyle("A1:M1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
${$varSheet}->getStyle("A1:M1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
${$varSheet}->getStyle("A2:M2")->getFont()->setBold(false);
${$varSheet}->getStyle("A2:M2")->getAlignment()->setWrapText(true);
${$varSheet}->getStyle("A2:M2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
}
*/
}
$leadStatus = [
1 => 'Lead',
2 => 'First discussion',
3 => 'Brief received',
4 => 'Work on proposal or quote',
5 => 'Proposal sent -> to follow up',
6 => 'Verbally Accepted',
7 => 'Work on Contract',
8 => 'Won (Contract or Quote signed)',
9 => 'Lost',
91 => 'Lost / No answer',
92 => 'No go / Closed'
];
// $detailSales = [];
$newLeads = $projectRepository->findNewLead($startDate, $endDate);
$proposalSents = $projectRepository->findProposalSent($startDate, $endDate);
$projectWons = $projectRepository->findProjectWon($startDate, $endDate);
$projectLosts = $projectRepository->findProjectLost($startDate, $endDate);
$contentSheets = ['allLeadSheet' => 'newLeads', 'allSentSheet' => 'proposalSents', 'allWinSheet' => 'projectWons', 'allLostSheet' => 'projectLosts'];
foreach($detailSheet as $varSheet => $descSheet){
$row = 2;
foreach(${$contentSheets[$varSheet]} as $salePipeline){
${$varSheet}->setCellValue('A'.$row, $salePipeline['generatedId']);
${$varSheet}->setCellValue('B'.$row, $salePipeline['clientName']);
${$varSheet}->setCellValue('C'.$row, $salePipeline['name']);
${$varSheet}->setCellValue('D'.$row, $salePipeline[0]->getPlannedRevenueUsd());
${$varSheet}->setCellValue('E'.$row, $salePipeline[0]->getEstimatedVendorCostUsd());
${$varSheet}->setCellValue('F'.$row, $salePipeline[0]->getEstimatedProfitUsd());
${$varSheet}->setCellValue('G'.$row, $salePipeline['leadDate'] ? $salePipeline['leadDate']->format("d-m-Y"): '');
${$varSheet}->setCellValue('H'.$row, $salePipeline[0]->getProposalDate() ? $salePipeline[0]->getProposalDate()->format("d-m-Y") : '');
${$varSheet}->setCellValue('I'.$row, $salePipeline[0]->getWinDate() ? $salePipeline[0]->getWinDate()->format("d-m-Y") : '');
${$varSheet}->setCellValue('J'.$row, $salePipeline[0]->getLeadFailDate() ? $salePipeline[0]->getLeadFailDate()->format("d-m-Y") : '');
${$varSheet}->setCellValue('K'.$row, $salePipeline['lead'].' - '.$leadStatus[$salePipeline['lead']]);
${$varSheet}->setCellValue('L'.$row, $salePipeline['picFirstName'].' '.$salePipeline['picMiddleName'].' '.$salePipeline['picLastName']);
${$varSheet}->setCellValue('M'.$row, $salePipeline[0]->getDescription());
${$varSheet}->setCellValue('N'.$row, $salePipeline[0]->getProbabilityText());
if($varSheet == 'allLostSheet'){
$leadFailText = $salePipeline[0]->getLeadFail() ? $salePipeline[0]->getLeadFail()->getTitle() : null;
$leadFailReason = $salePipeline[0]->getLeadFailNote() ?: null;
$leadFailShow = $leadFailText.($leadFailReason ? ': '.$leadFailReason : '');
// ${$varSheet}->setCellValue('O'.$row, $salePipeline[0]->getLeadFailNote());
${$varSheet}->setCellValue('O'.$row, $salePipeline[0]->getLeadLostReason());
}
// if($_SERVER['APP_ENV'] != "prod"){
// ${$varSheet}->setCellValue('N'.$row, $salePipeline[0]->getType());
// ${$varSheet}->setCellValue('O'.$row, isset($salePipeline['FROM'])? $salePipeline['FROM'] : '');
// }
$row++;
}
${$varSheet}->setAutoFilter('A1:'. $lastCol . ${$varSheet}->getHighestRow());
// ${$varSheet}->getStyle('D2:F' . ${$varSheet}->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
${$varSheet}->getStyle('D2:F' . ${$varSheet}->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
${$varSheet}->getStyle("M2:M".${$varSheet}->getHighestRow())->getAlignment()->setWrapText(true);
${$varSheet}->getStyle("N2:N".${$varSheet}->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);
${$varSheet}->getStyle("A2:N".${$varSheet}->getHighestRow())->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_CENTER);
}
/*
$i = 0;
foreach($newLeads as $lead){
$newLeads[$i]['FROM'] = 'NEW LEAD';
$i++;
}
$j = 0;
foreach($projectWons as $won){
$projectWons[$j]['FROM'] = 'PROJECT WON';
$j++;
}
$k = 0;
foreach($projectLosts as $lost){
$projectLosts[$k]['FROM'] = 'PROJECT LOST';
$k++;
}
$m = 0;
foreach($proposalSents as $sent){
$proposalSents[$m]['FROM'] = 'PROPOSAL SENT';
$m++;
}
if(!is_null($newLeads)) {
$detailSales = array_merge($detailSales, $newLeads);
}
if(!is_null($proposalSents)) {
$detailSales = array_merge($detailSales, $proposalSents);
}
if(!is_null($projectWons)) {
$detailSales = array_merge($detailSales, $projectWons);
}
if(!is_null($projectLosts)) {
$detailSales = array_merge($detailSales, $projectLosts);
}
$newDetailSales = array();
foreach ($detailSales as $detailSale) {
$id = $detailSale['id'];
if (!isset($newDetailSales[$id])) {
$newDetailSales[$id] = $detailSale;
}
}
$detailSalesUnique = array_values($newDetailSales);
*/
// LOST REASONS
$lostReasonSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'LOST REASONS');
$spreadsheet->addSheet($lostReasonSheet, 5);
$lostReasonSheet->getDefaultColumnDimension()->setWidth(20);
$lostReasonSheet->getColumnDimension('B')->setWidth(10);
$lostReasonSheet->getColumnDimension('C')->setWidth(30);
$lostReasonSheet->getColumnDimension('D')->setWidth(30);
$lostReasonSheet->setCellValue('A1', 'OVERVIEW OF THE PERIOD');
$lostReasonSheet->setCellValue('A2', 'From');
$lostReasonSheet->setCellValue('A3', 'To');
$lostReasonSheet->getStyle("A1:A3")->getFont()->setBold(true);
$lostReasonSheet->setCellValue('B2', $startDateRange);
$lostReasonSheet->setCellValue('B3', $endDateRange);
$lostReasonSheet->setCellValue('A4', 'REASON');
$lostReasonSheet->setCellValue('B4', '% LOST');
$lostReasonSheet->setCellValue('C4', 'REVENUE USD');
$lostReasonSheet->setCellValue('D4', 'GROSS MARGIN USD');
$lostReasonSheet->setCellValue('A5', 'Proposal quality');
$lostReasonSheet->setCellValue('A6', 'Team Ressources');
$lostReasonSheet->setCellValue('A7', 'Price');
$lostReasonSheet->setCellValue('A8', 'Timeline');
$lostReasonSheet->setCellValue('A9', 'Postponed');
$lostReasonSheet->setCellValue('A10', 'Cancelled');
$lostReasonSheet->setCellValue('A11', 'No answer');
$lostReasonSheet->setCellValue('A12', 'No go (MT)');
$lostReasonSheet->setCellValue('A13', 'Other');
$lostReasonSheet->setCellValue('A14', 'TOTAL');
$lostReasonSheet->getStyle("A4:D4")->getFont()->setBold(true);
$lostReasonSheet->getStyle("A4:D4")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$lostReasonSheet->getStyle("A4:D4")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$lostReasonSheet->getStyle("A14:D14")->getFont()->setBold(true);
$lostReasonSheet->getStyle("A14:D14")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$lostReasonSheet->getStyle("A14:D14")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$projectlostResons = ['Proposal Quality', 'MT Team Resource', 'Price', 'Timeline', 'Postponed', 'Cancelled', 'No Answer', 'No Go (MT)', 'Other'];
$projectLostGroupByReason = [];
foreach ($projectlostResons as $lostReason){
if(!isset($projectLostGroupByReason[$lostReason])){
$projectLostGroupByReason[$lostReason] = [
'total' => 0,
'revenueUsd' => 0,
'estVendorCost' => 0,
'estMargin' => 0,
'grossMargin' => 0,
'data' => []
];
}
}
$projectLostByReasons = $projectRepository->findProjectLost($startDate, $endDate);
foreach($projectLostByReasons as $projectLost){
$failNote = $projectLost['failNote'];
if(!is_null($failNote) && isset($projectLostGroupByReason[$failNote])){
// if($failNote == $projectLostGroupByReason[$failNote]){
array_push($projectLostGroupByReason[$failNote]['data'], $projectLost);
$projectLostGroupByReason[$failNote]['total'] += 1;
$projectLostGroupByReason[$failNote]['revenueUsd'] += $projectLost['plannedRevenueUsd'];
$projectLostGroupByReason[$failNote]['estVendorCost'] += $projectLost[0]->getEstimatedVendorCostUsd();
$projectLostGroupByReason[$failNote]['estMargin'] += $projectLost['plannedRevenueUsd'] - $projectLost[0]->getEstimatedVendorCostUsd();
// }
}else{
array_push($projectLostGroupByReason['Other']['data'], $projectLost);
$projectLostGroupByReason['Other']['total'] += 1;
$projectLostGroupByReason['Other']['revenueUsd'] += $projectLost['plannedRevenueUsd'];
$projectLostGroupByReason['Other']['estVendorCost'] += $projectLost[0]->getEstimatedVendorCostUsd();
$projectLostGroupByReason['Other']['estMargin'] += $projectLost['plannedRevenueUsd'] - $projectLost[0]->getEstimatedVendorCostUsd();
}
}
$totalRevenueUsdLosts = 0;
$totalEstMarginLosts = 0;
$totalQtyLost = 0;
foreach($projectLostGroupByReason as $groupLosts){
$totalRevenueUsdLosts += $groupLosts['revenueUsd'];
$totalEstMarginLosts += $groupLosts['estMargin'];
$totalQtyLost += $groupLosts['total'];
}
$row = 5;
foreach($projectLostGroupByReason as $groupLosts){
$lostReasonSheet->setCellValue('B'. $row, $totalQtyLost > 0 ? ($groupLosts['total'] / $totalQtyLost) : 0);
$lostReasonSheet->setCellValue('C'. $row, $groupLosts['revenueUsd']);
$lostReasonSheet->setCellValue('D'. $row, $groupLosts['estMargin']);
$row++;
}
// $lostReasonSheet->setCellValue('B'. $row, ($groupLosts['total'] / $totalQtyLost) * 100);
$lostReasonSheet->setCellValue('C'. $row, $totalRevenueUsdLosts);
$lostReasonSheet->setCellValue('D'. $row, $totalEstMarginLosts);
// $lostReasonSheet->getStyle('C5:D14')->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$lostReasonSheet->getStyle('C5:D14')->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$lostReasonSheet->getStyle("B5:B14")->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_PERCENTAGE_00);
$spreadsheet->setActiveSheetIndex(0);
// Write the file
$writer = new Xlsx($spreadsheet);
$writer->save($filename);
if($_target == 'google'){
$gsheetURL = $googleDriveService->uploadToGoogleDrive($filename);
if($gsheetURL){
unlink($filename);
return new RedirectResponse($gsheetURL, 302);
}
}else{
$response = new Response();
$response->headers->set('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', sprintf('attachment; filename="%s"', $filename));
$response->setContent(file_get_contents($filename));
$response->setStatusCode(\Symfony\Component\HttpFoundation\Response::HTTP_OK);
$response->headers->set('Content-Transfer-Encoding', 'binary');
$response->headers->set('Pragma', 'no-cache');
$response->headers->set('Expires', '0');
unlink($filename);
return $response;
exit;
}
}
#[Route(path: '/project/export-revenue-forecast/{_target}', defaults: ['_target' => ''], name: 'export_project_revenue_forecast')]
public function exportProjectRevenueForecast(string $_target, Request $request, ProjectService $projectService)
{
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$keyword = $request->get('keyword');
$startDate = $request->get('start');
$endDate = $request->get('end');
$filterType = $request->get('filter_type');
if (strpos($startDate, '/') !== false) {
$startDate = str_replace('/', '-', $startDate);
$startDate = date("Y-m-d", strtotime($startDate));
}
if (strpos($endDate, '/') !== false) {
$endDate = str_replace('/', '-', $endDate);
$endDate = date("Y-m-d", strtotime($endDate));
}
$client = $request->get('client');
$status = $request->get('status');
$type = 'CONFIRMED;LEAD';
$retainer = $request->get('retainer');
$department = $request->get('department');
$project = $request->get('project');
$pic = $request->get('pic');
$assignedUser = null;
$showDeleted = false;
$user = $this->getUser();
$userRoles = $user->getRoles();
$isCashflow = true;
$isAdmin = true;
if($filterType == 'project_start'){
$result = $projectService->exportProjectRevenueForecastProjectDate($isAdmin, $keyword, $startDate, $endDate, $client, $status, $type, $retainer, $department, $project, $assignedUser, $pic, $showDeleted, $baseurl, $isCashflow, $_target);
}elseif($filterType == 'invoice_due'){
$result = $projectService->exportProjectRevenueForecast($isAdmin, $keyword, $startDate, $endDate, $client, $status, $type, $retainer, $department, $project, $assignedUser, $pic, $showDeleted, $baseurl, $isCashflow, $_target);
}
return $result;
}
#[Route(path: '/project/export-revenue-recognition/{_target}', defaults: ['_target' => ''], name: 'export_project_revenue_recognition')]
public function exportProjectRevenueRecognition(string $_target, Request $request, ProjectService $projectService)
{
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$keyword = $request->get('keyword');
$startDate = $request->get('start');
$endDate = $request->get('end');
$filterType = $request->get('filter_type');
if (strpos($startDate, '/') !== false) {
$startDate = str_replace('/', '-', $startDate);
$startDate = date("Y-m-d", strtotime($startDate));
}
if (strpos($endDate, '/') !== false) {
$endDate = str_replace('/', '-', $endDate);
$endDate = date("Y-m-d", strtotime($endDate));
}
$client = $request->get('client');
$status = $request->get('status');
$type = 'CONFIRMED;LEAD';
$retainer = $request->get('retainer');
$department = $request->get('department');
$project = $request->get('project');
$pic = $request->get('pic');
$assignedUser = null;
$showDeleted = false;
$user = $this->getUser();
$userRoles = $user->getRoles();
$isCashflow = false;
$isAdmin = true;
if($filterType == 'project_start'){
$result = $projectService->exportProjectRevenueForecastProjectDate($isAdmin, $keyword, $startDate, $endDate, $client, $status, $type, $retainer, $department, $project, $assignedUser, $pic, $showDeleted, $baseurl, $isCashflow, $_target);
}elseif($filterType == 'invoice_issue'){
$result = $projectService->exportProjectRevenueForecast($isAdmin, $keyword, $startDate, $endDate, $client, $status, $type, $retainer, $department, $project, $assignedUser, $pic, $showDeleted, $baseurl, $isCashflow, $_target);
}
return $result;
}
#[Route(path: '/project/project-revenue-vs-cost-report/{_target}', defaults: ['_target' => ''], name: 'project_revenue_vs_cost_report')]
public function reportProjectRevenueCostReport(string $_target, Request $request, ProjectService $projectService)
{
$result ='';
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$isAdmin = true;
$financialYear = $request->get('financialYear');
// $startDate = $request->get('start');
// $endDate = $request->get('end');
// if (strpos($startDate, '/') !== false) {
// $startDate = str_replace('/', '-', $startDate);
// $startDate = date("Y-m-d", strtotime($startDate));
// }
// if (strpos($endDate, '/') !== false) {
// $endDate = str_replace('/', '-', $endDate);
// $endDate = date("Y-m-d", strtotime($endDate));
// }
// $client = $request->get('client');
// $status = $request->get('status');
// $type = 'CONFIRMED;LEAD';
// $retainer = $request->get('retainer');
// $department = $request->get('department');
// $project = $request->get('project');
// $pic = $request->get('pic');
// $assignedUser = null;
// $showDeleted = false;
// $user = $this->getUser();
// $userRoles = $user->getRoles();
// $isAdmin = true;
$result = $projectService->exportProjectRevenueCost($isAdmin, $financialYear, $baseurl, $_target);
return $result;
}
#[Route(path: '/project/project-revenue-vs-cost-report-range/{_target}', defaults: ['_target' => ''], name: 'project_revenue_vs_cost_report_range')]
public function reportProjectRevenueCostReportRange(string $_target, Request $request, ProjectService $projectService)
{
$result ='';
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$isAdmin = true;
$startDate = $request->get('start');
$endDate = $request->get('end');
if (strpos($startDate, '/') !== false) {
$startDate = str_replace('/', '-', $startDate);
$startDate = date("Y-m-d", strtotime($startDate));
}
if (strpos($endDate, '/') !== false) {
$endDate = str_replace('/', '-', $endDate);
$endDate = date("Y-m-d", strtotime($endDate));
}
// $client = $request->get('client');
// $status = $request->get('status');
// $type = 'CONFIRMED;LEAD';
// $retainer = $request->get('retainer');
// $department = $request->get('department');
// $project = $request->get('project');
// $pic = $request->get('pic');
// $assignedUser = null;
// $showDeleted = false;
// $user = $this->getUser();
// $userRoles = $user->getRoles();
// $isAdmin = true;
$result = $projectService->exportProjectRevenueCostDateRange($isAdmin, $startDate, $endDate, $baseurl, $_target);
return $result;
}
#[Route(path: '/project/vendor-invoices-report/{_target}', defaults: ['_target' => ''], name: 'project_vendor_invoices_report')]
public function projectVendorInvoicesReport(string $_target, Request $request, ProjectService $projectService)
{
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$keyword = $request->get('keyword');
$startDate = $request->get('start');
$endDate = $request->get('end');
if (strpos($startDate, '/') !== false) {
$startDate = str_replace('/', '-', $startDate);
$startDate = date("Y-m-d", strtotime($startDate));
}
if (strpos($endDate, '/') !== false) {
$endDate = str_replace('/', '-', $endDate);
$endDate = date("Y-m-d", strtotime($endDate));
}
$client = $request->get('client');
$status = $request->get('status');
$type = 'CONFIRMED;LEAD';
$retainer = $request->get('retainer');
$department = $request->get('department');
$project = $request->get('project');
$pic = $request->get('pic');
$assignedUser = null;
$showDeleted = false;
$user = $this->getUser();
$userRoles = $user->getRoles();
$isAdmin = true;
$result = $projectService->exportProjectVendorInvoices($isAdmin, $keyword, $startDate, $endDate, $client, $status, $type, $retainer, $department, $project, $assignedUser, $pic, $showDeleted, $baseurl, $_target);
return $result;
}
#[Route(path: '/project/client-invoices-report/{_target}', defaults: ['_target' => ''], name: 'project_client_invoices_report')]
public function projectClientInvoicesReport(string $_target, Request $request, ProjectService $projectService)
{
$baseurl = $request->getScheme() . '://' . $request->getHttpHost() . $request->getBasePath();
$keyword = $request->get('keyword');
$startDate = $request->get('start');
$endDate = $request->get('end');
if (strpos($startDate, '/') !== false) {
$startDate = str_replace('/', '-', $startDate);
$startDate = date("Y-m-d", strtotime($startDate));
}
if (strpos($endDate, '/') !== false) {
$endDate = str_replace('/', '-', $endDate);
$endDate = date("Y-m-d", strtotime($endDate));
}
$client = $request->get('client');
$status = $request->get('status');
$type = 'CONFIRMED;LEAD';
$retainer = $request->get('retainer');
$department = $request->get('department');
$project = $request->get('project');
$pic = $request->get('pic');
$assignedUser = null;
$showDeleted = false;
$user = $this->getUser();
$userRoles = $user->getRoles();
$isAdmin = true;
$result = $projectService->exportProjectClientInvoices($isAdmin, $keyword, $startDate, $endDate, $client, $status, $type, $retainer, $department, $project, $assignedUser, $pic, $showDeleted, $baseurl, $_target);
return $result;
}
/**
* @param int $id
*/
#[Route(path: '/project/export-detail/{id}/{_target}', defaults: ['id' => null, '_target' => ''], name: 'export_project_detail')]
public function exportProjectDetail($id, string $_target, TranslatorInterface $translator, ProjectRepository $projectRepository, ProjectAllocatedHoursRepository $allocatedHoursRepository, TimeSpentRepository $timeSpentRepository, UserRepository $userRepository, TaskRepository $taskRepository, GoogleDriveService $googleDriveService)
{
$projectId = $id ?: null;
$assignedUser = null;
$user = $this->getUser();
$userRoles = $user->getRoles();
if ($this->isGranted("ROLE_TEAM_LEADER") or $this->isGranted("ROLE_MARCOM") or $this->isGranted("ROLE_FINANCE")) {
$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', 'Client Name');
$sheet->setCellValue('B1', 'ID');
$sheet->setCellValue('C1', 'Project Name');
$sheet->setCellValue('D1', 'Project Status');
$sheet->setCellValue('E1', 'PIC');
$sheet->setCellValue('F1', 'Plan File');
$sheet->setCellValue('G1', 'Plan URL');
$sheet->setCellValue('H1', 'Start Date');
$sheet->setCellValue('I1', 'End Date');
$sheet->setCellValue('J1', 'Invoice Number(s)');
$sheet->setCellValue('K1', 'SO Number(s)');
$sheet->setCellValue('L1', 'Estimated Project Billings');
$sheet->setCellValue('M1', 'Estimated Project Billings Based on SO');
$sheet->setCellValue('N1', 'Estimated Project Billings Based on INV');
$sheet->setCellValue('O1', 'Estimated Project Hours');
// $sheet->setCellValue('O1', 'Man Hours Cost');
$sheet->setCellValue('P1', 'Employee Time Record Hours');
$sheet->setCellValue('Q1', 'Vendor Costs Based On Plan');
$sheet->setCellValue('R1', 'Vendor Costs Based On INV');
$sheet->setCellValue('S1', 'Project Type');
$sheet->setCellValue('T1', 'Profit (SO amount - 3rd party cost planned - man hours)');
$sheet->setCellValue('U1', 'Profit (INV amount - 3rd party cost planned - man hours)');
$sheet->setCellValue('V1', 'Profit (SO amount - 3rd party invoices - man hours)');
$sheet->setCellValue('W1', 'Profit (INV amount - 3rd party invoices - man hours)');
$sheet->getDefaultColumnDimension()->setWidth(25);
$sheet->getStyle("A1:W1")->getFont()->setBold(true);
$sheet->getStyle("A1:W1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$sheet->getStyle("A1:W1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$sheet->getStyle("A2:W2")->getFont()->setBold(false);
$sheet->getStyle("A2:W2")->getAlignment()->setWrapText(true);
$sheet->getStyle("A2:W2")->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
$filename = "ProjectDetail_" . $projectId . "_" . date('Y-m-d') . '.xlsx';
$projectsList = $projectRepository->findByPage(0, 9999, "", "ASC", null, "", "", "", 0, null, $projectId, null, 0, $assignedUser);
$n = 0;
foreach ($projectsList as $projectDetail) {
$projectsList[$n]['cost'] = $projectDetail[0]->getProjectCost();
$projectsList[$n]['profit'] = $projectDetail[0]->getProjectProfit1();
$projectsList[$n]['leadStatusText'] = $projectDetail[0]->getLeadStatus() ? $projectDetail[0]->getLeadStatus()->getName() : null;
$n++;
};
$row = $sheet->getHighestRow();
foreach ($projectsList as $project) {
$picName = $project['picMiddleName'] ? $project['picFirstName'] . ' ' . $project['picMiddleName'] . ' ' . $project['picLastName'] : $project['picFirstName'] . ' ' . $project['picLastName'];
//$sheet->insertNewRowBefore($row);
$sheet->setCellValue('A' . $row, $project['clientName']);
$sheet->setCellValue('B' . $row, $project['generatedId']);
$sheet->setCellValue('C' . $row, $project['name']);
$sheet->setCellValue('D' . $row, $project['type'] == 'LEAD' ? $project['lead'] . ' - ' . $project[0]->getLeadStatus()->getName() : ($project['status'] ? $project['status'] : '-'));
$sheet->setCellValue('E' . $row, $picName);
$sheet->setCellValue('F' . $row, $project['planFilename'] ? $project['planFilename'] : "-");
if ($project['planFilename']) {
$sheet->getCell('F' . $row)->getHyperlink()->setUrl($project['planFilepath']);
}
$sheet->setCellValue('G' . $row, $project['planURL'] ? $project['planURL'] : "-");
if ($project['planURL']) {
$sheet->getCell('G' . $row)->getHyperlink()->setUrl($project['planURL']);
}
$sheet->setCellValue('H' . $row, $project['startDate'] ? $project['startDate']->format('Y-m-d') : '-');
$sheet->setCellValue('I' . $row, $project['endDate'] ? $project['endDate']->format('Y-m-d') : '-');
$multiRow = $row;
$proSalesOrders = $project[0]->getProjectSalesOrders();
foreach ($proSalesOrders as $so) {
$sheet->setCellValue('J' . $multiRow, $so->getSalesOrder()->getSalesOrderNo());
$invoices = $so->getSalesOrder()->getSalesOrderInvoices($project['id']);
$multiMultiRow = $multiRow;
foreach ($invoices as $invoice) {
$sheet->setCellValue('K' . $multiMultiRow, $invoice->getInvoice()->getInvoiceNo());
$multiMultiRow++;
}
$multiRow = $multiMultiRow;
}
if ($project['type'] == 'INTERNAL') {
$sheet->setCellValue('L' . $row, '-');
} else {
//$sheet->setCellValue('L' . $row, $project['type'] == 'LEAD' ? $project['estimatedProfit'] : $project['plannedRevenue']);
// $sheet->setCellValue('L' . $row, $project['type'] == 'LEAD' ? $project[0]->getEstimatedProfitUsd() : $project[0]->getPlannedRevenueUsd());
$sheet->setCellValue('L' . $row, $project[0]->getPlannedRevenueUsd());
}
$sheet->setCellValue('M' . $row, $project[0]->getSoTotalUsd());
$sheet->setCellValue('N' . $row, $project[0]->getInvoicesTotalUsd());
// $sheet->setCellValue('O' . $row, $project[0]->getProjectManhourCost());
$resourceAllocations = $allocatedHoursRepository->findByProject('', '', $project['id']);
$totalEstimatedProjectHours = 0;
foreach ($resourceAllocations as $allocation) {
$totalEstimatedProjectHours += intval($allocation->getHours());
}
$estimatedProjectHours = !empty($totalEstimatedProjectHours) ? $totalEstimatedProjectHours : '-';
$sheet->setCellValue('O' . $row, $estimatedProjectHours);
// Calculate employee time record
$_id = intval($project['id']);
$departments = $timeSpentRepository->getDepartmentsForProject($_id, $project[0]->getStartDate(), $project[0]->getEndDate());
$totalHoursAll = 0;
$totalCostAll = 0;
for ($i = 0; $i < sizeof($departments); $i++) {
$employeeSummaryList = $userRepository->findTaskSummary($_id, $departments[$i]['id']);
$departments[$i]['employeeSummaryList'] = $employeeSummaryList;
$tmpHours = 0;
$tmpCost = 0;
for ($j = 0; $j < sizeof($employeeSummaryList); $j++) {
$tmpHours = $tmpHours + $employeeSummaryList[$j]['hours'];
$tmpCost = $tmpCost + $employeeSummaryList[$j]['cost'];
}
//$totalManpowerCost += $tmpCost;
$departments[$i]['totalCost'] = $tmpCost;
$departments[$i]['totalHours'] = $tmpHours;
}
if (count($departments) > 0) {
foreach ($departments as $department) {
$totalHoursAll += $department['totalHours'];
$totalCostAll += $department['totalCost'];
}
}
$employeeTimeRecordHours = !empty($totalHoursAll) ? $totalHoursAll : '-';
$sheet->setCellValue('P' . $row, $employeeTimeRecordHours);
$sheet->setCellValue('Q' . $row, $project[0]->getVendorPlannedTotalUsd());
$sheet->setCellValue('R' . $row, $project[0]->getVendorInvoicesTotalUsd());
$sheet->setCellValue('S' . $row, $project['type']);
$sheet->setCellValue('T' . $row, $project[0]->getProjectProfit3());
$sheet->setCellValue('U' . $row, $project[0]->getProjectProfit4());
$sheet->setCellValue('V' . $row, $project[0]->getProjectProfit2());
$sheet->setCellValue('W' . $row, $project[0]->getProjectProfit1());
$row = $multiRow + 1;
}
$sheet->setAutoFilter('A1:W' . $sheet->getHighestRow());
// $sheet->getStyle('L2:N' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
// $sheet->getStyle('Q2:R' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
// $sheet->getStyle('T2:W' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$sheet->getStyle('L2:N' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$sheet->getStyle('Q2:R' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$sheet->getStyle('T2:W' . $sheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
$ManHoursSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Man Hours');
$spreadsheet->addSheet($ManHoursSheet, 1);
$ManHoursSheet = $spreadsheet->getSheet(1);
$column = [];
for ($i = 65; $i < 91; $i++) {
$column[] = chr($i);
}
$projectMembers = $project[0]->getProjectMembers();
$allDepartment = $user->assignedCompany()->getDepartments();
$ManHoursSheet->setCellValue('A1', 'Departments \ Users');
$i = 2;
foreach ($allDepartment as $department) {
$ManHoursSheet->setCellValue('A' . $i, $department->getName());
$i++;
}
$ManHoursSheet->setCellValue('A' . $i, 'Total Hours');
$j = 1;
foreach ($projectMembers as $member) {
if(!isset($column[$j])) continue;
$ManHoursSheet->setCellValue($column[$j] . '1', $member->getUser()->getPersonalInfo()->getFirstName());
$j++;
}
if(isset($column[$j])) $ManHoursSheet->setCellValue($column[$j] . '1', 'Total Hours');
$hours = [];
$row = 2;
$AllTotalRowHours = 0;
foreach ($allDepartment as $department) {
$col = 1;
$totalRowHours = 0;
foreach ($projectMembers as $member) {
$tasks = $taskRepository->findTaskbyProjectDepartment($projectId, $member->getUser()->getId(), $department->getId());
if ($tasks) {
$taskHours = 0;
foreach ($tasks as $task) {
$taskHours += $task->getWeeklyTotalHours();
}
$dataHours = $taskHours;
$hours['column'][$member->getUser()->getId()][$department->getId()] = $taskHours;
} else {
$dataHours = 0;
$hours['column'][$member->getUser()->getId()][$department->getId()] = 0;
}
if(!isset($column[$col])) continue;
$ManHoursSheet->setCellValue($column[$col] . $row, !empty($dataHours) ? $dataHours : '-');
$totalRowHours += $dataHours;
$col++;
}
if(isset($column[$col])) $ManHoursSheet->setCellValue($column[$col] . $row, $totalRowHours == 0 ? '-' : $totalRowHours);
$hours['totalRow'][$department->getId()] = $totalRowHours;
$AllTotalRowHours += $totalRowHours;
$row++;
}
if(isset($column[$col])) $ManHoursSheet->setCellValue($column[$col] . $row, $AllTotalRowHours == 0 ? '-' : $AllTotalRowHours);
$hours['totalColumn'] = [];
if(isset($hours['column'])){
foreach ($hours['column'] as $key => $value) {
$hours['totalColumn'] += [$key => array_sum($value)];
}
} else {
$hours['totalColumn'] = ['-'];
}
$columnLoop = 1;
foreach ($hours['totalColumn'] as $dataColumn) {
if(!isset($column[$columnLoop])) continue;
$ManHoursSheet->setCellValue($column[$columnLoop] . $row, $dataColumn);
$columnLoop++;
}
$hours['totalAllHours'] = $AllTotalRowHours;
$lastColumn = $ManHoursSheet->getHighestColumn();
$lastRow = $ManHoursSheet->getHighestRow();
$ManHoursSheet->getColumnDimension('A')->setWidth(25);
$lengthColumn = array_search($lastColumn, $column);
for ($i = 1; $i <= $lengthColumn; $i++) {
$ManHoursSheet->getColumnDimension($column[$i])->setWidth(15);
}
$ManHoursSheet->getStyle("A1:" . $lastColumn . "1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$ManHoursSheet->getStyle("A1:" . $lastColumn . "1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$ManHoursSheet->getStyle("A1:" . $lastColumn . "1")->getFont()->setBold(true);
$ManHoursSheet->getStyle("A1:A" . $lastRow)->getFont()->setBold(true);
$ManHoursSheet->getStyle("A" . $lastRow . ":" . $lastColumn . $lastRow)->getFont()->setBold(true);
$ManHoursSheet->getStyle("A" . $lastRow . ":" . $lastColumn . $lastRow)->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$ManHoursSheet->getStyle("A" . $lastRow . ":" . $lastColumn . $lastRow)->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$ManHoursSheet->getStyle($lastColumn . "1:" . $lastColumn . $lastRow)->getFont()->setBold(true);
$VendorSheet = new \PhpOffice\PhpSpreadsheet\Worksheet\Worksheet($spreadsheet, 'Vendors');
$spreadsheet->addSheet($VendorSheet, 2);
$VendorSheet = $spreadsheet->getSheet(2);
$VendorSheet->setCellValue('A1', 'Vendors');
$VendorSheet->setCellValue('B1', 'Planned (total)');
$VendorSheet->setCellValue('C1', 'Invoiced (total)');
$VendorSheet->setCellValue('D1', 'Difference Planned & Invoiced');
$VendorSheet->getStyle("A1:D1")->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$VendorSheet->getStyle("A1:D1")->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$VendorSheet->getDefaultColumnDimension()->setWidth(27);
$VendorSheet->getStyle("A1:D1")->getFont()->setBold(true);
$vendorsQuotationPlannings = $project[0]->getVendorQuotationPlannings();
$vqpData = [];
$x = 2;
foreach ($vendorsQuotationPlannings as $vqp) {
$VendorSheet->setCellValue('A' . $x, $vqp->getVendor()->getName());
$VendorSheet->setCellValue('B' . $x, $vqp->getAmountPlannedUsd());
$VendorSheet->setCellValue('C' . $x, $vqp->getTotalInvoiceReceivedUSD());
$VendorSheet->setCellValue('D' . $x, $vqp->getBudgetRemainingUsd());
$data = [
'vendorName' => $vqp->getVendor()->getName(),
'totalPlanned' => $vqp->getAmountPlannedUsd(),
'totalInvoiced' => $vqp->getTotalInvoiceReceivedUSD(),
'diffPlannedInvoiced' => $vqp->getBudgetRemainingUsd()
];
array_push($vqpData, $data);
$x++;
}
$totalPlanned = array_column($vqpData, 'totalPlanned');
$totalInvoiced = array_column($vqpData, 'totalInvoiced');
$diffPlannedInvoiced = array_column($vqpData, 'diffPlannedInvoiced');
$vqpData['total']['vendorName'] = 'Total';
$vqpData['total']['totalPlanned'] = array_sum($totalPlanned);
$vqpData['total']['totalInvoiced'] = array_sum($totalInvoiced);
$vqpData['total']['diffPlannedInvoiced'] = array_sum($diffPlannedInvoiced);
$vendorLastColumn = $VendorSheet->getHighestColumn();
$vendorLastRow = $VendorSheet->getHighestRow() + 1;
$VendorSheet->setCellValue('A' . $vendorLastRow, $vqpData['total']['vendorName']);
$VendorSheet->setCellValue('B' . $vendorLastRow, $vqpData['total']['totalPlanned']);
$VendorSheet->setCellValue('C' . $vendorLastRow, $vqpData['total']['totalInvoiced']);
$VendorSheet->setCellValue('D' . $vendorLastRow, $vqpData['total']['diffPlannedInvoiced']);
$VendorSheet->getStyle("A" . $VendorSheet->getHighestRow() . ":D" . $VendorSheet->getHighestRow())->getFont()->getColor()->setARGB(\PhpOffice\PhpSpreadsheet\Style\Color::COLOR_WHITE);
$VendorSheet->getStyle("A" . $VendorSheet->getHighestRow() . ":D" . $VendorSheet->getHighestRow())->getFill()->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)->getStartColor()->setARGB('00000000');
$VendorSheet->getStyle("A" . $VendorSheet->getHighestRow() . ":D" . $VendorSheet->getHighestRow())->getFont()->setBold(true);
// $VendorSheet->getStyle('B2:D' . $VendorSheet->getHighestRow())->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
$VendorSheet->getStyle('B2:D' . $VendorSheet->getHighestRow())->getNumberFormat()->setFormatCode('#,##0.00;(#,##0.00)');
// $VendorSheet->setAutoFilter('A1:D' . $VendorSheet->getHighestRow());
}
$writer = new Xlsx($spreadsheet);
$writer->save($filename);
if($_target == 'google'){
$gsheetURL = $googleDriveService->uploadToGoogleDrive($filename);
if($gsheetURL){
unlink($filename);
return new RedirectResponse($gsheetURL, 302);
}
}else{
$response = new Response();
$response->headers->set('Content-type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$response->headers->set('Content-Disposition', sprintf('attachment; filename="%s"', $filename));
$response->setContent(file_get_contents($filename));
$response->setStatusCode(\Symfony\Component\HttpFoundation\Response::HTTP_OK);
$response->headers->set('Content-Transfer-Encoding', 'binary');
$response->headers->set('Pragma', 'no-cache');
$response->headers->set('Expires', '0');
unlink($filename);
return $response;
exit;
}
}
#[Route(path: '/api/projects', name: 'api_projects', methods: ['GET'])]
public function apiProjects(SerializerInterface $serializer, ProjectRepository $projectRepository, Request $request): Response
{
$header = $request->headers->get('Authorization') ?: null;
if ($header !== $this->getParameter('checkboxAuthToken')) {
$responseData = [
'status' => '401 Unauthorized',
];
$response = new Response(
$serializer->serialize($responseData, 'json', ['groups' => 'APIProject']),
Response::HTTP_UNAUTHORIZED,
['Content-type' => 'application/json']
);
return $response;
}
$projects = $projectRepository->findAll();
$data = [];
if (!empty($projects)) {
foreach ($projects as $project) {
$members = [];
foreach ($project->getProjectMembers() as $member) {
$members[] .= $member->getId();
}
$data[] = [
'id' => $project->getId(),
'name' => $project->getName(),
'description' => $project->getDescription(),
'client_id' => $project->getClient()->getId(),
'pic_id' => $project->getPersonInCharge() ? $project->getPersonInCharge()->getId() : "",
'status' => $project->getStatus(),
'project_members' => $members,
'active' => $project->getDeletedAt() ? false : true
];
}
}
$responseData = [
'status' => !empty($data) ? 200 : 404,
'data' => $data
];
$response = new Response(
$serializer->serialize($responseData, 'json', ['groups' => 'APIProject']),
Response::HTTP_OK,
['Content-type' => 'application/json']
);
return $response;
}
#[Route(path: '/api/projects/{id}', name: 'api_project_show', methods: ['GET'])]
public function apiProjectShow($id, SerializerInterface $serializer, ProjectRepository $projectRepository, Request $request): Response
{
$header = $request->headers->get('Authorization') ?: null;
if ($header !== $this->getParameter('checkboxAuthToken')) {
$responseData = [
'status' => '401 Unauthorized',
];
$response = new Response(
$serializer->serialize($responseData, 'json', ['groups' => 'APIProject']),
Response::HTTP_UNAUTHORIZED,
['Content-type' => 'application/json']
);
return $response;
}
$projects = $projectRepository->find($id);
$data = [];
if ($projects) {
$members = [];
foreach ($projects->getProjectMembers() as $member) {
$members[] .= $member->getId();
}
$data[] = [
'id' => $projects->getId(),
'name' => $projects->getName(),
'description' => $projects->getDescription(),
'client_id' => $projects->getClient()->getId(),
'pic_id' => $projects->getPersonInCharge()->getId(),
'status' => $projects->getStatus(),
'project_members' => $members,
'active' => $projects->getDeletedAt() ? false : true
];
}
$responseData = [
'status' => !empty($data) ? 200 : 404,
'data' => $data
];
$response = new Response(
$serializer->serialize($responseData, 'json', ['groups' => 'APIProject']),
Response::HTTP_OK,
['Content-type' => 'application/json']
);
return $response;
}
#[Route(path: '/api/projects/{id}', name: 'api_project_delete', methods: ['DELETE'])]
public function apiProjectDelete($id, SerializerInterface $serializer, ProjectRepository $projectRepository, Request $request): Response
{
$header = $request->headers->get('Authorization') ?: null;
if ($header !== $this->getParameter('checkboxAuthToken')) {
$responseData = [
'status' => '401 Unauthorized',
];
$response = new Response(
$serializer->serialize($responseData, 'json', ['groups' => 'APIProject']),
Response::HTTP_UNAUTHORIZED,
['Content-type' => 'application/json']
);
return $response;
}
$em = $this->getDoctrine()->getManager();
$project = $projectRepository->find($id);
if (!$project) {
$responseData = [
'status' => '404 No Project found for id ' . $id,
];
$response = new Response(
$serializer->serialize($responseData, 'json', ['groups' => 'APIProject']),
Response::HTTP_NOT_FOUND,
['Content-type' => 'application/json']
);
return $response;
}
$em->remove($project);
$em->flush();
$responseData = [
'status' => 200,
'data' => 'Deleted a project successfully with id ' . $id
];
$response = new Response(
$serializer->serialize($responseData, 'json', ['groups' => 'APIProject']),
Response::HTTP_OK,
['Content-type' => 'application/json']
);
return $response;
}
/**
* @param ProjectRepository $projectRepository
* @param Request $request
* @return Response
*/
#[Route(path: '/ajax/project/prospect-summary', name: 'ajax_prospect_summary')]
public function ajaxProjectProspectSummary(ProjectRepository $projectRepository, Request $request): Response
{
// Example params:
// http://localhost:8000/ajax/project/prospect-summary?orderBy=projectCount&order=DESC&pic=&client=&startDate=2022-10-01&endDate=2023-12-31
// $user = $this->getUser();
$page = intval($request->query->get('page') - 1);
if ($page < 0) $page = 0;
$limit = intval($request->query->get('limit'));
if ($limit === 0) $limit = 20;
$keyword = $request->get('keyword');
$order = $request->get('order');
$orderBy = $request->get('orderBy');
$startDate = $request->get('start');
$endDate = $request->get('end');
$client = $request->get('client');
$pic = $request->get('pic');
$period = $request->get('period');
$year = $request->get('year');
$retainer = $request->get('retainer');
$waitForPage = $request->query->get('waitForPage');
$reportList = $projectRepository->findProspectSummaryByPage($keyword, $order, $orderBy, $startDate, $endDate, $client, $pic, $period, $year, $retainer);
$leadStatus = [
1 => 'Lead',
2 => 'First discussion',
3 => 'Brief received',
4 => 'Work on proposal or quote',
5 => 'Proposal sent -> to follow up',
6 => 'Verbally Accepted',
7 => 'Work on Contract',
8 => 'Won (Contract or Quote signed)',
9 => 'Lost',
91 => 'Lost / No answer',
92 => 'No go / Closed'
];
$total = [];
$total['plannedRevenue'] = 0;
$total['estimatedMargin'] = 0;
$total['estimatedVendorCost'] = 0;
$total['projectCount'] = 0;
$total['clients'] = 0;
foreach ($reportList as $report) {
$total['plannedRevenue'] += $report['plannedRevenue'];
$total['estimatedMargin'] += $report['estimatedMargin'];
$total['estimatedVendorCost'] += $report['estimatedVendorCost'];
$total['projectCount'] += $report['projectCount'];
$total['clients'] += $report['clients'];
}
$body = $this->renderView('private/project-management/report/components/list-report.html.twig', [
'reportList' => $reportList,
'leadStatus' => $leadStatus,
'totalReport' => $total
]);
$result['waitForPage'] = false;
$result['content'] = [
'html' => $body,
'total' => 1
];
$result['status'] = 'OK';
return new JsonResponse($result);
}
/**
* @param ProjectRepository $projectRepository
* @param Request $request
* @return Response
*/
#[Route(path: '/ajax/project/confirmed-summary', name: 'ajax_confirmed_summary')]
public function ajaxProjectconfirmedSummary(ProjectRepository $projectRepository, Request $request): Response
{
// Example params:
// http://localhost:8000/ajax/project/prospect-summary?orderBy=projectCount&order=DESC&pic=&client=&startDate=2022-10-01&endDate=2023-12-31
// $user = $this->getUser();
$page = intval($request->query->get('page') - 1);
if ($page < 0) $page = 0;
$limit = intval($request->query->get('limit'));
if ($limit === 0) $limit = 20;
$keyword = $request->get('keyword');
$order = $request->get('order');
$orderBy = $request->get('orderBy');
$startDate = $request->get('start');
$endDate = $request->get('end');
$client = $request->get('client');
$pic = $request->get('pic');
$period = $request->get('period');
$year = $request->get('year');
$retainer = $request->get('retainer');
$waitForPage = $request->query->get('waitForPage');
$reportList = $projectRepository->findConfirmedSummaryByPage($keyword, $order, $orderBy, $startDate, $endDate, $client, $pic, $period, $year, $retainer);
$total = [];
$total['plannedRevenue'] = 0;
$total['estimatedMargin'] = 0;
$total['estimatedVendorCost'] = 0;
$total['projectCount'] = 0;
$total['clients'] = 0;
foreach ($reportList as $report) {
$total['plannedRevenue'] += $report['plannedRevenue'];
$total['estimatedMargin'] += $report['estimatedMargin'];
$total['estimatedVendorCost'] += $report['estimatedVendorCost'];
$total['projectCount'] += $report['projectCount'];
$total['clients'] += $report['clients'];
}
$body = $this->renderView('private/project-management/report/components/list-report-confirmed.html.twig', [
'reportList' => $reportList,
// 'leadStatus' => $leadStatus,
'totalReport' => $total
]);
$result['waitForPage'] = false;
$result['content'] = [
'html' => $body,
'total' => 1
];
$result['status'] = 'OK';
return new JsonResponse($result);
}
/**
* @param Request $request
* @param ProjectRepository $projectRepository
* @param TranslatorInterface $translator
* @param LogService $log
* @return Response
*/
#[Route(path: '/ajax/project/duplicate', name: 'ajax_duplicate_project')]
public function ajaxDuplicateProject(Request $request, ProjectRepository $projectRepository, TranslatorInterface $translator, LogService $log, WebhookService $webhook): Response
{
$projectId = intval($request->get('id'));
$project = $projectRepository->find(intval($projectId));
if (!$project) {
$result['status'] = 'error';
$result['error'] = 'Project not found';
return new JsonResponse($result);
}
$user = $this->getUser();
/*
$newProject = new Project();
$dateForStr = new \DateTime();
$newProject->setClient($project->getClient());
$newProject->setName($project->getName() . "_" . $dateForStr->format('Y-m-d'));
$newProject->setDescription("");
$newProject->setCurrency($project->getCurrency());
$newProject->setCreatedBy($user);
$newProject->setCreatedAt(new \DateTime());
$newProject->setUpdatedAt(new \DateTime());
// $newProject->setStartDate($project->getStartDate());
// $newProject->setEndDate($project->getEndDate());
$newProject->setPersonInCharge($project->getPersonInCharge());
$newProject->setStatus($project->getStatus());
$newProject->setRetainer(0);
$newProject->setLead($project->getLead());
$newProject->setType($project->getType());
*/
$newProject = clone $project;
// Update the name with a timestamp
$dateForStr = new \DateTime();
$newProject->setName($project->getName() . "_" . $dateForStr->format('Y-m-d'));
// Set other properties as needed
$newProject->setDescription("");
$newProject->setCreatedBy($user);
$newProject->setCreatedAt(new \DateTime());
$newProject->setUpdatedAt(new \DateTime());
$newProject->setGeneratedId(null);
$newProject->setStartDate(null);
$newProject->setEndDate(null);
$newProject->setLeadDate(new \DateTime());
$newProject->setProposalDate(new \DateTime());
$newProject->setWinDate(new \DateTime());
$newProject->setProposalFollowUpDate(null);
$newProject->setPresentationDate(null);
$newProject->setNoEstimatedVendorCost(false);
$newProject->setEstimatedVendorCost(0);
$newProject->setEstimatedProfit(null);
$newProject->setPlannedRevenue(null);
$newProject->setPlannedRevenueUsd(null);
$newProject->setRetainer(0);
$newProject->setPlanFilename(null);
$newProject->setPlanFilepath(null);
$newProject->setPlanFiletype(null);
$newProject->setPlanURL(null);
$entityManager = $this->getDoctrine()->getManager();
$entityManager->persist($newProject);
$entityManager->flush();
if ($newProject->getGeneratedId() == null) {
$newProject->setGeneratedId($newProject->getCreatedAt()->format('ym') . '-' . sprintf('%04d', $newProject->getId()));
$entityManager->flush();
$entityManager->refresh($newProject);
}
$log->save($user, [
'owner' => $user,
'message' => 'log.project.duplicate',
'new_data' => $newProject,
'old_data' => "",
]);
// $result['status'] = 'OK';
// $result['id'] = $newProject->getId();
return $this->redirectToRoute('view_project', ['_id' => $newProject->getId()]);
// return new JsonResponse($result);
}
/**
* @param UserRepository $userRepository
* @param ProjectRepository $projectRepository
* @param TranslatorInterface $translator
* @return Response
* @throws \Exception
*/
#[Route(path: '/project-management/project/pic/view/{_id}', defaults: ['_id' => 0], name: 'view_pic_project')]
public function viewPicProject($_id, UserRepository $userRepository, ProjectRepository $projectRepository, TranslatorInterface $translator, IndustryClassificationRepository $industryClassificationRepository, ProjectClassificationRepository $projectClassificationRepository): Response
{
$user = $this->getUser();
$pic = $userRepository->findByActiveEmail($_id) ?: null;
// if (!$this->isGranted('ROLE_FINANCE') && !$this->isGranted('ROLE_TEAM_LEADER')) {
// throw new \Exception($translator->trans('messages.security.forbidden'));
// };
$totalProject = $projectRepository->getTotalProjectByPic($pic);
$industryClassification = $industryClassificationRepository->findAll();
$projectClassification = $projectClassificationRepository->findAll();
if (!$pic) {
throw new \Exception($translator->trans('messages.security.forbidden'));
} else {
return $this->render('private/project-management/project/components/view-pic.html.twig', [
'pic' => $pic,
'totalProject' => $totalProject,
'industryClassification' => $industryClassification,
'projectClassification' => $projectClassification
]);
}
}
/**
* @param UserRepository $userRepository
* @param ProjectRepository $projectRepository
* @param TranslatorInterface $translator
* @return Response
* @throws \Exception
*/
#[Route(path: '/project-management/project/employee/view/{_id}', defaults: ['_id' => 0], name: 'view_user_project')]
public function viewUserProject($_id, UserRepository $userRepository, ProjectRepository $projectRepository, TranslatorInterface $translator, IndustryClassificationRepository $industryClassificationRepository, ProjectClassificationRepository $projectClassificationRepository): Response
{
$user = $this->getUser();
// $targetedUser = $userRepository->findByActiveEmail($_id) ?: null;
$targetedUser = $userRepository->findByEmail($_id);
// if (!$this->isGranted('ROLE_FINANCE') && !$this->isGranted('ROLE_TEAM_LEADER')) {
// throw new \Exception($translator->trans('messages.security.forbidden'));
// };
$totalProject = $projectRepository->getTotalProjectByEmployee($targetedUser);
$industryClassification = $industryClassificationRepository->findAll();
$projectClassification = $projectClassificationRepository->findAll();
// $totalProject = 0;
if (!$targetedUser) {
throw new \Exception($translator->trans('messages.security.forbidden'));
} else {
return $this->render('private/project-management/project/components/view-user-project-list.html.twig', [
'user' => $targetedUser,
'totalProject' => $totalProject,
'industryClassification' => $industryClassification,
'projectClassification' => $projectClassification
]);
}
}
/**
* @param UserRepository $userRepository
* @param ProjectRepository $projectRepository
* @param TranslatorInterface $translator
* @return Response
* @throws \Exception
*/
#[Route(path: '/project-management/project/client-pic/view/{_id}', defaults: ['_id' => 0], name: 'view_client_pic_project')]
public function viewClientPicProject($_id, ClientContactRepository $clientContactRepository, ProjectRepository $projectRepository, TranslatorInterface $translator, IndustryClassificationRepository $industryClassificationRepository, ProjectClassificationRepository $projectClassificationRepository): Response
{
$user = $this->getUser();
$clientPic = $clientContactRepository->find($_id) ?: null;
// if (!$this->isGranted('ROLE_FINANCE') && !$this->isGranted('ROLE_TEAM_LEADER')) {
// throw new \Exception($translator->trans('messages.security.forbidden'));
// };
$totalProject = $projectRepository->getTotalProjectByClientPic($clientPic);
$industryClassification = $industryClassificationRepository->findAll();
$projectClassification = $projectClassificationRepository->findAll();
if (!$clientPic) {
throw new \Exception($translator->trans('messages.security.forbidden'));
} else {
return $this->render('private/project-management/project/components/view-pic-client.html.twig', [
'pic' => $clientPic,
'totalProject' => $totalProject,
'industryClassification' => $industryClassification,
'projectClassification' => $projectClassification
]);
}
}
/**
* @param UserRepository $userRepository
* @param ProjectRepository $projectRepository
* @param TranslatorInterface $translator
* @return Response
* @throws \Exception
*/
#[Route(path: '/project-management/project/vendor-pic/view/{_id}', defaults: ['_id' => 0], name: 'view_vendor_pic_project')]
public function viewVendorPicProject($_id, VendorContactRepository $vendorContactRepository, ProjectRepository $projectRepository, TranslatorInterface $translator, IndustryClassificationRepository $industryClassificationRepository, ProjectClassificationRepository $projectClassificationRepository): Response
{
$user = $this->getUser();
$vendorPic = $vendorContactRepository->find($_id) ?: null;
// if (!$this->isGranted('ROLE_FINANCE') && !$this->isGranted('ROLE_TEAM_LEADER')) {
// throw new \Exception($translator->trans('messages.security.forbidden'));
// };
$totalProject = $projectRepository->getTotalProjectByVendorPic($vendorPic);
$industryClassification = $industryClassificationRepository->findAll();
$projectClassification = $projectClassificationRepository->findAll();
if (!$vendorPic) {
throw new \Exception($translator->trans('messages.security.forbidden'));
} else {
return $this->render('private/project-management/project/components/view-pic-vendor.html.twig', [
'pic' => $vendorPic,
'totalProject' => $totalProject,
'industryClassification' => $industryClassification,
'projectClassification' => $projectClassification
]);
}
}
#[Route(path: '/project/get-project-file', name: 'get-project-file', methods: ['GET'])]
public function getProjectFile(Request $request, UtilsService $utilsService, ParameterBagInterface $params)
{
$path = $request->get('path');
$name = $request->get('name');
if (!$path || strlen($path) == 0) {
exit();
}
$preSignedAwsFileProd = $utilsService->createPresignedUrl2($path, true);
$preSignedAwsFileDev = $utilsService->createPresignedUrl2($path);
if ($utilsService->UR_exists($preSignedAwsFileProd)) {
header('Content-Type: application/octet-stream');
header("Content-Transfer-Encoding: Binary");
header("Content-disposition: attachment; filename=\"" . $name . "\"");
readfile($preSignedAwsFileProd);
exit();
} elseif ($utilsService->UR_exists($preSignedAwsFileDev)) {
header('Content-Type: application/octet-stream');
header("Content-Transfer-Encoding: Binary");
header("Content-disposition: attachment; filename=\"" . $name . "\"");
readfile($preSignedAwsFileDev);
exit();
} else {
exit();
}
}
/**
* @param Request $request
* @param ProjectRepository $projectRepository
*/
#[Route(path: '/ajax/project/get-cost-and-profit', name: 'ajax_get-project-cost-and-profit', methods: ['POST'])]
public function getProjectCostAndProfit(Request $request, ProjectRepository $projectRepository)
{
$idList = $request->get('projects');
$projects = $projectRepository->findBy(array('id' => $idList));
// dd($projects);
$result['status'] = 'OK';
$result['projects'] = [];
foreach ($projects as $project) {
$data['profit1'] = 0;
if ($project->getType() == 'CONFIRMED') {
$data['profit1'] = $project->getProjectProfit1();
} else {
$data['profit1'] = $project->getEstimatedProfitUsd();
}
//$data['profit1USD'] = $project->getType() == 'CONFIRMED' ? $project->getProjectProfit1() : $project->getEstimatedProfit();
$data['cost'] = $project->getType() == 'CONFIRMED' ? $project->getProjectCost() : $project->getProjectEstimatedCostUsd();
$data['id'] = $project->getId();
array_push($result['projects'], $data);
}
return new JsonResponse($result);
}
/**
* @param ProjectRepository $projectRepositor
* @param TranslatorInterface $translator
* @param LogService $log
* @return Response
* @throws \Exception
*/
#[Route(path: '/project-management/project/log/description/{_id}/{_type}', defaults: ['_id' => 0, '_type' => null], name: 'view_project_log')]
public function viewProjectLog($_id, $_type, ProjectRepository $projectRepository, LogService $log, TranslatorInterface $translator): Response
{
$project = $projectRepository->find($_id) ?: null;
if (!$project) {
$result['status'] = 'ERROR';
} else {
$logData = $log->data('log.project.edit', $project->getId(), $_type);
$projectLeadStatus = $this->getDoctrine()->getRepository(ProjectLeadStatus::class)->findAll();
$projectLeadStatusArr = [];
foreach ($projectLeadStatus as $pls) {
$projectLeadStatusArr[$pls->getLead()] = $pls->getName();
}
$result['status'] = 'OK';
$result['content'] = $this->renderView('private/project-management/project/components/view-log.html.twig', [
'project' => $project,
'logType' => $_type,
'logData' => $logData,
'projectLeadStatus' => $projectLeadStatusArr
]);
}
return new JsonResponse($result);
}
#[Route(path: '/project-management/project/email/client-feedback/send/{_id}', defaults: ['_id' => 0, '_type' => null], name: 'send_email_client_feedback')]
public function sendEmailClientFeedbackk($_id, ProjectService $projectService, ProjectRepository $projectRepository, RequestStack $requestStack)
{
$baseUrl = $requestStack->getCurrentRequest()->getSchemeAndHttpHost();
$project = $projectRepository->find($_id);
if(!empty($project)){
$projectService->projectClientFeedbackEmail($project, $baseUrl);
}
$result['status'] = 'OK';
return new JsonResponse($result);
}
/**
* @param ProjectRepository $projectRepositor
* @param TranslatorInterface $translator
* @param LogService $log
* @return Response
* @throws \Exception
*/
#[Route(path: '/project-management/project/client-feedback/email/log/{_toAddress}/{_title}', defaults: ['_toAddress' => 0, '_title' => null], name: 'view_client_feedback_email_log', requirements: ['_title' => '.+'])]
public function viewClientFeedbackEmailLog($_toAddress, $_title, ProjectRepository $projectRepository, ProjectService $projectService, LogService $log, TranslatorInterface $translator): Response
{
$emailLogs = $projectService->getEmailLogFeedback($_toAddress, $_title);
$result['status'] = 'OK';
$result['content'] = $this->renderView('private/project-management/project/components/view-email-log.html.twig', [
'emailLogs' => $emailLogs,
]);
return new JsonResponse($result);
}
}

src/Controller/ProjectManagement/ProjectController.php line 101