PHP SPARESHEET CODE

PHP SPARESHEET CODE

<?php

namespace App\Http\Controllers;

require 'vendor/autoload.php';

use DB;

use File;

use Session;

use Helper;

use DateTime;

use DateInterval;

use DatePeriod;

use App\AdminAuth;

use App\SalaryLog;

use App\TimeLog;

use App\Project;

use Illuminate\Support\Str;

use Illuminate\Http\Request;

use Illuminate\Http\Response;

use Illuminate\Support\Facades\URL;

use Illuminate\Support\Facades\Storage;

use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PhpOffice\PhpSpreadsheet\IOFactory;

use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

use PhpOffice\PhpSpreadsheet\Style\NumberFormat;

\PhpOffice\PhpSpreadsheet\Cell\Cell::setValueBinder( new \PhpOffice\PhpSpreadsheet\Cell\AdvancedValueBinder() );

class ExcelController extends Controller

{

    public function index()

    {

        return view('Report.report');

    }

    public function create_report()

    {

        $row_cnt=2;

        $projects=Project::with('projectType')->orderBy('start_date', 'ASC')->get();

        $old_proj_date=Project::select('start_date')->orderBy('start_date', 'ASC')->first();

        $timeLog=TimeLog::get();

        $spreadsheet = new Spreadsheet();

        $sheet = $spreadsheet->getActiveSheet();

        $sheet->setCellValue('A1', 'Project Name');

        $sheet->setCellValue('B1', 'Project Group');

        $sheet->setCellValue('C1', 'Project Type');

        $sheet->setCellValue('D1', 'Status');

        $sheet->setCellValue('E1', 'Month');

        $sheet->setCellValue('F1', 'Estimated/billed Hrs');

        $sheet->setCellValue('G1', 'CR Hrs');

        $sheet->setCellValue('H1', 'Total (Zoho hrs)');

        $sheet->setCellValue('I1', 'hrs diff');

        $sheet->setCellValue('J1', '% hrs increased');

        $spreadsheet->getActiveSheet()->getStyle("A1:J1")->getFont()->setSize(12)->setBold(true);

        foreach($projects as $project){

            if(strtoupper($project->projectType->type)==strtoupper('Fixed')){

                $total_min=TimeLog::where('proj_id',$project->zoho_id)->sum('total_minutes');

                $zoho_hours = intdiv($total_min, 60).':'.($total_min % 60);

                $sheet->setCellValue('A'.$row_cnt, $project->name);

                $sheet->setCellValue('B'.$row_cnt, $project->group_name);

                $sheet->setCellValue('C'.$row_cnt, $project->type);

                $sheet->setCellValue('D'.$row_cnt, $project->status);

                $sheet->setCellValue('E'.$row_cnt, '');

                $sheet->setCellValue('F'.$row_cnt, (float)$project->billed_hrs);

                $sheet->setCellValue('G'.$row_cnt, (float)$project->cr_hrs);

                $sheet->setCellValue('H'.$row_cnt, (float)$zoho_hours);

                $sheet->setCellValue('I'.$row_cnt,'=H'.$row_cnt.'-(F'.$row_cnt.'+G'.$row_cnt.')');

                $sheet->setCellValue('J'.$row_cnt,'=(I'.$row_cnt.'/F'.$row_cnt.')*100');

                $row_cnt++;          

            }else{

                $cnt=0;

                $today=date('Y-m-d');

                $start    = (new DateTime($project->start_date))->modify('first day of this month');

                $end      = (new DateTime($today))->modify('first day of +2 month');

                $interval = DateInterval::createFromDateString('1 month');

                $period   = new DatePeriod($start, $interval, $end);

                foreach ($period as $dt) {

                    if($cnt!=0){

                        $mth_name =date('F', strtotime('-1 month', strtotime($dt->format("Y-m-d")))); 

                        $start_date = date('m-d-Y', strtotime('-1 month', strtotime($dt->format("Y-m-d")))); 

                        $end_date = date('m-d-Y', strtotime('-1 day', strtotime($dt->format("Y-m-d")))); 

                        $total_min=TimeLog::where('proj_id',$project->zoho_id)->whereBetween('date', [$start_date, $end_date])->sum('total_minutes');                     

                        $zoho_hours = intdiv($total_min, 60).':'.($total_min % 60);

                        $sheet->setCellValue('A'.$row_cnt, $project->name);

                        $sheet->setCellValue('B'.$row_cnt, $project->group_name);

                        $sheet->setCellValue('C'.$row_cnt, $project->type);

                        $sheet->setCellValue('D'.$row_cnt, $project->status);

                        $sheet->setCellValue('E'.$row_cnt, $mth_name);

                        $sheet->setCellValue('F'.$row_cnt, (float)$project->billed_hrs);

                        $sheet->setCellValue('G'.$row_cnt, (float)$project->cr_hrs);

                        $sheet->setCellValue('H'.$row_cnt, (float)$zoho_hours);

                        $sheet->setCellValue('I'.$row_cnt,'=H'.$row_cnt.'-(F'.$row_cnt.'+G'.$row_cnt.')');

                        $sheet->setCellValue('J'.$row_cnt,'=(I'.$row_cnt.'/F'.$row_cnt.')*100');

                          $row_cnt++;

                    } 

                    $cnt++;                   

                }

            }     

        }

        for($i = 'A'; $i <=  $spreadsheet->getActiveSheet()->getHighestDataColumn(); $i++) {

                $spreadsheet->getActiveSheet()->getColumnDimension($i)->setAutoSize(TRUE);

            }

           $spreadsheet->getActiveSheet()->freezePaneByColumnAndRow(2,2);

            $filename=date('d-m-Y')."-Potal-Report.xlsx";

            // $writer = new Xlsx($spreadsheet);

            // $writer->save('storage/app/public/'. $filename);

            $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, "Xlsx");

            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

            header('Content-Disposition: attachment; filename="'.$filename.'"');

            ob_end_clean();

            $writer->save("php://output");

        }

    public function load_data($value='')

    {

        // code...

    }

   

}