Gecko [ gandhischool.edu.np ]

Name	Size	Permission
Spatie	[ DIR ]	drwxrwxr-x
AccessController.php	2.48 KB	-rw-rw-r--
AddressController.php	3.29 KB	-rw-rw-r--
AdminUserController.php	8.74 KB	-rw-rw-r--
AppUserController.php	8.02 KB	-rw-rw-r--
CategoryController.php	3.02 KB	-rw-rw-r--
DashboardController.php	8.02 KB	-rw-rw-r--
FiscalController.php	1.28 KB	-rw-rw-r--
GeneralController.php	682 B	-rw-rw-r--
LoginController.php	2.28 KB	-rw-rw-r--
OfficeController.php	1.35 KB	-rw-rw-r--
PriorityController.php	2.15 KB	-rw-rw-r--
ReportController.php	21.01 KB	-rw-rw-r--
SifarisFormController.php	1.43 KB	-rw-rw-r--
SifarisRecordController.php	26.6 KB	-rw-rw-r--
StatusController.php	2.25 KB	-rw-rw-r--

Code Editor : ReportController.php

<?php

namespace App\Http\Controllers\Admin;

use App\Exports\SifarisExport;
use App\Http\Controllers\Controller;
use App\Models\Category;
use App\Models\FiscalYear;
use App\Models\Office;
use App\Models\SifarisForm;
use App\Services\SifarisRecordService;
use Illuminate\Http\Request;
use DB;
use Helper;
use Illuminate\Database\Eloquent\Collection;
use Maatwebsite\Excel\Facades\Excel;

