uparrow/app/Http/Controllers/Admin/AccountReportController.php

<?php

namespace App\Http\Controllers\Admin;

use Illuminate\Http\Request;

use App\Http\Requests;
use App\Http\Controllers\Controller;
use App\User;
use Auth;
use App\Models\AccountType;
use App\Models\AccountHead;
use App\Models\Journal;
use App\Models\PaymentHistory;
use App\Models\JournalComment;
use App\Models\CompanyModel;
class AccountReportController extends Controller
{
    function expense_report_view()
    {
        $data['credit_heads']=AccountHead::where('type_id',2)->get();
        $data['title']='Expense Report';
        return view('admin.acc_report.expense_report',$data);
    }

    function expense_report(Request $req)
    {
        $from=$req->get('from_date');
        $to=$req->get('to_date');
        $acc_head=$req->get('acc_head');

        if(empty($from) || empty($to)){
            return '';
        }

        $sql="SELECT ahd.name,j.* FROM journal j JOIN acc_head ahd ON j.ref_id=ahd.id WHERE DATE(j.created_at) >= '{$from}'  AND DATE(j.created_at) <= '{$to}'  AND ahd.type_id=2";
        if(!empty($acc_head))
        {
            $sql.=" AND ref_id={$acc_head} ";
        }
        $sql.=" order by j.id desc";


        $report_data=\DB::select($sql);


        $str="";
        $str.="<h4>Expense Report</h4>";
        $str.="<h5><strong>From</strong> {$from} <strong>To</strong> {$to}</h5>";
        $str.="<table class='table table-bordered table-striped'>";
        $str.="<thead>";
        $str.="<tr>";
        $str.="<th>Sl.</th>";
        $str.="<th>Date</th>";
        $str.="<th>Expense Name</th>";
        $str.="<th>Description</th>";
        $str.="<th>Document</th>";
        $str.="<th style='text-align:right;'>Amount</th>";
        $str.="</tr>";
        $str.="</thead>";
        $str.="<tbody>";
        if($report_data)
        {
            $i=1;
            $amnt=0;
            foreach($report_data as $row)
            {
                $str.="<tr>";
                $str.="<td>{$i}</td>";
                $dt=date('d M, Y',strtotime($row->created_at));
                $str.="<td>{$dt}</td>";
                $str.="<td>{$row->name}</td>";
                $str.="<td>{$row->description}</td>";
                $str.="<td><a href='".url('public/account_expense/'.$row->expense_document)."' target='_blank'>{$row->expense_document}</a></td>";
                $str.="<td style='text-align:right;'>{$row->amount}</td>";
                $amnt+= $row->amount;
                $str.="</tr>";
                $i++;
            }

            $str.="<tr>";
            $str.="<th colspan='5'>Total</th>";
            $amnt=number_format($amnt,2,'.','');
            $str.="<th style='text-align:right;'>{$amnt}</th>";
            $str.="</tr>";


        }else{
            $str.="<tr>";
            $str.="<td colspan='4'>No Record Found !</td>";
            $str.="</tr>";
        }
        $str.="</tbody>";
        $str.="</table>";       

        return $str;        
    }

    function revenue_report_view()
    {
        $data['companies']=CompanyModel::where('status',2)->orderBy('name')->get(['id','name']);
        $data['credit_heads']=AccountHead::where('type_id',4)->get();
        $data['title']='Revenue Report';
        return view('admin.acc_report.revenue_report',$data);
    }

    function revenue_report(Request $req)
    {
        $from=$req->get('from_date');
        $to=$req->get('to_date');
        $client=$req->get('client');
        $acc_head=$req->get('acc_head');

        if(empty($from) || empty($to)){
            return '';
        }

        $sql="SELECT ahd.name,j.* FROM journal j JOIN acc_head ahd ON j.ref_id=ahd.id WHERE DATE(j.created_at) >= '{$from}'  AND DATE(j.created_at) <= '{$to}' ";
        if(!empty($client))
        {
            $sql.=" AND client_id={$client} ";
        }

        if(!empty($acc_head))
        {
            $sql.=" AND ref_id={$acc_head} ";
        }

        $sql.="  AND ahd.type_id=4 order by j.id desc";
        $report_data=\DB::select($sql);


        $str="";
        $str.="<h4>Revenue Report</h4>";
        $str.="<h5><strong>From</strong> {$from} <strong>To</strong> {$to}</h5>";
        $str.="<table class='table table-bordered table-striped'>";
        $str.="<thead>";
        $str.="<tr>";
        $str.="<th>Sl.</th>";
        $str.="<th>Date</th>";
        $str.="<th>Client Name</th>";
        $str.="<th>Purpose</th>";
        $str.="<th>Description</th>";
        $str.="<th style='text-align:right;'>Amount</th>";
        $str.="</tr>";
        $str.="</thead>";
        $str.="<tbody>";
        if($report_data)
        {
            $i=1;
            $amnt=0;
            foreach($report_data as $row)
            {
                $str.="<tr>";
                $str.="<td>{$i}</td>";
                $dt=date('d M, Y',strtotime($row->created_at));
                $str.="<td>{$dt}</td>";

                if($row->client_id != 0){
                    $client_info=User::with(['company_data'])->find($row->client_id);
                    $client_name=$client_info->company_data?$client_info->company_data->name:'Not Found';
                }else{
                    $client_name = 'None';
                }
                $str.="<td>{$client_name}</td>";
                $str.="<td>{$row->name}</td>";
                $str.="<td>{$row->description}</td>";
                $str.="<td style='text-align:right;'>{$row->amount}</td>";
                $amnt+= $row->amount;
                $str.="</tr>";
                $i++;
            }

            $str.="<tr>";
            $str.="<th colspan='5'>Total</th>";
            $amnt=number_format($amnt,2,'.','');
            $str.="<th style='text-align:right;'>{$amnt}</th>";
            $str.="</tr>";


        }else{
            $str.="<tr>";
            $str.="<td colspan='6'>No Record Found !</td>";
            $str.="</tr>";
        }
        $str.="</tbody>";
        $str.="</table>";       

        return $str;    
    }
}