public function index(Request $request)
    {
        $semester_id = $request->input('semester_id');
        $students = Student::isStudying()->selectRaw('students.*,
						   	SUM(invoices.amount) as total,
						   	SUM(IF(type="debit",invoices.amount,0)) AS total_debit,
						   	SUM(IF(type="credit",invoices.amount,0)) AS total_credit
						   	')->leftJoin('financial_invoices AS invoices', function ($j) use($semester_id) {
            $j->on('invoices.student_id', '=', 'students.id');
            if ($semester_id) {
                $j->where('invoices.semester_id', '=', $semester_id);
            }
        })->groupBy('students.id');
        $total_amounts = Student::selectRaw('
        					SUM(IF(type="debit",invoices.amount,0)) AS total_debit,
						   	SUM(IF(type="credit",invoices.amount,0)) AS total_credit
						   	')->leftJoin('financial_invoices AS invoices', 'invoices.student_id', '=', 'students.id');
        $semesters = Semester::selectRaw('as.id, CONCAT(as.name, " ",ay.name) as name')->join('academycycle_years as ay', 'as.academycycle_year_id', '=', 'ay.id')->from('academycycle_semesters as as')->groupBy('as.id')->pluck('name', 'id');
        $year_term = year::join('academystructure_terms', 'academystructure_years.id', '=', 'academystructure_terms.year_id')->select(\DB::raw('CONCAT(academystructure_years.name, "-", academystructure_terms.name) as name,
										academystructure_terms.id as tid'))->groupBy('academystructure_terms.name', 'academystructure_years.name')->orderBy('academystructure_terms.id')->pluck('name', 'tid')->toArray();
        if ($request->has('name')) {
            $students->where('students.name', 'LIKE', "%" . $request->input('name') . "%");
        }
        if ($request->has('pay_type')) {
            $students->where('invoices.pay_type', 'LIKE', "%" . $request->input('pay_type') . "%");
        }
        if ($request->has('has_credit')) {
            $students->havingRaw('(SUM(IF(type="debit",invoices.amount,0))-SUM(IF(type="credit",invoices.amount,0)))>0');
        }
        if ($request->has('has_debit')) {
            $students->havingRaw('(SUM(IF(type="credit",invoices.amount,0))-SUM(IF(type="debit",invoices.amount,0)))>0');
        }
        if ($request->has('code')) {
            $students->where(function ($query) use($request) {
                return $query->orWhere('students.username', 'LIKE', "%" . substr($request->input('code'), -5, 5) . "%")->orWhere('students.username_prefix', 'LIKE', "%" . substr($request->input('code'), -5, 5) . "%");
            });
        }
        if ($request->has('year_term')) {
            $students->whereHas('department', function ($query) use($request) {
                $query->where('term_id', '=', $request->input('year_term'));
            });
        }
        if ($semester_id) {
            $students->where(function ($query) use($semester_id) {
                $query->orWhereNull('invoices.semester_id')->orWhere('semester_id', $semester_id);
            });
            $total_amounts->where('semester_id', $semester_id);
        }
        $has_search = count($request->except('page'));
        $students = $students->paginate(50);
        // return $students;
        $total_amounts = $total_amounts->first();
        return view('financials::reports.index', compact('students', 'has_search', 'semesters', 'total_amounts', 'year_term'));
    }
Example #2
0
    public function index(Request $request)
    {
        $subjects = Subject::select('*');
        if (request('QB_subject')) {
            $subjects->where('id', request('QB_subject'));
        }
        if (request('QB_year_term')) {
            $subjects->whereIn('id', explode(',', request('QB_year_term')));
        }
        $subjects = $subjects->paginate(20);
        $year_term = year::join('academystructure_terms', 'academystructure_years.id', '=', 'academystructure_terms.year_id')->join('academystructure_departments', 'academystructure_terms.id', '=', 'academystructure_departments.term_id')->select(\DB::raw('CONCAT(academystructure_years.name, "-", academystructure_terms.name) as name,
													GROUP_CONCAT(DISTINCT(academystructure_departments.subject_ids)) as sid'))->groupBy('academystructure_terms.name', 'academystructure_years.name')->orderBy('academystructure_terms.id', 'desc')->get()->toArray();
        $year_term_options;
        foreach ($year_term as $key => &$aa) {
            $a = implode(',', array_unique(array_merge(json_decode(str_replace('],[', ',', $aa['sid']), TRUE))));
            $year_term_options[$a] = $aa['name'];
        }
        $subject_list = Subject::pluck('name', 'id')->toArray();
        return view('subject::subjects.index', compact('subjects', 'subject_list', 'year_term_options'));
    }
Example #3
0
    public function index(Request $request)
    {
        $tickets = Ticket::orderBy('open', 'desc')->orderBy('priority', 'desc')->orderBy('tickets.updated_at', 'desc')->select('tickets.*')->with('replies', 'department', 'student')->Join('ticket_departments as td', function ($join) {
            $join->on('td.id', '=', 'tickets.department_id');
        })->Join('ticket_department_user as tdu', function ($join) {
            $join->on('tdu.department_id', '=', 'td.id')->where('user_id', "=", user()->id);
        });
        if (is_array(request('status'))) {
            $tickets->whereIn('open', request('status'));
        }
        if (is_array(request('department_id'))) {
            $tickets->whereIn('tickets.department_id', request('department_id'));
        }
        if (is_array(request('priority'))) {
            $tickets->whereIn('priority', request('priority'));
        }
        if ($request->has('subject')) {
            $tickets->where('subject', 'LIKE', '%' . request('subject') . '%');
        }
        if ($request->has('student')) {
            $tickets->where('tickets.student_id', request('student'));
            $student = Student::find(request('student'));
        }
        if ($request->has('create_at')) {
            $tickets->where('tickets.created_at', 'LIKE', '%' . request('create_at') . '%');
        }
        if (request('year_term')) {
            $tickets = $tickets->whereHas('student.department', function ($query) {
                $query->where('term_id', request('year_term'));
            });
        }
        if ($request->has('is_new')) {
            $tickets = $tickets->join('ticket_replies as tr', function ($join) {
                $join->on('tr.ticket_id', '=', 'tickets.id')->where('seen', '=', 0);
            });
        }
        $ticket_search_count = $tickets->count();
        $tickets = $tickets->paginate(30);
        $tickets->appends($request->except('page'));
        $ticketcount = Ticket::selectRaw('SUM(IF(open=0 ,1,0)) AS closed, SUM(IF(open=1 ,1,0)) AS open')->with('replies', 'department')->join('ticket_departments as td', function ($join) {
            $join->on('td.id', '=', 'tickets.department_id');
        })->join('ticket_department_user as tdu', function ($join) {
            $join->on('tdu.department_id', '=', 'td.id')->where('user_id', "=", user()->id);
        });
        $ticketcount = $ticketcount->first();
        //dd($ticketcount);
        $unseencount = Ticket::orderBy('open', 'desc')->join('ticket_replies as tr', function ($join) {
            $join->on('tr.ticket_id', '=', 'tickets.id')->where('seen', '=', 0);
        })->join('ticket_departments as td', function ($join) {
            $join->on('td.id', '=', 'tickets.department_id');
        })->join('ticket_department_user as tdu', function ($join) {
            $join->on('tdu.department_id', '=', 'td.id')->where('tdu.user_id', "=", user()->id);
        })->whereNull('tr.user_id')->count();
        $categories = TicketCategory::pluck('name', 'id')->toArray();
        $categories = [NULL => NULL] + $categories;
        $year_term = year::join('academystructure_terms', 'academystructure_years.id', '=', 'academystructure_terms.year_id')->select(\DB::raw('CONCAT(academystructure_years.name, "-", academystructure_terms.name) as name,
										academystructure_terms.id as tid'))->groupBy('academystructure_terms.name', 'academystructure_years.name')->orderBy('academystructure_terms.id')->pluck('name', 'tid')->toArray();
        $options = [];
        $departments = TicketDepartment::withDepth()->defaultOrder()->orderBy('parent_id', 'ASC')->get();
        foreach ($departments as $department) {
            $options[$department->id] = str_repeat("----", $department->depth) . $department->name;
        }
        $priorities = trans('tickets::tickets.priorities');
        $has_search = count($request->except('page', 'per_page'));
        if ($request->ajax()) {
            $unseen = Ticket::orderBy('open', 'desc')->join('ticket_replies as tr', function ($join) {
                $join->on('tr.ticket_id', '=', 'tickets.id')->where('seen', '=', 0);
            })->join('ticket_departments as td', function ($join) {
                $join->on('td.id', '=', 'tickets.department_id');
            })->join('ticket_department_user as tdu', function ($join) {
                $join->on('tdu.department_id', '=', 'td.id')->where('tdu.user_id', "=", user()->id);
            })->whereNull('tr.user_id')->count();
            $view = view('tickets::tickets._table', compact('categories', 'year_term', 'tickets', 'options', 'priorities', 'has_search', 'student', 'ticketcount', 'ticket_search_count'))->render();
            return response()->json(['unseen' => $unseen, 'view' => $view]);
        }
        return view('tickets::tickets.index', compact('tickets', 'categories', 'year_term', 'options', 'priorities', 'has_search', 'student', 'ticketcount', 'unseencount', 'ticket_search_count'));
    }