class ReportController extends Controller
{
    private $service;
    private $helper;
    public function __construct(Helper $helper, SifarisRecordService $service)
    {
        $this->service = $service;
        $this->helper = $helper;
    }

//Sifaris Summary Report
    public function sifaris_summary_report(Request $request)
    {
        //get filter params
        $office = office();
        $offices = $this->get_offices($office);

$params = $this->get_filter_params($request);

$date_bs = $params['today'];
        if (!$date_bs) $date_bs = $params['single_day'];

$data = $this->get_sifaris_summary_report($offices, $date_bs, $params['date_from'], $params['date_to'], $params['fiscal_id']);

$bag = [
            'data' => $data,
            'offices' => $offices,
            'params' => $params
        ];

$export = $request->query('export');
        if ($export && $export == 'excel') {
            return Excel::download(new SifarisExport('admin.reports.excel.sifaris_summary_report_partial', $bag), 'sifaris_summary_report.xlsx');
        }

return view('admin.reports.sifaris_summary_report', compact('bag'));
    }

private function get_sifaris_summary_report(Collection $offices, $date_bs = null, $from_date = null, $to_date = null, $fiscal_id = null)
    {
        // dd($offices);
        $params = [];
        if ($date_bs) {
            $params['date_ad'] = adDate($date_bs);
        }
        if ($from_date) {
            $params['from_date'] = adDate($from_date);
        }
        if ($to_date) {
            $params['to_date'] = adDate($to_date);
        }
        if ($fiscal_id) {
            $params['fiscal_id'] = $fiscal_id;
        }

$query = 'SELECT COUNT(*) AS total_count, count(if(sifaris_receipts.fee=0,1,NULL) ) AS free_count, count(if(sifaris_receipts.fee>0,1,NULL) ) AS paid_count, offices.id AS office_id,offices.name
        from sifaris_receipts
        INNER JOIN sifaris_records on sifaris_receipts.id = sifaris_records.receipt_id
        INNER JOIN offices ON sifaris_records.office_id=offices.id
        INNER JOIN sifaris_forms ON sifaris_forms.id=sifaris_records.form_id
        INNER JOIN categories ON  categories.id=sifaris_records.status_id
        INNER JOIN category_details ON  category_details.category_id=categories.id where 1=1 ';

$query .= ' GROUP BY offices.id,offices.name;';

// DB::enableQueryLog();
        $records = DB::select($query, $params);
        // dd(DB::getQueryLog());

$subjects = [
            [
                'name' => 'जम्मा सिफारिस',
                'name_en' => 'Total Sifaris',
                'code' => 'all',
                'office_data' => []
            ],
            [
                'name' => 'नि:शुल्क सिफारिस',
                'name_en' => 'Free Sifaris',
                'code' => 'free',
                'office_data' => []
            ],
            [
                'name' => 'सशुल्क सिफारिस',
                'name_en' => 'Paid Sifaris',
                'code' => 'paid',
                'office_data' => []
            ]
        ];

foreach ($offices as $o) {
            foreach ($subjects as $key => $s) {
                $subjects[$key]['office_data'][] = [
                    'office_id' => $o->id,
                    'office_name' => $o->name,
                    'total_count' => 0
                ];
            }
        }

foreach ($subjects as $key => $s) {
            foreach ($records as $item) {
                foreach ($s['office_data'] as $key_f => $o) {
                    if ($item->office_id == $o['office_id']) {
                        if ($s['code'] == 'total')
                            $subjects[$key]['office_data'][$key_f]['total_count'] += $item->total_count;
                        if ($s['code'] == 'free')
                            $subjects[$key]['office_data'][$key_f]['total_count'] += $item->free_count;
                        if ($s['code'] == 'paid')
                            $subjects[$key]['office_data'][$key_f]['total_count'] += $item->paid_count;
                    }
                }
            }
        }

// dd($subjects);
        return $subjects;
    }

//Revenue Report for Sifaris Forms
    public function wardwise_sifaris_form_revenue_report(Request $request)
    {
        //get filter params
        $office = office();
        $offices = $this->get_offices($office);

$forms = $this->get_forms($office);

$params = $this->get_filter_params($request);

$date_bs = $params['today'];
        if (!$date_bs) $date_bs = $params['single_day'];

$data = $this->get_wardwise_sifaris_form_revenue($forms, $offices, $date_bs, $params['date_from'], $params['date_to'], $params['fiscal_id']);

$bag = [
            'data' => $data,
            'offices' => $offices,
            'params' => $params
        ];

$export = $request->query('export');
        if ($export && $export == 'excel') {
            return Excel::download(new SifarisExport('admin.reports.excel.ward_wise_sifaris_form_revenue_report_partial', $bag), 'revenue_by_sifaris_form_report.xlsx');
        }

return view('admin.reports.ward_wise_sifaris_form_revenue_report', compact('bag'));
    }

private function get_wardwise_sifaris_form_revenue(Collection $forms, Collection $offices, $date_bs = null, $from_date = null, $to_date = null, $fiscal_id = null)
    {
        $params = [];
        if ($date_bs) {
            $params['date_ad'] = adDate($date_bs);
        }
        if ($from_date) {
            $params['from_date'] = adDate($from_date);
        }
        if ($to_date) {
            $params['to_date'] = adDate($to_date);
        }
        if ($fiscal_id) {
            $params['fiscal_id'] = $fiscal_id;
        }

if (count($forms)) {
            $params['form_ids'] = $forms->pluck('id')->join(',');
        }

$query = "SELECT COUNT(*) AS total_count, SUM(sifaris_receipts.fee) as total_fee, sifaris_forms.id AS form_id,sifaris_forms.name as form_name, offices.id as office_id, offices.name as office_name
        FROM sifaris_receipts
        INNER JOIN sifaris_records on sifaris_receipts.id = sifaris_records.receipt_id
        INNER JOIN offices ON sifaris_records.office_id=offices.id
        INNER JOIN sifaris_forms ON sifaris_forms.id=sifaris_records.form_id
        INNER JOIN categories ON  categories.id=sifaris_records.status_id
        INNER JOIN category_details ON  category_details.category_id=categories.id where 1=1 and categories.code='completed' ";

if ($date_bs) {
            $query .= 'AND sifaris_receipts.date_ad=:date_ad ';
        }
        if ($from_date) {
            $query .= 'AND sifaris_receipts.date_ad >=:from_date ';
        }
        if ($to_date) {
            $query .= 'AND sifaris_receipts.date_ad <=:to_date ';
        }
        if ($fiscal_id) {
            $query .= 'AND sifaris_receipts.fiscal_id =:fiscal_id ';
        }
        if (count($forms)) {
            $query .= 'AND sifaris_forms.id in (:form_ids) ';
        } else {
            $query .= 'AND sifaris_forms.id = 999999 ';
        }

$query .= ' GROUP BY sifaris_forms.id, sifaris_forms.name, offices.id,offices.name';

DB::enableQueryLog();
        //$records = DB::select(DB::raw($query), $params);
        $records = DB::select($query, $params);
        // dd(DB::getQueryLog());
        // dd($records);

$data = [];

foreach ($forms as $f) {
            $form_data = [
                'id' => $f->id,
                'name' => $f->name,
                'office_data' => []
            ];
            foreach ($offices as $o) {
                $office_data = [
                    'id' => $o->id,
                    'name' => $o->name,
                    'total_count' => 0,
                    'total_revenue' => 0
                ];
                foreach ($records as $r) {
                    if ($r->form_id == $f->id && $r->office_id == $o->id) {
                        $office_data['total_count'] = $r->total_count;
                        $office_data['total_revenue'] = $r->total_fee;
                    }
                }

$form_data['office_data'][] = $office_data;
            }

$data[] = $form_data;
        }

// dd($data);
        return $data;
    }

//Revenue Report for Sifaris Status
    public function status_summary_report(Request $request)
    {
        $office = office();
        $offices = $this->get_offices($office);
        $statuses = Category::where('type_id', masterType('sifaris_form_status')->id)->with(['details'])->get();
        // dd($statuses);

$params = $this->get_filter_params($request);

$date_bs = $params['today'];
        if (!$date_bs) $date_bs = $params['single_day'];

$data = $this->get_status_summary_report($offices, $statuses, $date_bs, $params['date_from'], $params['date_to'], $params['fiscal_id']);

$bag = [
            'data' => $data,
            'offices' => $offices,
            'params' => $params
        ];

$export = $request->query('export');
        if ($export && $export == 'excel') {
            return Excel::download(new SifarisExport('admin.reports.excel.status_summary_report_partial', $bag), 'status_summary_report.xlsx');
        }

return view('admin.reports.status_summary_report', compact('bag'));
    }

private function get_status_summary_report(Collection $offices, Collection $statuses, $date_bs = null, $from_date = null, $to_date = null, $fiscal_id = null)
    {
        // dd($offices);
        $params = [];

if ($date_bs) {
            $params['date_ad'] = adDate($date_bs);
        }
        if ($from_date) {
            $params['from_date'] = adDate($from_date);
        }
        if ($to_date) {
            $params['to_date'] = adDate($to_date);
        }
        if ($fiscal_id) {
            $params['fiscal_id'] = $fiscal_id;
        }

$query = 'SELECT COUNT(*) AS total_count, categories.id as status_id,category_details.name as status_name, offices.id AS office_id, offices.name AS office_name FROM sifaris_records
        INNER JOIN sifaris_forms ON sifaris_forms.id=sifaris_records.form_id
        INNER JOIN categories ON categories.id=sifaris_records.status_id
        INNER JOIN category_details ON category_details.category_id=categories.id
        INNER JOIN offices ON sifaris_records.office_id=offices.id ';

$query .= ' WHERE 1=1 ';

if ($date_bs) {
            $query .= 'AND date_ad=:date_ad ';
        }
        if ($from_date) {
            $query .= 'AND date_ad >=:from_date ';
        }
        if ($to_date) {
            $query .= 'AND date_ad <=:to_date ';
        }
        if ($fiscal_id) {
            $query .= 'AND fiscal_id =:fiscal_id ';
        }

$query .= ' GROUP BY categories.id,category_details.name,offices.id,offices.name';

// DB::enableQueryLog();
        $records = DB::select($query, $params);
        // dd(DB::getQueryLog());
        // dd($records);

$data = [];
        // dd($statuses);

foreach ($statuses as $s) {
            $form_data = [
                'id' => $s->id,
                'name' => $s->details[0]->name,
                'office_data' => []
            ];
            foreach ($offices as $o) {
                $office_data = [
                    'id' => $o->id,
                    'name' => $o->name,
                    'total_count' => 0
                ];
                foreach ($records as $r) {
                    if ($r->status_id == $s->id && $r->office_id == $o->id) {
                        $office_data['total_count'] = $r->total_count;
                    }
                }

$form_data['office_data'][] = $office_data;
            }

$data[] = $form_data;
        }

return $data;
    }

//Status Report for Sifaris Forms
    public function wardwise_sifaris_status_report(Request $request)
    {
        $office = office();

$offices = $this->get_offices($office);

$statuses = Category::where('type_id', masterType('sifaris_form_status')->id)->with(['details'])->get();

$forms = $this->get_forms($office);

$params = $this->get_filter_params($request);

$date_bs = $params['today'];
        if (!$date_bs) $date_bs = $params['single_day'];

$data = $this->get_wardwise_sifaris_status_report($offices, $statuses, $forms, $date_bs, $params['date_from'], $params['date_to'], $params['fiscal_id']);

$bag = [
            'data' => $data,
            'offices' => $offices,
            'statuses' => $statuses,
            'params' => $params
        ];

$export = $request->query('export');
        if ($export && $export == 'excel') {
            return Excel::download(new SifarisExport('admin.reports.excel.sifaris_status_report_partial', $bag), 'sifaris_status_report.xlsx');
        }

return view('admin.reports.sifaris_status_report', compact('bag'));
    }

private function get_wardwise_sifaris_status_report(Collection $offices, Collection $statuses, Collection $forms, $date_bs = null, $from_date = null, $to_date = null, $fiscal_id = null)
    {
        // dd($forms);
        $params = [];

$query = 'SELECT COUNT(*) AS total_count, sifaris_forms.id AS form_id,sifaris_forms.name AS form_name,categories.id as status_id,category_details.name as status_name, offices.id AS office_id, offices.name AS office_name FROM sifaris_records
        INNER JOIN sifaris_forms ON sifaris_forms.id=sifaris_records.form_id
        INNER JOIN categories ON categories.id=sifaris_records.status_id
        INNER JOIN category_details ON category_details.category_id=categories.id
        INNER JOIN offices ON sifaris_records.office_id=offices.id ';

$query .= ' WHERE 1=1 ';

$query .= ' GROUP BY sifaris_forms.id,sifaris_forms.name,categories.id,category_details.name,offices.id,offices.name';

// DB::enableQueryLog();
        $records = DB::select($query, $params);
        // dd(DB::getQueryLog());
        // dd($records);

$data = [];
        // dd($statuses);
        // dd($forms);

foreach ($forms as $f) {
            $form_data = [
                'id' => $f->id,
                'name' => $f->name,
                'office_data' => []
            ];

foreach ($offices as $o) {
                $office_data = [
                    'id' => $o->id,
                    'name' => $o->name,
                    'status_data' => []
                ];
                foreach ($statuses as $s) {
                    $status_data = [
                        'id' => $s->id,
                        'name' => $s->details[0]->name,
                        'total_count' => 0
                    ];
                    foreach ($records as $r) {
                        if ($r->status_id == $s->id && $r->office_id == $o->id && $r->form_id == $f->id) {
                            $status_data['total_count'] += $r->total_count;
                        }
                    }
                    $office_data['status_data'][] = $status_data;
                }

$form_data['office_data'][] = $office_data;
            }
            $data[] = $form_data;
        }

// dd($data);
        return $data;
    }

//helper functions
    private function get_offices($office)
    {
        $query = Office::active();
        if ($office->is_head_office) {
            return $query->orderBy('seq')->get();
        } else {
            return $query->where('id', $office->id)->orderBy('seq')->get();
        }
    }

private function get_forms($office)
    {
        $query = SifarisForm::active();
        if ($office->is_head_office) {
            return $query->get();
        } else {
            return $query->whereHas('form_scope', function ($q) {
                $q->whereIn('code', ['all', 'wards']);
            })->get();
        }
    }

private function get_filter_params(Request $request)
    {
        $date_type = request()->query('date_type');
        if (!$date_type) {
            $date_type = 'today';
        }

$fiscal_id = null;
        $single_day = null;
        $date_from = null;
        $date_to = null;
        $today = null;

$date_type = request()->query('date_type');
        if (!$date_type) $date_type = 'today';

$filter_text = '';

if ($date_type == 'fiscal_year') {
            $fiscal_id = request()->query('fiscal_id');
            if ($fiscal_id) {
                $fiscal_year = FiscalYear::active()->find($fiscal_id);
                $fiscal_id = $fiscal_year ? $fiscal_year->id : null;
                $filter_text = $fiscal_year->title_np;
            } else {
                $fiscal_year = fiscalYearByBS();
                $fiscal_id = $fiscal_year ? $fiscal_year->id : null;
                $filter_text = $fiscal_year->title_np;
            }
        }

if ($date_type == 'today') {
            $today = bsDate();
            $filter_text = $today;
        }

if ($date_type == 'single_day') {
            $single_day = request()->query('single_day');
            if (!$single_day) {
                $single_day = bsDate();
            }
            $filter_text = $single_day;
        }

if ($date_type == 'date_range') {
            $date_from = request()->query('date_from');
            if (!$date_from) {
                $date_from = bsDate();
            }

$date_to = request()->query('date_to');
            if (!$date_to) {
                $date_to = bsDate();
            }

$filter_text = $date_from . ' to ' . $date_to;
        }

return [
            'date_type' => $date_type,
            'today' => $today,
            'single_day' => $single_day,
            'fiscal_id' => $fiscal_id,
            'date_from' => $date_from,
            'date_to' => $date_to,
            'filter_text' => $filter_text
        ];
    }

private function get_filter_params_full(Request $request)
    {
        $params = $this->get_filter_params($request);

//get more params
        $params['office_id'] = request()->query('office_id');
        $params['status_id'] = request()->query('status_id');
        $params['payment_status_id'] = request()->query('payment_status_id');
        $params['form_id'] = request()->query('form_id');

return $params;
    }

private function get_payment_statuses()
    {
        return [
            [
                'id' => 'all',
                'name' => 'All'
            ],
            [
                'id' => 'free',
                'name' => 'Free'
            ],
            [
                'id' => 'paid',
                'name' => 'Paid'
            ]
        ];
    }

//List of Filtered Sifaris
    public function sifaris_list(Request $request)
    {
        // dd(0);
        $only = ['page_size', 'current_page', 'status_id', 'office_id','payment_status_id','form_id','date_type','fiscal_id','single_day','date_from','date_to'];
        $filter = $this->helper->getFilterApi($request, $only);

$data = $this->service->list($filter);
        $records = $data['records'];

$params = $this->get_filter_params_full($request);

$forms = $this->service->list_forms();
        $offices = $this->get_offices(office());
        $statuses = Category::where('type_id', masterType('sifaris_form_status')->id)->with(['details'])->get();

$bag = [
            'records' => $records,
            'params' => $params,
            'forms' => $forms,
            'offices' => $offices,
            'statuses' => $statuses,
            'payment_statuses' => $this->get_payment_statuses()
        ];

return view('admin.reports.sifaris_list', compact('bag'));
    }
}

${this.title}

Code Editor : ReportController.php