chess/app/Http/Controllers/HomeController.php

<?php

namespace App\Http\Controllers;

use App\Mail\ReportMail;
use App\Models\BudgetRealModel;
use App\Models\BudgetTypeModel;
use App\Models\MeetingModel;
use App\Models\MeetingParticipantModel;
use App\Models\ProjectCommentModel;
use Illuminate\Database\QueryException;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\ProjectsModel;
use App\Models\ProjectSubTaskModel;
use App\Models\ProjectTaskDocument;
use App\Models\TeamModel;
use App\Models\ProjectTaskModel;
use App\Models\TaskCommentModel;
use App\Models\TaskInboxModel;
use App\Exports\LaporanProjectExport;
use Carbon\Carbon;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Mail;
use Yajra\DataTables\DataTables;
use PDF;
use Excel;

class HomeController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
        // $this->middleware('auth');
    }

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Contracts\Support\Renderable
     */
    public function index()
    {
        /* if(Auth::user()->role_id==7) {
            return redirect(route('company'));
        }
        if(Auth::user()->role_id==6) {
            $progress = collect(DB::select("select
            round(((
                sum(
                (select count(id)
                from master_project_task mpt
                where mpt.project_id = mp.id and status_id = 3)
                )
            ) /
            nullif(
                sum(
                (select count(id)
                from master_project_task mpt
                where mpt.project_id = mp.id and status_id not in (4))
                )
            ,0)) * 100) as progress
            from master_project mp where mp.company_id = ?",[Auth::user()->company_id]))->first();
            $param['progress'] = $progress->progress;
            $param['listProject'] = DB::select("select mp.id, mp.project_name, null as team_role_id from master_project mp where mp.company_id = ?",[Auth::user()->company_id]);
        } else {
            $progress = collect(DB::select("select
            round(((
                sum(
                (select count(id)
                from master_project_task mpt
                where mpt.project_id = mp.id and status_id = 3)
                )
            ) /
            nullif(
                sum(
                (select count(id)
                from master_project_task mpt
                where mpt.project_id = mp.id and status_id not in (4))
                )
            ,0)) * 100) as progress
            from master_project mp
            join master_team mt on mt.team_code = mp.team_code and mt.member_id = :id
            where mp.company_id = :compId",['id' => Auth::user()->id, 'compId' => Auth::user()->company_id]))->first();
            $param['progress'] = $progress->progress;
            $param['listProject'] = DB::select("select mp.id, mp.project_name, mt.team_role_id from master_project mp
            join master_team mt on mt.team_code = mp.team_code and mt.member_id = :id
            where mp.company_id = :compId",['id' => Auth::user()->id, 'compId' => Auth::user()->company_id]);
        }
        $param['top5RunningProject'] = DB::select("select mp.project_name,
            (DATE_PART('day', concat(mp.due_date,' 00:00:00')::timestamp - concat(CURRENT_DATE,' 00:00:00')::timestamp)) as days_left,
            round(coalesce((((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3)::float) /
            nullif(((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float),0)*100),0)) as progress,
            (CASE WHEN (mp.logo_url is not NULL) THEN mp.logo_url ELSE mc.logo_url END) as logo_url
            from master_project mp
            left join master_company mc on mc.id = mp.company_id
            where mp.company_id = ? and (DATE_PART('day', concat(mp.due_date,' 00:00:00')::timestamp - concat(CURRENT_DATE,' 00:00:00')::timestamp)) > 0
            order by days_left LIMIT 5",[Auth::user()->company_id]);
        $param['sp'] = collect(DB::select("select
            count(mp.id) filter (where mp.status_id = 3) as project_done,
            count(mp.id) filter (where mp.status_id not in (4)) as all_project,
            coalesce(sum((select count(mpt.id) from master_project_task mpt where mpt.project_id = mp.id and mpt.status_id = 3)),0) as task_done,
            coalesce(sum((select count(mpt.id) from master_project_task mpt where mpt.project_id = mp.id and mpt.status_id not in (4))),0) as all_task
            from master_project mp
            where to_char(mp.created_at,'yyyy') = ? and mp.company_id = ?",[date('Y'), Auth::user()->company_id]))->first();
        $param['top5Wi'] = DB::select("select mu.id, mu.full_name, mu.image_url, munit.unit_name, coalesce(sum(rcl.bobot),0) as idx
            from master_user mu
            left join master_project_task mpt on mpt.pic_id = mu.id and status_id in (1,2)
            left join ref_critical_level rcl on mpt.critical_level_id = rcl.id
            left join master_unit munit on munit.id = mu.unit_id
            where mu.company_id = ?
            group by mu.id, mu.full_name, mu.image_url, munit.unit_name
            order by idx desc",[Auth::user()->company_id]); */
        $param['request'] = DB::table('ref_request')->get();
        $param['level'] = DB::table('ref_critical_level')->get();
        $param['getUser'] = DB::table('master_user as mu')
        ->leftJoin('ref_division as rd', 'rd.id', '=', 'mu.division_id')
        ->leftJoin('ref_role as rr', 'rr.id', '=', 'mu.role_id')
        ->leftJoin('master_client as mc', 'mc.id', '=', 'mu.client_id')
        ->select('mu.*', 'rd.definition as division', 'rr.definition as role', 'mc.client_name')
        ->where('mu.id', Auth::user()->id)
        ->first();
        if(Auth::user()->role_id==1) {
            $param['stat'] = collect(DB::select("select sum(stat_new) as stat_new, sum(stat_op) as stat_op, sum(stat_op) as stat_op, sum(stat_cancel) as stat_cancel,
            sum(stat_overdue) as stat_overdue, sum(stat_done) as stat_done
            from
            (select count(mpt.id) as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where user_crt_id = :id and mpt.status_id = 7
            union
            select 0 as stat_new, count(mpt.id) as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where user_crt_id = :id and mpt.status_id in (1,2,3,4)
            union
            select 0 as stat_new, 0 as stat_op, count(mpt.id) as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where user_crt_id = :id and mpt.status_id in (6, 8)
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, count(mpt.id) as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where user_crt_id = :id and mpt.status_id not in (5,6,7,8) and (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) >= 0
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, count(mpt.id) as stat_done
            from master_project_task mpt
            where user_crt_id = :id and mpt.status_id = 5 and status_approve = true) as x", ['id' => Auth::user()->id]))->first();

            /* $param['listoverdue'] = DB::select("select mpt.*, (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) as days_left
            from master_project_task mpt
            where user_crt_id = :id and mpt.status_id not in (5,6,7,8) and (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) >= 0
            order by mpt.id limit 5", ['id' => Auth::user()->id]); */
            $param['client'] = DB::table('master_client')->where('id', Auth::user()->client_id)->first();
            $param['project_pm'] = DB::table('master_project')->where('client_id', Auth::user()->client_id)->get();
        } else if(Auth::user()->role_id==4 || Auth::user()->role_id==5) {
            $param['stat'] = collect(DB::select("select sum(stat_new) as stat_new, sum(stat_op) as stat_op, sum(stat_op) as stat_op, sum(stat_cancel) as stat_cancel, sum(stat_overdue) as stat_overdue, sum(stat_done) as stat_done
            from
            (select count(mpt.id) as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            join master_project mp on mp.id = mpt.project_id
            where mp.client_id = :client and mpt.status_id = 7
            union
            select 0 as stat_new, count(mpt.id) as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            join master_project mp on mp.id = mpt.project_id
            where mp.client_id = :client and mpt.status_id in (1,2,3,4)
            union
            select 0 as stat_new, 0 as stat_op, count(mpt.id) as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            join master_project mp on mp.id = mpt.project_id
            where mp.client_id = :client and mpt.status_id in (6, 8)
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, count(mpt.id) as stat_overdue, 0 as stat_done
            from master_project_task mpt
            left join master_project mp on mp.id = mpt.project_id
            where mp.client_id = :client and mpt.status_id not in (5,6,7,8)
            and (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) >= 0
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, count(mpt.id) as stat_done
            from master_project_task mpt
            join master_project mp on mp.id = mpt.project_id
            where mp.client_id = :client and mpt.status_id = 5 and status_approve = true) as x", ['client' => Auth::user()->client_id]))->first();

            /* $param['listoverdue'] = DB::select("select mpt.*, (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) as days_left
            from master_project_task mpt
            left join master_project mp on mp.id = mpt.project_id
            where mp.client_id = :client and mpt.status_id not in (5,6,7,8) and (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) >= 0
            order by mpt.id limit 5", ['client' => Auth::user()->client_id]); */
            $param['client'] = DB::table('master_client')->where('id', Auth::user()->client_id)->first();
            $param['project_pm'] = DB::table('master_project')->where('client_id', Auth::user()->client_id)->get();
        } else {
            $param['stat'] = collect(DB::select("select sum(stat_new) as stat_new, sum(stat_op) as stat_op, sum(stat_op) as stat_op, sum(stat_cancel) as stat_cancel, sum(stat_overdue) as stat_overdue, sum(stat_done) as stat_done
            from
            (select count(mpt.id) as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where mpt.status_id = 7
            union
            select 0 as stat_new, count(mpt.id) as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where mpt.status_id in (1,2,3,4)
            union
            select 0 as stat_new, 0 as stat_op, count(mpt.id) as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where mpt.status_id in (6, 8)
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, count(mpt.id) as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where mpt.status_id not in (5,6,7,8) and (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) >= 0
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, count(mpt.id) as stat_done
            from master_project_task mpt
            where mpt.status_id = 5 and status_approve = true) as x"))->first();

            /* $param['listoverdue'] = DB::select("select mpt.*, (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) as days_left
            from master_project_task mpt
            where mpt.status_id not in (5,6,7,8) and (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) >= 0
            order by mpt.id limit 5"); */
            $param['client'] = DB::table('master_client')->get();
            $param['project_pm'] = DB::table('master_project')->get();
        }

        // return view('home')->nest('content', 'content.dashboard.index', $param);
        return $this->bsGetView('content.dashboard.index', $param);
    }

    public function changeYearsStatistik(Request $request)
    {
        $data = collect(DB::select("select
        count(mp.id) filter (where mp.status_id = 3) as project_done,
        count(mp.id) filter (where mp.status_id not in (4)) as all_project,
        coalesce(sum((select count(mpt.id) from master_project_task mpt where mpt.project_id = mp.id and mpt.status_id = 3)),0) as task_done,
        coalesce(sum((select count(mpt.id) from master_project_task mpt where mpt.project_id = mp.id and mpt.status_id not in (4))),0) as all_task
        from master_project mp
        where to_char(mp.created_at,'yyyy') = ? and mp.company_id = ?",[$request->tahun, Auth::user()->company_id]))->first();
        return response()->json(['data' => $data]);
    }

    public function laporanProject(Request $request)
    {
        $param['type'] = $request->get('type');
        $param['title_progres']  = '';
        if(Auth::user()->role_id==1) {
            $param['stat'] = collect(DB::select("select sum(stat_new) as stat_new, sum(stat_op) as stat_op, sum(stat_op) as stat_op, sum(stat_cancel) as stat_cancel,
            sum(stat_overdue) as stat_overdue, sum(stat_done) as stat_done
            from
            (select count(mpt.id) as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where user_crt_id = :id and mpt.status_id = 7
            union
            select 0 as stat_new, count(mpt.id) as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where user_crt_id = :id and mpt.status_id in (1,2,3,4)
            union
            select 0 as stat_new, 0 as stat_op, count(mpt.id) as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where user_crt_id = :id and mpt.status_id in (6, 8)
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, count(mpt.id) as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where user_crt_id = :id and mpt.status_id not in (5,6,7,8) and (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) >= 0
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, count(mpt.id) as stat_done
            from master_project_task mpt
            where user_crt_id = :id and mpt.status_id = 5 and status_approve = true) as x", ['id' => Auth::user()->id]))->first();
        } else if(Auth::user()->role_id==4 || Auth::user()->role_id==5) {
            $param['stat'] = collect(DB::select("select sum(stat_new) as stat_new, sum(stat_op) as stat_op, sum(stat_op) as stat_op, sum(stat_cancel) as stat_cancel, sum(stat_overdue) as stat_overdue, sum(stat_done) as stat_done
            from
            (select count(mpt.id) as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            join master_project mp on mp.id = mpt.project_id
            where mp.client_id = :client and mpt.status_id = 7
            union
            select 0 as stat_new, count(mpt.id) as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            join master_project mp on mp.id = mpt.project_id
            where mp.client_id = :client and mpt.status_id in (1,2,3,4)
            union
            select 0 as stat_new, 0 as stat_op, count(mpt.id) as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            join master_project mp on mp.id = mpt.project_id
            where mp.client_id = :client and mpt.status_id in (6, 8)
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, count(mpt.id) as stat_overdue, 0 as stat_done
            from master_project_task mpt
            left join master_project mp on mp.id = mpt.project_id
            where mp.client_id = :client and mpt.status_id not in (5,6,7,8)
            and (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) >= 0
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, count(mpt.id) as stat_done
            from master_project_task mpt
            join master_project mp on mp.id = mpt.project_id
            where mp.client_id = :client and mpt.status_id = 5 and status_approve = true) as x", ['client' => Auth::user()->client_id]))->first();
        } else {
            $param['stat'] = collect(DB::select("select sum(stat_new) as stat_new, sum(stat_op) as stat_op, sum(stat_op) as stat_op, sum(stat_cancel) as stat_cancel, sum(stat_overdue) as stat_overdue, sum(stat_done) as stat_done
            from
            (select count(mpt.id) as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where mpt.status_id = 7
            union
            select 0 as stat_new, count(mpt.id) as stat_op, 0 as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where mpt.status_id in (1,2,3,4)
            union
            select 0 as stat_new, 0 as stat_op, count(mpt.id) as stat_cancel, 0 as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where mpt.status_id in (6, 8)
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, count(mpt.id) as stat_overdue, 0 as stat_done
            from master_project_task mpt
            where mpt.status_id not in (5,6,7,8) and (DATE_PART('day', date_deadline::timestamp - CURRENT_DATE::timestamp)) >= 0
            union
            select 0 as stat_new, 0 as stat_op, 0 as stat_cancel, 0 as stat_overdue, count(mpt.id) as stat_done
            from master_project_task mpt
            where mpt.status_id = 5 and status_approve = true) as x"))->first();
        }
        /* if($request->get('percent')) {
            if($request->get('percent')=='lbh70') {
                $param['title_progres']  = 'Progress Lebih Dari 70%';
            } else if($request->get('percent')=='btwn5070') {
                $param['title_progres']  = 'Progress Diantara 50% dan 70%';
            } else if($request->get('percent')=='krg50') {
                $param['title_progres']  = 'Progress Dibawah 50%';
            }
        } */

        $param['level'] = DB::table('ref_critical_level')->where('is_active', true)->get();
        $param['status'] = DB::table('ref_status')->where('is_active', true)->whereNotIn('id',[7,8,4])->orderBy('id')->get();
        $param['table'] = route('projects.dataTable',['type'=>'laporan_project', 'model'=>$request->get('type')]);
        return $this->bsGetView('content.projects.laporanProject', $param);
    }

    public function approve_css(Request $request)
    {
        $param['table'] = route('projects.dataTable',['type'=>'approve_css']);
        return $this->bsGetView('content.projects.approve_css', $param);
    }

    public function approve_helpdesk(Request $request)
    {
        $param = [];
        return $this->bsGetView('content.projects.approve_helpdesk', $param);
    }

    public function detail_approve_helpdesk($id) {
        $param['title_progres']  = '';
        $param['table'] = route('projects.dataTable',['id' => $id, 'type'=>'detail_approve_helpdesk']);
        return $this->bsGetView('content.projects.detail_approve_helpdesk', $param);
    }

    public function helpdesk(Request $request)
    {
        $param = [];
        return $this->bsGetView('content.projects.helpdesk', $param);
    }

    public function helpdesk_data(Request $request)
    {
        $keyword = $request->get('keyword');
        $projects = DB::table('master_client as mc')
            ->when ( !is_null($keyword), function ($query) use($keyword) {
                return $query->whereRaw("LOWER(mc.client_name) LIKE '%" . strtolower($keyword) . "%'");
            })
            /* ->when ( !is_null($wherePercent), function ($query) use($wherePercent) {
                return $query->whereRaw($wherePercent);
            }) */
            ->orderBy('mc.client_name')
            ->paginate(4);
        $param['projects'] = $projects;
        $param['paginateLinks'] = (string) $projects->links();

        return response()->json([
            'rc' => 200,
            'data' => $param
        ]);
    }

    public function detail_helpdesk($id) {
        $param['title_progres']  = '';
        $param['level'] = DB::table('ref_critical_level')->where('is_active', true)->get();
        $param['status'] = DB::table('ref_status')->where('is_active', true)->get();
        $param['table'] = route('projects.dataTable',['id' => $id, 'type'=>'detail_helpdesk']);
        return $this->bsGetView('content.projects.detail_helpdesk', $param);
    }

    public function get_dokumen(Request $request) {
        $data['task'] = DB::table('master_project_task')->where('id', $request->id)->first();
        $data['crtName'] = collect(DB::select("select full_name from master_user where id = ?",[$data['task']->user_crt_id]))->first();
        $data['approveName'] = collect(DB::select("select full_name from master_user where id = ?",[$data['task']->user_approve_it]))->first();

        if($request->type) {
            $data['dokumen'] = DB::table('master_project_task_document as mptd')
                    ->where('type_doc', $request->type)
                    ->where('project_task_id', $request->id)
                    ->get();
        } else {
            $data['dokumen'] = DB::table('master_project_task_document as mptd')
                    ->where('project_task_id', $request->id)
                    ->get();
        }

        foreach ( $data['dokumen'] as $item ) {
            $item->space_url_doc = null;
            if ( SpaceController::checkFileIfExists($item->url_doc) ) {
                $item->space_url_doc = SpaceController::getFile($item->url_doc);
            }
        }
        return response()->json(['data' => $data]);
    }

    public function projects(Request $request)
    {
        $param['totalProject'] = collect(DB::select("SELECT count(id) AS total_project FROM master_project"))->first();
        // $param['totalProject'] = 0;
        $param['client'] = DB::table('master_client')->get();
        // $arrayListProject = DB::table('master_project')->selectRaw("replace(replace(array_agg(id)::text,'{',''),'}','')")->first();
        $param['project_pm'] = DB::connection('pgsql2')->table('master_project')->get();
        // dd($param);exit();
        /* $param['list_project'] = DB::select("SELECT mp.id, (CASE
            WHEN (mp.logo_url is not NULL) THEN mp.logo_url
            ELSE mc.logo_url
        end) as logo_url, mp.project_name, mu.unit_name,
        substring(mp.project_detail,1,30) as project_short, mp.project_detail,
        (SELECT count(id) FROM master_team WHERE team_code = mp.team_code) AS member, mp.due_date, mp.days_overdue, mp.status_id, rs.definition as status_project
        FROM master_project mp
        LEFT JOIN master_company mc ON mc.id = mp.company_id
        JOIN master_unit mu ON mu.id = mp.unit_owner_id
        JOIN ref_status rs on rs.id = mp.status_id
        ORDER BY mp.created_at"); */
        $param['type'] = 'projects';
        $param['percent'] = $request->get('percent');
        // return view('home')->nest('content', 'content.projects.index', $param);
        return $this->bsGetView('content.projects.index', $param);
    }

    public function data_projects(Request $request)
    {
        /* $wherePercent = null;
        if($request->get('percent') == 'krg50') {
            if(Auth::user()->role_id == 6) {
                $wherePercent = 'coalesce((((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3)::float) / nullif(((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float),0)*100),0) < 50';
            } else {
                $wherePercent = 'coalesce((((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3)::float) / nullif(((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4) and mpt.pic_id = '.Auth::user()->id.')::float),0)*100),0) < 50';
            }
        } else if($request->get('percent') == 'btwn5070') {
            if(Auth::user()->role_id == 6) {
                $wherePercent = 'coalesce((((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3)::float) / nullif(((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float),0)*100),0) between 50 and 70';
            } else {
                $wherePercent = 'coalesce((((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3)::float) / nullif(((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4) and mpt.pic_id = '.Auth::user()->id.')::float),0)*100),0) between 50 and 70';
            }
        } else if($request->get('percent') == 'lbh70') {
            if(Auth::user()->role_id == 6) {
                $wherePercent = 'coalesce((((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3)::float) / nullif(((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float),0)*100),0) > 70';
            } else {
                $wherePercent = 'coalesce((((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3)::float) / nullif(((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4) and mpt.pic_id = '.Auth::user()->id.')::float),0)*100),0) > 70';
            }
        } */
        $keyword = $request->get('keyword');
        $projects = DB::table('master_project as mp')
            ->leftJoin('master_client as mc', 'mc.id', '=', 'mp.client_id')
            ->selectRaw('mp.*, mc.client_name, mc.logo_url, substring(mp.project_detail,1,30) as project_short')
            ->when ( !is_null($keyword), function ($query) use($keyword) {
                return $query->whereRaw("LOWER(mp.project_name) LIKE '%" . strtolower($keyword) . "%'");
            })
            /* ->when ( !is_null($wherePercent), function ($query) use($wherePercent) {
                return $query->whereRaw($wherePercent);
            }) */
            ->orderBy('mp.created_at', 'desc')
            ->paginate(3);
        $param['projects'] = $projects;
        $param['paginateLinks'] = (string) $projects->links();

        return response()->json([
            'rc' => 200,
            'data' => $param
        ]);
    }

    public function myProjects(Request $request)
    {
        $param['list_project'] = DB::select("select mp.id, (CASE
            WHEN (mp.logo_url is not NULL) THEN mp.logo_url
            ELSE mc.logo_url
        end) as logo_url, mp.project_name, rs.definition as status_project, mp.status_id,
        count(mpt.id) filter (where mpt.status_id<>4 and mpt.pic_id = :id) as total_task,
        count(mpt.id) filter (WHERE mpt.status_id=3 and mpt.pic_id = :id) as total_done, mp.team_code, mt.team_role_id
        from master_project mp
        left join master_company mc on mc.id = mp.company_id
        left join master_team mt on mt.team_code = mp.team_code
        left join ref_status rs on rs.id = mp.status_id and rs.company_id = mp.company_id
        left join master_project_task mpt on mp.id = mpt.project_id
        where mt.member_id = :id
        group by mp.id, mp.logo_url, mc.logo_url, mp.project_name, rs.definition, mt.team_role_id",['id' => Auth::user()->id]);
        $param['type'] = 'myProjects';
        // return view('home')->nest('content', 'content.projects.myProjects', $param);
        return $this->bsGetView('content.projects.myProjects', $param);
    }

    public function kanbanProject($id, Request $request)
    {
        $param['idProject'] = $id;
        $param['namaProject'] = collect(DB::select("select project_name from master_project where id = ?",[$id]))->first();
        $param['status'] = DB::select("select * from ref_status where company_id = ?",[Auth::user()->company_id]);
        $team_role_id = collect(DB::select("select mt.team_role_id from master_team mt
        join master_project mp on mp.team_code = mt.team_code
        where mp.id = ? and mt.member_id = ?",[$id, Auth::user()->id]))->first();
        $param['team_role_id'] = $team_role_id->team_role_id;
        $param['member_id'] = $request->get('member_id');
        $param['member_team'] = DB::select("select mt.member_id, mu.full_name, rtr.definition from master_team mt
        join master_project mp on mp.team_code = mt.team_code
        left join master_user mu on mu.id = mt.member_id
        left join ref_team_role rtr on rtr.id = mt.team_role_id and mp.company_id = rtr.company_id
        where mp.id = ? order by mt.team_role_id",[$id]);
        $param['type'] = 'kanbanProjects';
        // return view('home')->nest('content', 'content.projects.kanbanProject',$param);
        return $this->bsGetView('content.projects.kanbanProject', $param);
    }

    public function budgeting_project($id, Request $request)
    {
        $param['id_project'] = $id;
        $param['team_role_id'] = $request->get('team_role_id');
        $param['dataHead'] = collect(DB::select("select * from master_project where id = ? ", [$id]))->first();
        $param['table'] = route('projects.dataTable',['type'=>'budgeting_project','id_project'=>$id,'team_role_id'=>$request->get('team_role_id')]);
        return $this->bsGetView('content.projects.budgeting_project', $param);
    }

    public function mng_meeting($id, Request $request)
    {
        $param['id_project'] = $id;
        $param['team_role_id'] = $request->get('team_role_id');
        $param['table'] = route('projects.dataTable',['type'=>'mng_meeting','id_project'=>$id,'team_role_id'=>$request->get('team_role_id')]);
        return $this->bsGetView('content.projects.mng_meeting', $param);
    }

    public function detailProjects(Request $request)
    {
        $param['id'] = $request->get('id');
        $param['dataHead'] = collect(DB::select("
            select mp.id, (CASE
                WHEN (mp.logo_url is not NULL) THEN mp.logo_url
                ELSE mc.logo_url
            end) as logo_url, mp.project_name, mu.unit_name,
                (select mu.full_name from master_user mu join master_team mt on mt.member_id = mu.id where mt.team_role_id = 1 and mt.team_code = mp.team_code) as PM,
                mp.project_detail, mp.start_date, mp.due_date, mp.status_id, rs.definition as status_project,
                (select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4)) as jml_task,
                (select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id =3) as jml_task_done,
                (select count(id) from master_project_comment mpc where mpc.project_id = mp.id) as jml_comment,
                (select string_agg(concat(image_url,'|',mu.full_name),',') from master_user mu join master_team mt on mt.member_id = mu.id where mt.team_code = mp.team_code)
                as foto_member, mp.team_code, mp.unit_owner_id, mp.start_date as tgl_mulai, mp.due_date as tgl_selesai, mp.budget_amount, mp.outstanding
            from master_project mp
            join master_company mc on mc.id = mp.company_id
            join master_unit mu on mu.id = mp.unit_owner_id
            join ref_status rs on rs.id = mp.status_id and rs.company_id = ?
            where mp.id = ?
        ",[Auth::user()->company_id, $request->get('id')]))->first();
        $param['tableHead'] = array(
            ["Member Name","all","member_name"],
            ["Jumlah Task","all","jml_task"],
            ["Task Selesai","all","task_done"],
            ["Task Belum Selesai","all","task_undone"],
            ["Act","all","action"]
        );
        foreach($param['tableHead'] as $v){
            $arrHead[] = $v[2];
        }
        $param['totTask'] = collect(DB::select("select count(id) as totonprogress from master_project_task mpt where status_id = 2 and project_id = ?",[$request->get('id')]))->first();
        $param['head'] = implode(",",$arrHead);
        $param['type'] = 'detailProjects';
        $param['team_role_id'] = $request->get('team_role_id');
        $param['table'] = route('projects.dataTable',['type'=>'detailProjects','id_project'=>$request->get('id'),'team_role_id'=>$request->get('team_role_id')]);
        $param['personil'] = DB::table('master_user')->whereNotIn('role_id',[6])->where('company_id', Auth::user()->company_id)->get();
        $param['role_team'] = DB::table('ref_team_role')->where('company_id',Auth::user()->company_id)->get();
        $param['projectOwner'] = DB::table('master_unit')->where('company_id',Auth::user()->company_id)->get();
        $param['before'] = $request->get('before');
        // return view('home')->nest('content', 'content.projects.detailProjects',$param);
        return $this->bsGetView('content.projects.detailProjects', $param);
    }

    public function listProjectSaya()
    {
        $param = [];
        return view('home')->nest('content', 'content.projects.listProjectSaya');
        return $this->bsGetView('content.projects.listProjectSaya', $param);
    }

    public function tambahProject()
    {
        $param['projectOwner'] = DB::table('master_unit')->where('company_id',Auth::user()->company_id)->get();
        $param['personil'] = DB::table('master_user')->whereNotIn('role_id',[6])->where('company_id', Auth::user()->company_id)->get();
        $param['role_team'] = DB::table('ref_team_role')->where('company_id', Auth::user()->company_id)->get();
        $param['type'] = 'tambahProjects';
        // return view('home')->nest('content', 'content.projects.tambahProject',$param);
        return $this->bsGetView('content.projects.tambahProject', $param);
    }

    public function inboxMeeting(Request $request)
    {
        $param = [];
        return $this->bsGetView('content.others.inbox.inbox_meeting', $param);
    }

    public function inboxMeetingData(Request $request)
    {
        $keyword = $request->get('keyword');
        $is_read = $request->get('is_read');
        $type_inbox = $request->get('type');
        if($type_inbox=='task') {
            $inbox = DB::table('master_task_inbox as mti')
                ->leftJoin('master_project as mp', 'mp.id', '=', 'mti.project_id')
                ->leftJoin('master_user as mu', 'mu.id', '=', 'mti.user_crt_id')
                ->selectRaw('mti.id, mti.is_read, mti.tittle, mp.project_name, mti.description as inv_content, mti.created_at as send_date, mu.image_url, mu.full_name')
                ->when ( !is_null($keyword), function ($query) use($keyword) {
                    return $query->whereRaw("(LOWER(mti.tittle) LIKE '%" . strtolower($keyword) . "%' OR LOWER(mp.project_name) LIKE '%" . strtolower($keyword) . "%')");
                })
                ->when ( !is_null($is_read), function ($query) use($is_read) {
                    if($is_read=='all') {
                    } else {
                        return $query->whereRaw("mti.is_read = ".$is_read."");
                    }
                })
                ->where('mti.pic_id', Auth::user()->id)
                ->orderBy('mti.created_at', 'desc')
                ->paginate(10);
        } else {
            $inbox = DB::table('master_meeting_participant as mmp')
                ->join('master_meeting as mm', 'mm.id', '=' , 'mmp.meeting_id')
                ->leftJoin('master_user as mu', 'mu.id', '=', 'mm.user_crt_id')
                ->join('master_project as mp', 'mp.id', '=' , 'mm.project_id')
                ->selectRaw('mmp.id,mmp.meeting_id,mmp.is_read,mu.full_name,mmp.created_at as send_date,mm.inv_content,mm.tittle,mp.project_name,mu.image_url')
                ->when ( !is_null($keyword), function ($query) use($keyword) {
                    return $query->whereRaw("(LOWER(mm.tittle) LIKE '%" . strtolower($keyword) . "%' OR LOWER(mp.project_name) LIKE '%" . strtolower($keyword) . "%')");
                })
                ->when ( !is_null($is_read), function ($query) use($is_read) {
                    if($is_read=='all') {
                    } else {
                        return $query->whereRaw("mmp.is_read = ".$is_read."");
                    }
                })
                ->where('mmp.participant_id', Auth::user()->id)
                ->orderBy('mm.created_at', 'desc')
                ->paginate(10);
        }
        $param['inbox'] = $inbox;
        $param['paginateLinks'] = (string) $inbox->links();

        return response()->json([
            'rc' => 200,
            'data' => $param
        ]);
    }

    public function act_projects(Request $request, $type)
    {
        switch ($type) {
            case 'store_data_projects':
                return $this->act_store_projects($request);
            break;
            case 'store_data_member':
                return $this->act_store_member($request);
            break;
            case 'store_data_task':
                return $this->act_store_task($request);
            break;
            case 'delete_data_task':
                return $this->act_delete_task($request);
            break;
            case 'update_status_task':
                return $this->act_update_status_task($request);
            break;
            case 'store_data_sub_task':
                return $this->act_store_sub_task($request);
            break;
            case 'store_data_comment_task':
                return $this->act_store_comment_task($request);
            break;
            case 'store_data_comment_project':
                return $this->act_store_comment_project($request);
            break;
            case 'store_data_generate_task':
                return $this->act_store_data_generate_task($request);
            break;
            case 'store_status_project':
                return $this->act_store_status_project($request);
            break;
            case 'store_data_budget':
                return $this->act_store_data_budget($request);
            break;
            case 'store_reversal_data_budget':
                return $this->act_store_reversal_data_budget($request);
            break;
            case 'store_data_budget_awal':
                return $this->act_store_data_budget_awal($request);
            break;
            case 'store_data_meeting':
                return $this->act_store_data_meeting($request);
            break;
            case 'read_inbox':
                return $this->act_read_inbox($request);
            break;
            case 'store_lanjutkan_task':
                return $this->act_store_lanjutkan_task($request);
            break;
            case 'store_reminder':
                return $this->act_store_reminder($request);
            break;
            case 'store_data_project_css':
                return $this->act_store_data_project_css($request);
            break;
            case 'store_css_task':
                return $this->act_store_css_task($request);
            break;
            case 'batal_css_task':
                return $this->act_batal_css_task($request);
            break;
            case 'proses_css_task':
                return $this->act_proses_css_task($request);
            break;
            case 'approve_proses_css_task':
                return $this->act_approve_proses_css_task($request);
            break;
            case 'reject_proses_css_task':
                return $this->act_reject_proses_css_task($request);
            break;
            case 'approve_open_css_task':
                return $this->act_approve_open_css_task($request);
            break;
            case 'reject_open_css_task':
                return $this->act_reject_open_css_task($request);
            break;
        }
    }

    public function act_approve_open_css_task($request) {
        DB::beginTransaction();
        try {
            //code...
            $pt = ProjectTaskModel::find($request->id);
            $emailMaker = DB::table('master_user')->where('id', $pt->user_crt_id)->first();
            $emailApprover = DB::table('master_user')->where('id', Auth::user()->id)->first();
            $data['ticket'] = $pt->task_code;
            $data['jenis'] = 1;
            // if($emailMaker) {
            //     $kirimToMaker = $this->kirimEmail($emailMaker->email, $data);
            // }
            /* if($kirimToMaker::failures()) {
                DB::rollBack();
                return response()->json([
                    'rc' => 99,
                    'rm' => 'Error Kirim Email'
                ]);
            } else { */
            // if($emailApprover) {
            //     $kirimToApprover = $this->kirimEmail($emailApprover->email, $data);
            // }
            /* } */
            $pt->status_id = 1;
            $pt->user_approve_it = Auth::user()->id;
            $pt->date_approve_it = date('Y-m-d H:i:s');
            $pt->save();
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil Approve Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_reject_open_css_task($request) {
        DB::beginTransaction();
        try {
            //code...
            $pt = ProjectTaskModel::find($request->id);
            $pt->status_id = 8;
            $pt->user_approve_it = Auth::user()->id;
            $pt->date_approve_it = date('Y-m-d H:i:s');
            $pt->save();
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil Reject Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_reject_proses_css_task($request) {
        DB::beginTransaction();
        try {
            //code...
            $pt = ProjectTaskModel::find($request->id);
            $pt->user_approve_id = Auth::user()->id;
            $pt->date_approve = date('Y-m-d H:i:s');
            $pt->status_id = 1;
            $pt->send_approve = false;
            $pt->status_approve = false;
            $pt->note_approval = $request->note_approval;
            $pt->save();
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil Reject Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_approve_proses_css_task($request) {
        DB::beginTransaction();
        try {
            //code...
            $pt = ProjectTaskModel::find($request->id);
            $emailMaker = DB::table('master_user')->where('id', $pt->user_crt_id)->first();
            $emailApprover = DB::table('master_user')->where('id', $pt->user_approve_it)->first();
            $status = DB::table('ref_status')->where('id', $pt->status_id)->first();
            $data['ticket'] = $pt->task_code;
            $data['status'] = $status->definition;
            $data['catatan'] = $pt->note_approval;
            $data['pelapor'] = $emailMaker->full_name;
            $data['jenis'] = 2;
            // if($emailMaker) {
            //     $kirimToMaker = $this->kirimEmail($emailMaker->email, $data);
            // }

            // if($emailApprover) {
            //     $kirimToApprover = $this->kirimEmail($emailApprover->email, $data);
            // }

            $pt->user_approve_id = null;
            $pt->date_approve = date('Y-m-d H:i:s');
            $pt->user_upd_id = Auth::user()->id;
            $pt->status_id = 5;
            $pt->send_approve = false;
            $pt->status_approve = true;
            $pt->note_approval = $request->note_approval;
            $pt->save();
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil Approve Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_proses_css_task($request) {
        DB::beginTransaction();
        try {
            //code...
            $pt = ProjectTaskModel::find($request->id);


            $emailMaker = DB::table('master_user')->where('id', $pt->user_crt_id)->first();
            $emailApprover = DB::table('master_user')->where('id', $pt->user_approve_it)->first();
            $status = DB::table('ref_status')->where('id', $pt->status_id)->first();
            $data['ticket'] = $pt->task_code;
            $data['status'] = $status->definition;
            $data['catatan'] = $pt->notes_imp;
            $data['pelapor'] = $emailMaker->full_name;
            $data['jenis'] = 2;

            $pt->user_imp_id = Auth::user()->id;
            $pt->notes_imp = $request->notes_imp;
            $pt->critical_level_id = $request->critical_level_id;
            $pt->date_deadline = date('Y-m-d', strtotime($request->date_deadline));
            if($request->status_id==5) {
                $pt->status_id = 4;
                $pt->send_approve = true;
            } else {
                $pt->status_id = $request->status_id;
                $pt->send_approve = false;
            }
            $pt->save();

            // $kirimToMaker = $this->kirimEmail($emailMaker->email, $data);
            // if(!empty($emailApprover)) {
            //     $kirimToApprover = $this->kirimEmail($emailApprover->email, $data);
            // }


            if($request->hasFile('dok_helpdesk')){


                $destination_path = public_path('dokumen');
                $files = $request->file('dok_helpdesk');
                $urlDoc = SpaceController::uploadFile($files, 'dokumen', []);


                // $filename = 'Dokumen_'.date('Ymd')."_".date('His').'.'.$files->getClientOriginalExtension();
                // $upload_success = $files->move($destination_path, $filename);


                $ptd = new ProjectTaskDocument();
                $ptd->project_task_id = $pt->id;
                $ptd->url_doc = $urlDoc;
                $ptd->user_crt_id = Auth::user()->id;
                $ptd->type_doc = 2;
                $ptd->save();
            }
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil Kirim Approval Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_batal_css_task($request) {
        DB::beginTransaction();
        try {
            //code...
            $pt = ProjectTaskModel::find($request->id);
            $pt->status_id = 6;
            $pt->save();
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil Batalkan Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_css_task($request) {
        // dd($request->all());exit();
        DB::beginTransaction();
        try {
            //code...
            $checkRole = DB::table('ref_role')->where('id', Auth::user()->role_id)->first();

            $mp = ProjectsModel::find($request->project_id_modal_);
            $get = collect(DB::select("select max(id) as max_id from master_project_task"))->first();
            $codeGenerate = str_pad($get->max_id+1, 5, 0, STR_PAD_LEFT);
            $pt = new ProjectTaskModel();
            $pt->project_id = $request->project_id_modal_;
            $pt->project_id_pm = $mp->project_id_pm;
            $pt->task_name = $request->judul_;
            $pt->task_code = date('Ymd').$codeGenerate;
            $pt->task_note = $request->deskripsi_;
            if($checkRole) {
                if($checkRole->is_external) {
                    $pt->status_id = 7;
                } else {
                    $pt->status_id = 1;
                }
            } else {
                $pt->status_id = 7;
            }
            $pt->critical_level_id = $request->level_;
            $pt->user_crt_id = Auth::user()->id;
            $pt->client_id = $request->client_id_modal_;
            if($pt->save()) {
                for ($i=1; $i <= $request->row_file ; $i++) {
                    # code...
                    if($request->hasFile('file_input_'.$i.'_')){
                        $destination_path = public_path('dokumen');
                        $files = $request->file('file_input_'.$i.'_');
                        $urlDoc = SpaceController::uploadFile($files, 'dokumen', []);

                        // $filename = 'Dokumen_'.date('Ymd')."_".date('His').'.'.$files->getClientOriginalExtension();
                        // $upload_success = $files->move($destination_path, $filename);

                        $ptd = new ProjectTaskDocument();
                        $ptd->project_task_id = $pt->id;
                        $ptd->url_doc = $urlDoc;
                        $ptd->user_crt_id = Auth::user()->id;
                        $ptd->type_doc = 1;
                        $ptd->save();
                    }
                }
            }
            $emailMaker = DB::table('master_user')->whereIn('id', [13, 21, 14])->get()->toArray();
            $status = DB::table('ref_status')->where('id', $pt->status_id)->first();
            $data['ticket'] = $pt->task_code;
            $data['status'] = $status->definition;
            $data['catatan'] = $pt->notes_imp;
            $data['jenis'] = 2;
            if(!empty($emailMaker)) {
                foreach($emailMaker as $em) {
                    $data['pelapor'] = $em->full_name;
                    if($em->email) {
                        // $kirimToMaker = $this->kirimEmail($em->email, $data);
                    }
                }
            }

            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_data_project_css($request) {
        DB::beginTransaction();
        try {
            //code...
            $id = $request->input('get_id');
            if($id == '') {
                $mp = new ProjectsModel();
                $mp->user_crt_id = Auth::user()->id;
            } else {
                $mp = ProjectsModel::findOrFail($id);
                $mp->user_upd_id = Auth::user()->id;
                DB::connection('pgsql2')->table('master_project')->where('id', $mp->project_id_pm)->update([
                    'project_id_css' => null,
                    'client_id_css' => null
                ]);
            }

            $mp->project_name = $request->project_name;
            $mp->project_detail = $request->info_detail_project;
            $mp->client_id = $request->client_project;
            $mp->project_id_pm = $request->project_id;
            if($mp->save()) {
                DB::connection('pgsql2')->table('master_project')->where('id', $request->project_id)->update([
                    'project_id_css' => $mp->id,
                    'client_id_css' => $request->client_project
                ]);
            }

            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Project'
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_reminder($request) {
        $key_demo ='88749978e47dcff1496c29213cbb9b8964fd677f7a6dd17d';
        $mpt = ProjectTaskModel::findOrFail($request->id);
        $message = 'Kamu mendapatkan task baru untuk Project '.$request->project_name.' : \n'. $mpt->task_name . '\n\nUntuk melihat detail task silahkan akses ke: \nhttp://quince-project.basys.co.id';
        $number = substr_replace(trim($request->phone_number),'62',0,1);
        $data = array(
            "phone_no"=> $number,
            "key"     => $key_demo,
            "message" => $message,
        );

        $result = $this->sendWA($data);
        if($result == 'Success') {
            return response()->json([
                'rc' => 0,
                'rm' => 'sukses'
            ]);
        } else {
            return response()->json([
                'rc' => 500,
                'rm' => $result
            ]);
        }
        // return $result;
    }

    public function sendWA($data)
    {
        $url='http://116.203.191.58/api/send_message';

        $data_string = json_encode($data,1);
        $ch = curl_init($url);
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, "POST");
        curl_setopt($ch, CURLOPT_POSTFIELDS, $data_string);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
        curl_setopt($ch, CURLOPT_VERBOSE, 0);
        curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 0);
        curl_setopt($ch, CURLOPT_TIMEOUT, 360);
        curl_setopt($ch, CURLOPT_SSL_VERIFYPEER, 0);
        curl_setopt($ch, CURLOPT_SSL_VERIFYHOST, 0);
        curl_setopt($ch, CURLOPT_HTTPHEADER, array(
            'Content-Type: application/json',
            'Content-Length: ' . strlen($data_string),
            'Authorization: Basic dXNtYW5ydWJpYW50b3JvcW9kcnFvZHJiZWV3b293YToyNjM3NmVkeXV3OWUwcmkzNDl1ZA=='
        ));
        $res=curl_exec($ch);
        curl_close($ch);

        return $res;
    } // end function

    public function act_store_lanjutkan_task (Request $request) {
        DB::beginTransaction();
        try {
            $mpt = ProjectTaskModel::findOrFail($request->id_task_lanjutkan);

            $newMpt = new ProjectTaskModel();
            $newMpt->project_id = $mpt->project_id;
            $newMpt->task_name = '[Forward Task] - '.$mpt->task_name;
            $newMpt->task_code = $mpt->task_code;
            $newMpt->task_note = '[Forward Task] - '.$mpt->task_note;
            $newMpt->pic_id = $request->personil_task_pilihan;
            $newMpt->start_date = $mpt->start_date;
            $newMpt->due_date = $mpt->due_date;
            $newMpt->status_id = 1;
            $newMpt->critical_level_id = $mpt->critical_level_id;
            $newMpt->user_crt_id = Auth::user()->id;
            $newMpt->company_id = $mpt->company_id;
            $newMpt->save();

            $mit = new TaskInboxModel();
            $mit->project_id = $newMpt->project_id;
            $mit->project_task_id = $newMpt->id;
            $mit->tittle = $newMpt->task_name;
            $mit->description = $newMpt->task_note;
            $mit->pic_id = $request->personil_task_pilihan;
            $mit->is_read = false;
            $mit->user_crt_id = Auth::user()->id;
            $mit->save();
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_read_inbox(Request $request) {
        DB::beginTransaction();
        try {
            if($request->type=='meetings') {
                $mmp = MeetingParticipantModel::findOrFail($request->id);
                $mmp->is_read = true;
                $mmp->save();
            } else {
                $mmp = TaskInboxModel::findOrFail($request->id);
                $mmp->is_read = true;
                $mmp->save();
            }

            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_data_meeting(Request $request) {
        DB::beginTransaction();
        try {
            //code...
            $id = $request->get_id;
            if($id) {
                $mm = MeetingModel::findOrFail($id);
                $mm->mom_content = $request->mom_content;
                $mm->is_done = $request->is_done_edit;
                if($request->hasFile('mom_doc_url')){
                    $destination_path = public_path('dok_meeting');
                    // $image_path = public_path($mm->mom_doc_url);
                    // if(File::exists($image_path)) {
                    //     File::delete($image_path);
                    // }

                    SpaceController::deleteFile($mm->mom_doc_url);
                    $files = $request->file('mom_doc_url');
                    $mm->mom_doc_url = SpaceController::uploadFile($files, 'meeting', []);

                    // $filename = 'dok_'.date('Ymd')."_".date('His').'.'.$files->getClientOriginalExtension();
                    // $upload_success = $files->move($destination_path, $filename);
                    // $mm->mom_doc_url =  '/dok_meeting/'.$filename;


                }
                $mm->user_upd_id = Auth::user()->id;
                $mm->save();
                if($request->row_anggota > 0) {
                    for ($i=1; $i <= $request->row_anggota ; $i++) {
                        $mp = MeetingParticipantModel::findOrFail($request->input('mp_id'.$i));
                        $mp->is_present = $request->input('hadir'.$i)=='true'?true:false;
                        $mp->notes = $request->input('ket'.$i)?$request->input('ket'.$i):null;
                        $mp->user_upd_id = Auth::user()->id;
                        $mp->save();
                    }
                }
            } else {
                // $getMm = DB::table('master_meeting')->max('id');
                // $getMmp = DB::table('master_meeting_participant')->max('id');
                $mm = new MeetingModel();
                // $mm->id = $getMm+1;
                $mm->project_id = $request->id_project;
                $mm->tittle = $request->title;
                $mm->start_date = date('Y-m-d', strtotime($request->start_date));
                $mm->start_time = $request->start_time;
                $mm->end_time = $request->end_time;
                $mm->is_done = false;
                $mm->inv_content = $request->inv_content;
                $mm->user_crt_id = Auth::user()->id;
                $mm->save();
                $dataMm = [];
                for($i=1; $i<=$request->row_anggota;$i++) {
                    array_push($dataMm, array(
                        // 'id' => $getMmp + $i,
                        'meeting_id' => $mm->id,
                        'participant_id' => $request->input('personil_id_'.$i),
                        'is_present' => null,
                        'is_read' => false,
                        'user_crt_id' => Auth::user()->id,
                        'created_at' => Carbon::now(),
                        'updated_at' => Carbon::now(),
                    ));
                }

                MeetingParticipantModel::insert($dataMm);
            }
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_data_budget_awal(Request $request) {
        DB::beginTransaction();
        try {
            //code...
            $amount_tx = str_replace('.','',$request->amount_tx_ba);
            // $get = BudgetRealModel::all()->last();
            // if($get) {
                // $idNow = $get->id + 1;
            // } else {
                // $idNow = 1;
            // }
            $mbr = new BudgetRealModel();
            // $mbr->id = $idNow;
            $mbr->project_id = $request->project_id;
            $mbr->budget_type = 1;
            $mbr->amount_tx = $amount_tx;
            $mbr->tx_note = 'Budgeting Awal Project';
            $mbr->tx_date = date('Y-m-d');
            $mbr->created_at = date('Y-m-d');
            $mbr->user_crt_id = Auth::user()->id;
            $mbr->company_id = Auth::user()->company_id;
            $mbr->outstanding = $amount_tx;
            $mbr->save();

            $mp = ProjectsModel::findOrFail($request->project_id);
            $mp->budget_amount = $amount_tx;
            $mp->outstanding = $amount_tx;
            $mp->save();

            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil',
                'budget_awal' => $amount_tx
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_reversal_data_budget(Request $request) {
        DB::beginTransaction();
        try {
            //code...
            $bt = DB::table('ref_budget_type')->where('id', $request->typeBudget)->where('company_id', Auth::user()->company_id)->first();
            $rev_operator = $bt->rev_operator;
            $mbr = BudgetRealModel::findOrFail($request->idBudget);
            $amount_tx = $mbr->amount_tx;
            $project_id = $mbr->project_id;
            $mp = ProjectsModel::findOrFail($project_id);
            $outstandingPr = $mp->outstanding;
            $budgetPr = $mp->budget_amount;
            if($rev_operator=='-') {
                $outstanding = $outstandingPr - $amount_tx;
                $budget = $budgetPr - $amount_tx;
            } else {
                $outstanding = $outstandingPr + $amount_tx;
                $budget = $budgetPr + $amount_tx;
            }

            if($request->typeBudget==1) {
                $mp->budget_amount = $outstanding;
            } else if($request->typeBudget==8 || $request->typeBudget==9) {
                $mp->budget_amount = $budget;
            }

            $mp->outstanding = $outstanding;

            $mp->save();

            BudgetRealModel::findOrFail($request->idBudget)->delete();

            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil',
                'budget' => $mp->budget_amount,
                'outstanding' => $outstanding
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_data_budget(Request $request) {
        DB::beginTransaction();
        try {
            //code...
            $mp = ProjectsModel::findOrFail($request->project_id);
            $outstandingLast = $mp->outstanding;
            $budgetLast = $mp->budget_amount;
            if($request->operator=='-') {
                $outstanding = $outstandingLast - str_replace('.','',$request->amount_tx);
                $budget = $budgetLast - str_replace('.','',$request->amount_tx);
            } else {
                $outstanding = $outstandingLast + str_replace('.','',$request->amount_tx);
                $budget = $budgetLast + str_replace('.','',$request->amount_tx);
            }
            // $get = BudgetRealModel::all()->last()->id;
            $mbr = new BudgetRealModel();
            // $mbr->id = $get + 1;
            $mbr->project_id = $request->project_id;
            $mbr->budget_type = $request->budget_type;
            $mbr->amount_tx = str_replace('.','',$request->amount_tx);
            $mbr->tx_note = $request->tx_note;
            $mbr->tx_date = date('Y-m-d', strtotime($request->tx_date));
            $mbr->created_at = date('Y-m-d');
            $mbr->user_crt_id = Auth::user()->id;
            $mbr->company_id = Auth::user()->company_id;
            $mbr->outstanding = $outstanding;
            $mbr->save();
            if($request->budget_type==8 || $request->budget_type==9) {
                $mp->budget_amount = $budget;
            }
            $mp->outstanding = $outstanding;
            $mp->save();
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil',
                'budget' => $mp->budget_amount,
                'outstanding' => $outstanding
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_status_project(Request $request) {
        DB::beginTransaction();
        try {
            $mp = ProjectsModel::find($request->id_project);
            $mp->status_id = $request->pilih_status_project;
            $mp->save();
            //code...
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Comment Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_comment_project(Request $request) {
        DB::beginTransaction();
        try {
            $mpt = new ProjectCommentModel();
            $mpt->project_id = $request->id_project;
            $mpt->comment_note = $request->comment_note;
            $mpt->user_crt_id = Auth::user()->id;
            $mpt->created_at = date('Y-m-d H:i:s');
            $mpt->company_id = Auth::user()->company_id;
            $mpt->save();
            $jmlComment = collect(DB::select("select count(id) as jmlcomment from master_project_comment mpc where mpc.project_id = ?",[$request->id_project]))->first();
            //code...
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Comment Task',
                'jmlComment' => $jmlComment->jmlcomment
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_comment_task(Request $request){
        DB::beginTransaction();
        try {
            $mpt = new TaskCommentModel();
            $mpt->project_id = $request->id_project;
            $mpt->task_id = $request->id_task;
            $mpt->comment_note = $request->comment_task_note;
            $mpt->user_crt_id = Auth::user()->id;
            $mpt->created_at = date('Y-m-d H:i:s');
            $mpt->company_id = Auth::user()->company_id;
            $mpt->save();
            //code...
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Comment Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_sub_task(Request $request) {
        DB::beginTransaction();
        try {
            if($request->get_id) {
                $mpt = ProjectSubTaskModel::find($request->get_id);
                $mpt->user_upd_id = Auth::user()->id;
            } else {
                $mpt = new ProjectSubTaskModel();
                // $get = collect(DB::select("select max(id) as max_id from master_project_subtask"))->first();
                // $mpt->id = $get->max_id + 1;
                $mpt->user_crt_id = Auth::user()->id;
            }
            $mpt->task_id = $request->task_id;
            $mpt->subtask_detail = $request->subtask_detail;
            $mpt->status_id = $request->status_id_st;
            $mpt->company_id = Auth::user()->company_id;
            $mpt->save();
            //code...
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Sub Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_update_status_task(Request $request) {
        DB::beginTransaction();
        try {
            //code...
            $mpt = ProjectTaskModel::find($request->id);
            $mpt->status_id = $request->status_id;
            $mpt->save();
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Task',
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_task(Request $request) {
        DB::beginTransaction();
        try {
            if($request->get_id) {
                $mpt = ProjectTaskModel::find($request->get_id);
                $mpt->user_upd_id = Auth::user()->id;
            } else {
                $mpt = new ProjectTaskModel();
                $get = collect(DB::select("select max(id) as max_id from master_project_task"))->first();
                // $mpt->id = $get->max_id + 1;
                $mpt->user_crt_id = Auth::user()->id;
                $companyCode = collect(DB::select("select company_code from master_company mc where id = ?",[Auth::user()->company_id]))->first();
                $codeGenerate = str_pad($get->max_id+1, 5, 0, STR_PAD_LEFT);
                $codeTask = $companyCode->company_code.$request->project_id.$request->pic_id.$codeGenerate;
                $mpt->task_code = $codeTask;
            }

            if($request->hasFile('dok_task')){
                $destination_path = public_path('dokumen');
                $image_path = public_path($mpt->url_doc);
                if(File::exists($image_path)) {
                    File::delete($image_path);
                }
                $files = $request->file('dok_task');
                $urlDoc = SpaceController::uploadFile($files, 'dokumen', []);

                // $filename = 'Dokumen_'.date('Ymd')."_".date('His').'.'.$files->getClientOriginalExtension();
                // $upload_success = $files->move($destination_path, $filename);

                $mpt->url_doc = $urlDoc;
            }

            $mpt->project_id = $request->project_id;
            $mpt->task_name = $request->task_name;
            $mpt->task_note = $request->task_note;
            $mpt->pic_id = $request->pic_id;
            $mpt->start_date = date('Y-m-d', strtotime($request->start_date));
            $mpt->due_date = date('Y-m-d', strtotime($request->due_date));
            $mpt->status_id = $request->status_id;
            $mpt->critical_level_id = $request->critical_level_id;
            $mpt->company_id = Auth::user()->company_id;
            $mpt->save();

            if($request->get_id) {
                $mit = TaskInboxModel::where('project_task_id')->first();
                $mit->tittle = $request->task_name;
                $mit->description = $request->task_note;
                $mit->user_upd_id = Auth::user()->id;
                $mit->save();
            } else {
                $mit = new TaskInboxModel();
                $mit->project_id = $request->project_id;
                $mit->project_task_id = $mpt->id;
                $mit->tittle = $request->task_name;
                $mit->description = $request->task_note;
                $mit->pic_id = $request->pic_id;
                $mit->is_read = false;
                $mit->user_crt_id = Auth::user()->id;
                $mit->save();
            }

            $jml_task = collect(DB::select("select count(id) as jml_task from master_project_task where project_id = ? and status_id not in (4)",[$request->project_id]))->first();
            $task_on_progress = collect(DB::select("select count(id) as totonprogress from master_project_task mpt where status_id = 2 and project_id = ?",[$request->project_id]))->first();
            //code...
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Task',
                'jml_task' => $jml_task,
                'task_on_progress' => $task_on_progress
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_data_generate_task(Request $request) {
        DB::beginTransaction();
        try {
            for($i=1; $i<=$request->row_modeling; $i++) {
                $mpt = new ProjectTaskModel();
                $get = collect(DB::select("select max(id) as max_id from master_project_task"))->first();
                // $mpt->id = $get->max_id + 1;
                $mpt->user_crt_id = Auth::user()->id;
                $companyCode = collect(DB::select("select company_code from master_company mc where id = ?",[Auth::user()->company_id]))->first();
                $codeGenerate = str_pad($get->max_id+1, 5, 0, STR_PAD_LEFT);
                $codeTask = $companyCode->company_code.$request->id_project.$request->input('pic_id_generate'.$i).$codeGenerate;
                $mpt->project_id = $request->id_project;
                $mpt->task_name = $request->input('task_name_generate'.$i);
                $mpt->task_code = $codeTask;
                $mpt->task_note = $request->input('task_note_generate'.$i);
                $mpt->pic_id = $request->input('pic_id_generate'.$i);
                $mpt->start_date = date('Y-m-d', strtotime($request->input('start_date_generate'.$i)));
                $mpt->due_date = date('Y-m-d', strtotime($request->input('due_date_generate'.$i)));
                $mpt->status_id = $request->input('status_id_generate'.$i);
                $mpt->critical_level_id = $request->input('critical_level_id_generate'.$i);
                $mpt->company_id = Auth::user()->company_id;
                $mpt->save();
            }

            $jml_task = collect(DB::select("select count(id) as jml_task from master_project_task where project_id = ? and status_id not in (4)",[$request->id_project]))->first();
            $task_on_progress = collect(DB::select("select count(id) as totonprogress from master_project_task mpt where status_id = 2 and project_id = ?",[$request->id_project]))->first();
            //code...
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Task',
                'jml_task' => $jml_task,
                'task_on_progress' => $task_on_progress
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_delete_task(Request $request) {
        DB::beginTransaction();
        try {
            $mpt = ProjectTaskModel::find($request->id);
            $mpt->status_id = 4;
            $mpt->save();

            // $mit = TaskInboxModel::where('project_task_id', $request->id)->delete();

            $jml_task = collect(DB::select("select count(id) as jml_task from master_project_task where project_id = ? and status_id not in (4)",[$request->project_id]))->first();
            $task_on_progress = collect(DB::select("select count(id) as totonprogress from master_project_task mpt where status_id = 2 and project_id = ?",[$request->project_id]))->first();
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Task',
                'jml_task' => $jml_task,
                'task_on_progress' => $task_on_progress
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_projects(Request $request) {
        DB::beginTransaction();
        try {
            //code...
            $min = 5;
            $get = collect(DB::select("SELECT max(id::int) as max_id FROM master_project"))->first();
            $getTC = collect(DB::select("SELECT coalesce(max(substr(team_code,5,".$min.")::int),0) as max_number FROM master_team WHERE company_id = ?",[Auth::user()->company_id]))->first();
            $getTCId = collect(DB::select("SELECT max(id::int) as max_id FROM master_team"))->first();
            // $getMbrId = collect(DB::select("SELECT max(id::int) as max_id FROM master_budget_real"))->first();
            $arrayTeam = [];
            $id = $request->input('get_id');
            if($id=='') {
                $mp = new ProjectsModel();
                $mt = new TeamModel();
                $mbr = new BudgetRealModel();
                // $mp->id = $get->max_id + 1;
                $mp->project_name = $request->nama_project;
                $mp->unit_owner_id = $request->project_owner;
                $mp->start_date = date('Y-m-d', strtotime($request->tgl_mulai));
                $mp->due_date = date('Y-m-d', strtotime($request->tgl_selesai));
                $mp->days_overdue = round((strtotime($request->tgl_selesai)-strtotime($request->tgl_mulai))/60/60/40);
                $mp->status_id = 1;
                $mp->project_detail = $request->info_detail_project;
                $mp->budget_amount = str_replace('.','',$request->budget_amount);
                $mp->outstanding = str_replace('.','',$request->budget_amount);
                // sementara pantek dulu karna blm ada login
                $mp->user_crt_id = Auth::user()->id;
                $mp->company_id = Auth::user()->company_id;

                if($request->hasFile('logo_project')){
                    $destination_path = public_path('gallery');
                    $files = $request->file('logo_project');
                    $urlDoc = SpaceController::uploadFile($files, 'logo', []);


                    // $filename = 'Logo_'.date('Ymd')."_".date('His').'.'.$files->getClientOriginalExtension();
                    // $upload_success = $files->move($destination_path, $filename);
                    $mp->logo_url = $urlDoc;
                }

                $team_code = $getTC->max_number + 1;
                if(strlen($team_code) <= $min) {
                    $addZero = str_pad($team_code,5,"0",STR_PAD_LEFT);
                } else {
                    $batas = strlen($team_code);
                    $addZero = str_pad($team_code,$batas,"0",STR_PAD_LEFT);
                }

                if($request->row_anggota > 0) {
                    // $idTeam = $getTCId->max_id;
                    for ($i=1; $i <= $request->row_anggota ; $i++) {
                        # code...
                        // $arrayTeam[$i]['id'] = $idTeam+$i;
                        $arrayTeam[$i]['member_id'] = $request->input('personil_id_'.$i);
                        $arrayTeam[$i]['team_role_id'] = $request->input('team_role_id_'.$i);
                        $arrayTeam[$i]['team_code'] = 'TM0'.Auth::user()->company_id.$addZero;
                        $arrayTeam[$i]['is_active'] = true;
                        $arrayTeam[$i]['created_at'] = date('Y-m-d H:i:s');
                        // sementara pantek dulu karna blm ada login
                        $arrayTeam[$i]['user_crt_id'] = Auth::user()->id;
                        $arrayTeam[$i]['company_id'] = Auth::user()->company_id;
                    }
                }
                $mt::insert($arrayTeam);
                $numberOfCode = str_pad($get->max_id + 1,5,"0",STR_PAD_LEFT);
                $codeProject = 'PRJ'.$numberOfCode.'TM0'.Auth::user()->company_id.$addZero;
                $mp->project_code = $codeProject;
                $mp->team_code = 'TM0'.Auth::user()->company_id.$addZero;
                $mp->save();

                // $mbr->id = $getMbrId->max_id + 1;
                $mbr->project_id = $mp->id;
                $mbr->budget_type = 1;
                $mbr->amount_tx = str_replace('.','',$request->budget_amount);
                $mbr->outstanding = str_replace('.','',$request->budget_amount);
                $mbr->tx_date = date('Y-m-d');
                $mbr->tx_note = 'Budgeting Awal Project';
                $mbr->created_at = Carbon::now();
                $mbr->user_crt_id = Auth::user()->id;
                $mbr->company_id = Auth::user()->company_id;
                $mbr->save();
            } else {
                $mp = ProjectsModel::findOrFail($id);
                if($request->hasFile('logo_project')){
                    $destination_path = public_path('gallery');
                    // $image_path = public_path($mp->logo_url);
                    // if(File::exists($image_path)) {
                    //     File::delete($image_path);
                    // }

                    SpaceController::deleteFile($mp->logo_url);
                    $files = $request->file('logo_project');
                    $urlDoc = SpaceController::uploadFile($files, 'logo', []);

                    // $filename = 'Logo_'.date('Ymd')."_".date('His').'.'.$files->getClientOriginalExtension();
                    // $upload_success = $files->move($destination_path, $filename);

                    $mp->logo_url = $urlDoc;
                }
                $mp->project_name = $request->nama_project;
                $mp->unit_owner_id = $request->project_owner;
                $mp->start_date = date('Y-m-d', strtotime($request->tgl_mulai));
                $mp->due_date = date('Y-m-d', strtotime($request->tgl_selesai));
                $mp->days_overdue = round((strtotime($request->tgl_selesai)-strtotime($request->tgl_mulai))/60/60/40);
                $mp->project_detail = $request->info_detail_project;
                $mp->user_upd_id = Auth::user()->id;
                $mp->save();
                // $mp->company_id = Auth::user()->company_id;
            }

            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Success Add Project'
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_store_member(Request $request) {
        DB::beginTransaction();
        try {
            //code...
            // $get = collect(DB::select("SELECT max(id::int) as max_id FROM master_team"))->first();
            $mt = new TeamModel();
            // $mt->id = $get->max_id+1;
            $mt->member_id = $request->input('pilih_personil');
            $mt->team_role_id = $request->input('pilih_team_role');
            $mt->team_code = $request->input('team_code');
            $mt->is_active = true;
            $mt->created_at = date('Y-m-d H:i:s');
            // sementara pantek dulu karna blm ada login
            $mt->user_crt_id = Auth::user()->id;
            $mt->company_id = Auth::user()->company_id;
            $mt->save();
            $dataMember = DB::select("select mu.full_name, mu.image_url from master_user mu
                join master_team mt on mt.member_id = mu.id
                where mt.team_code = ? order by mt.id",[$request->input('team_code')]);
            DB::commit();
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil Tambah Member',
                'data' => $dataMember,
            ]);
        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }

    public function act_get_projects(Request $request, $type)
    {
        switch ($type) {
            case 'get_member_not_in_project':
                $data = DB::select("select mu.id, mu.full_name from master_user mu
                where mu.id not in (select member_id from master_team where team_code = ?) and mu.role_id not in (6) and mu.company_id = ?",[$request->input('team_code'), Auth::user()->company_id]);
                # code...
                break;
            case 'get_team_role_not_in_project':
                $data = DB::select("select rtr.* from ref_team_role rtr
                where rtr.id not in (select team_role_id from master_team where team_role_id <> 3 and team_code = ?) and rtr.company_id = ?",[$request->input('team_code'), Auth::user()->company_id]);
                # code...
                break;
            case 'data_task_member':
                $data = DB::select("select mpt.id, mpt.project_id, mpt.task_name, mpt.task_code, mpt.due_date,
                    mp.project_name, rs.definition as status_task
                from master_project_task mpt
                left join master_project mp on mp.id = mpt.project_id
                left join ref_status rs on rs.id = mpt.status_id and rs.company_id = mpt.company_id
                where mpt.project_id = ? and pic_id = ?",[$request->id_project, $request->member_id]);
                break;
            case 'get_status':
                $data = DB::select("select * from ref_status where company_id = ? and is_active=true and id not in (4)",[Auth::user()->company_id]);
                break;
            case 'get_status_project':
                $data = DB::select("select * from ref_status where company_id = ? and is_active=true",[Auth::user()->company_id]);
                break;
            case 'get_critical_level':
                $data = DB::select("select * from ref_critical_level where company_id = ? and is_active=true",[Auth::user()->company_id]);
                break;
            case 'get_data_task':
                $data = DB::select("select * from master_project_task where id = ? ",[$request->get('id')]);
                break;
            case 'data_kanban_task':
                $data = [];
                $status = DB::select("select * from ref_status where company_id = ? and is_active = true order by id",[Auth::user()->company_id]);
                $color = ['info', 'warning', 'success', 'danger'];
                foreach ($status as $key => $value) {
                    # code...
                    $data[$key]['id'] = '_'.$value->id;
                    $data[$key]['title'] = $value->definition;
                    $data[$key]['class'] = 'light-'.$color[$key];
                    // $data[$key]['dragTo'] = [$value->id + 1];
                    $data[$key]['item'] = [];
                    if($request->input('member_id')) {
                        $item = DB::select("select mpt.*, rcl.definition as critical_name, mu.full_name from master_project_task mpt
                        left join ref_critical_level rcl on rcl.id = mpt.critical_level_id and rcl.company_id = mpt.company_id
                        left join master_user mu on mu.id = mpt.pic_id where pic_id = ? and status_id = ? and project_id = ? order by mpt.id",[$request->input('member_id'), $value->id, $request->input('id')]);
                    } else {
                        $item = DB::select("select mpt.*, rcl.definition as critical_name, mu.full_name from master_project_task mpt
                        left join ref_critical_level rcl on rcl.id = mpt.critical_level_id and rcl.company_id = mpt.company_id
                        left join master_user mu on mu.id = mpt.pic_id where project_id = ? and status_id = ?",[$request->input('id'), $value->id]);
                    }
                    $colorTask = ['info', 'warning', 'success', 'danger'];
                    foreach ($item as $k => $itemVal) {
                        # code...
                        $data[$key]['item'][$k]['id'] = '_'.$itemVal->id;
                        $data[$key]['item'][$k]['statusid'] = $value->id;
                        $data[$key]['item'][$k]['title'] =  $itemVal->task_name.' <br> Code: ('.$itemVal->task_code.') <br> Level: '.$itemVal->critical_name.' <br><br> <u>'.$itemVal->full_name.'</u>';
                        $data[$key]['item'][$k]['class'] = $colorTask[$value->id-1];
                    }
                }
                break;
            case 'get_data_detail_task':
                $data = [];
                $data['task'] = collect(DB::select("select mpt.*, rs.definition as status, rcl.definition as critical_level, mu.phone_number from master_project_task mpt
                left join ref_status rs on rs.id = mpt.status_id and rs.company_id = mpt.company_id
                left join ref_critical_level rcl on rcl.id = mpt.critical_level_id and rcl.company_id = mpt.company_id
                left join master_user mu on mu.id = mpt.pic_id
                where mpt.id = ? ",[$request->get('id')]))->first();
                $data['subTask'] = DB::select("select mps.*, rs.definition as status from master_project_subtask mps
                left join ref_status rs on rs.id = mps.status_id and rs.company_id = mps.company_id
                where task_id = ?",[$request->get('id')]);
                $data['commentTask'] = DB::select("select mtc.comment_note, mu.full_name, mtc.user_crt_id, mu.image_url , mtc.created_at
                from master_task_comment mtc
                left join master_user mu on mu.id = mtc.user_crt_id where task_id = ? and project_id = ?
                order by mtc.created_at",[$request->get('id'),$request->get('id_project')]);
                break;
            case 'data_sub_task':
                $data = DB::select("select mps.*, rs.definition as status from master_project_subtask mps
                left join ref_status rs on rs.id = mps.status_id and rs.company_id = mps.company_id
                where task_id = ? order by mps.id",[$request->get('id')]);
                break;
            case 'data_comment_task':
                $data = DB::select("select mtc.comment_note, mu.full_name, mtc.user_crt_id, mu.image_url , mtc.created_at
                from master_task_comment mtc
                left join master_user mu on mu.id = mtc.user_crt_id
                where task_id = ? and project_id = ?
                order by mtc.created_at",[$request->get('task_id'),$request->get('id_project')]);
                break;
            case 'data_project_comment':
                $data = DB::select("select mtc.comment_note, mu.full_name, mtc.user_crt_id, mu.image_url , mtc.created_at
                from master_project_comment mtc
                left join master_user mu on mu.id = mtc.user_crt_id where project_id = ?
                order by mtc.created_at",[$request->get('id')]);
                break;
            case 'get_member_project':
                $data = DB::select("select mt.member_id, mu.full_name, rtr.definition from master_team mt
                left join master_project mp on mp.team_code = mt.team_code
                left join master_user mu on mu.id = mt.member_id and mu.company_id = mt.company_id
                left join ref_team_role rtr on rtr.id = mt.team_role_id and mp.company_id = rtr.company_id
                where  mp.id = ?
                order by mt.team_role_id",[$request->get('id')]);
                break;
            case 'get_model_task':
                $data = DB::select("select * from master_model where is_active = true and company_id = ?",[Auth::user()->company_id]);
                break;
            case 'get_model_task_detail':
                $data = DB::select("select rmt.task_id, rt.task_name from rel_model_task rmt
                left join ref_task rt on rt.id = rmt.task_id
                where rmt.model_id = ?",[$request->get('id')]);
                break;
            case 'detail_meeting':
                $data['meeting'] = collect(DB::select("select * from master_meeting where id = ? ", [$request->get('id')]))->first();
                $data['meetingParticipant'] = DB::select("select mmp.*, mu.full_name from master_meeting_participant mmp join master_user mu on mu.id = mmp.participant_id where mmp.meeting_id = ?", [$request->get('id')]);
                break;
            case 'get_det_inbox':
                if($request->type=='meetings') {
                    $data = collect(DB::select("select mmp.id,mmp.meeting_id,mmp.is_read,mu.full_name,mmp.created_at as send_date,mm.inv_content,mm.tittle,mp.project_name,mu.image_url
                    from master_meeting_participant mmp
                    join master_meeting mm on mm.id = mmp.meeting_id
                    left join master_user mu on mu.id = mm.user_crt_id
                    join master_project mp on mp.id = mm.project_id
                    where mmp.id = ? order by mm.created_at desc",[$request->id]))->first();
                } else {
                    $data = collect(DB::select("select mti.id, mti.is_read, mti.tittle, mp.project_name,
                        mti.description as inv_content, mti.created_at as send_date, mu.image_url, mu.full_name
                        from master_task_inbox mti
                        left join master_project mp on mp.id = mti.project_id
                        left join master_user mu on mu.id = mti.user_crt_id
                        where mti.id = ?
                        order by mti.created_at desc",[$request->id]))->first();
                }
                break;
            case 'get_data_project':
                $data = DB::table('master_project')->where('id', $request->id)->first();
                break;
            case 'get_project':
                $data = DB::table('master_project')->where('client_id', $request->client)->get();
                break;
        }


        return response()->json(['data' => $data]);
    }

    public function dataTable(Request $request) {
        $type = $request->get('type');
        switch ($type) {
            case 'detailProjects':
                $whereTri = '';
                $array = [Auth::user()->company_id, $request->get('id_project')];
                if($request->get('team_role_id')) {
                    if($request->get('team_role_id')!=1 && $request->get('team_role_id')!=2) {
                        $whereTri = 'and mt.member_id = ?';
                        $array = [Auth::user()->company_id, $request->get('id_project'), Auth::user()->id];
                    }
                }
                $data = DB::select("select
                    (select count(id) from master_project_task
                        where pic_id = mt.member_id and project_id = mp.id and status_id not in (4)) as jml_task,
                    (select count(id) from master_project_task
                        where status_id = 3 and pic_id = mt.member_id and project_id = mp.id) as task_done,
                    (select count(id) from master_project_task
                        where status_id in (1,2) and pic_id = mt.member_id and project_id = mp.id) as task_undone,
                    mu.full_name, rtr.definition as team_role_name, mu.image_url, mp.id as id_project, mt.member_id, mt.team_code from master_team mt
                join master_project mp on mp.team_code = mt.team_code
                left join master_user mu on mu.id = mt.member_id
                left join ref_team_role rtr on rtr.id = mt.team_role_id and rtr.company_id = ?
                where mp.id = ? ".$whereTri."
                order by mt.team_role_id", $array);
                # code...
            break;
            case 'data_task_member':
                $data = DB::select("select mpt.id, mpt.project_id, mpt.task_name, mpt.task_code, mpt.due_date,
                    mp.project_name, rs.definition as status_task, mp.status_id as status_id_project
                from master_project_task mpt
                left join master_project mp on mp.id = mpt.project_id
                left join ref_status rs on rs.id = mpt.status_id and rs.company_id = mpt.company_id
                where mpt.project_id = ? and mpt.pic_id = ? and mpt.status_id not in (4)
                order by mpt.id",[$request->id_project, $request->member_id]);
            break;
            case 'budgeting_project':
                $data = DB::select("select
                    mbr.id,
                    mbr.tx_date,
                    rbt.definition as budget_name,
                    rbt.tx_type,
                    mbr.tx_note,
                    mbr.amount_tx,
                    mbr.outstanding,
                    mbr.budget_type
                from
                    master_budget_real mbr
                join master_project mp on
                    mp.id = mbr.project_id
                left join ref_budget_type rbt on
                    rbt.id = mbr.budget_type
                    and mbr.company_id = rbt.company_id
                    where mbr.project_id = ?",[$request->get('amp;id_project')]);
            break;
            case 'mng_meeting':
                $data = DB::select("select * from master_meeting where project_id = ?",[$request->get('amp;id_project')]);
            break;
            case 'laporan_project':
                $model = $request->get('model');
                if(Auth::user()->role_id==1) {
                    if($model=='new') {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                            from master_project_task mpt
                            join master_project mp on mp.id = mpt.project_id
                            left join master_user mu on mu.id = mpt.user_imp_id
                            left join ref_status rs on rs.id = mpt.status_id
                            left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                            where mpt.user_crt_id = :id and mpt.status_id = 7
                            order by created_at desc", ['id' => Auth::user()->id]);
                    } else if($model=='op') {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                            from master_project_task mpt
                            join master_project mp on mp.id = mpt.project_id
                            left join master_user mu on mu.id = mpt.user_imp_id
                            left join ref_status rs on rs.id = mpt.status_id
                            left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                            where mpt.user_crt_id = :id and mpt.status_id in (1,2,3,4)
                            order by created_at desc", ['id' => Auth::user()->id]);
                    } else if($model=='cancel') {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                            from master_project_task mpt
                            join master_project mp on mp.id = mpt.project_id
                            left join master_user mu on mu.id = mpt.user_imp_id
                            left join ref_status rs on rs.id = mpt.status_id
                            left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                            where mpt.user_crt_id = :id and mpt.status_id in (6, 8)
                            order by created_at desc", ['id' => Auth::user()->id]);
                    } else if($model=='overdue') {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                            from master_project_task mpt
                            join master_project mp on mp.id = mpt.project_id
                            left join master_user mu on mu.id = mpt.user_imp_id
                            left join ref_status rs on rs.id = mpt.status_id
                            left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                            where mpt.user_crt_id = :id and mpt.status_id not in (5,6,7,8) and (DATE_PART('day', mpt.date_deadline::timestamp - CURRENT_DATE::timestamp)) >=0
                            order by created_at desc", ['id' => Auth::user()->id]);
                    } else if($model=='done') {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                            from master_project_task mpt
                            join master_project mp on mp.id = mpt.project_id
                            left join master_user mu on mu.id = mpt.user_imp_id
                            left join ref_status rs on rs.id = mpt.status_id
                            left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                            where mpt.user_crt_id = :id and mpt.status_id = 5 and mpt.status_approve = true
                            order by created_at desc", ['id' => Auth::user()->id]);
                    } else {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                            from master_project_task mpt
                            join master_project mp on mp.id = mpt.project_id
                            left join master_user mu on mu.id = mpt.user_imp_id
                            left join ref_status rs on rs.id = mpt.status_id
                            left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                            where mpt.user_crt_id = :id
                            order by created_at desc", ['id' => Auth::user()->id]);
                    }
                } else {
                    if($model=='new') {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                        from master_project_task mpt
                        join master_project mp on mp.id = mpt.project_id
                        left join master_user mu on mu.id = mpt.user_imp_id
                        left join ref_status rs on rs.id = mpt.status_id
                        left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                        where mpt.status_id = 7
                        order by created_at desc");
                    } else if($model=='op') {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                        from master_project_task mpt
                        join master_project mp on mp.id = mpt.project_id
                        left join master_user mu on mu.id = mpt.user_imp_id
                        left join ref_status rs on rs.id = mpt.status_id
                        left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                        where mpt.status_id in (1,2,3,4)
                        order by created_at desc");
                    } else if($model=='cancel') {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                        from master_project_task mpt
                        join master_project mp on mp.id = mpt.project_id
                        left join master_user mu on mu.id = mpt.user_imp_id
                        left join ref_status rs on rs.id = mpt.status_id
                        left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                        where mpt.status_id in (6, 8)
                        order by created_at desc");
                    } else if($model=='overdue') {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                        from master_project_task mpt
                        join master_project mp on mp.id = mpt.project_id
                        left join master_user mu on mu.id = mpt.user_imp_id
                        left join ref_status rs on rs.id = mpt.status_id
                        left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                        where mpt.status_id not in (5,6,7,8) and (DATE_PART('day', mpt.date_deadline::timestamp - CURRENT_DATE::timestamp)) >=0
                        order by created_at desc");
                    } else if($model=='done') {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                        from master_project_task mpt
                        join master_project mp on mp.id = mpt.project_id
                        left join master_user mu on mu.id = mpt.user_imp_id
                        left join ref_status rs on rs.id = mpt.status_id
                        left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                        where mpt.status_id = 5 and mpt.status_approve = true
                        order by created_at desc");
                    } else {
                        $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name as implementor
                        from master_project_task mpt
                        join master_project mp on mp.id = mpt.project_id
                        left join master_user mu on mu.id = mpt.user_imp_id
                        left join ref_status rs on rs.id = mpt.status_id
                        left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                        order by created_at desc");
                    }
                }
            break;
            case 'detail_helpdesk':
                $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name
                    from master_project_task mpt
                    join master_project mp on mp.id = mpt.project_id
                    left join ref_status rs on rs.id = mpt.status_id
                    left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                    where mpt.client_id = :id and mpt.status_id not in (6,7,8) and mpt.send_approve = false and mpt.status_approve = false
                    order by created_at desc", ['id' => $request->get('id')]);
            break;
            case 'detail_approve_helpdesk':
                $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name, mu.full_name
                    from master_project_task mpt
                    join master_project mp on mp.id = mpt.project_id
                    left join ref_status rs on rs.id = mpt.status_id
                    left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                    left join master_user mu on mu.id = mpt.user_imp_id
                    where mpt.client_id = :id and mpt.status_id not in (6,7,8) and mpt.send_approve = true and mpt.status_approve = false
                    order by created_at desc", ['id' => $request->get('id')]);
            break;
            case 'approve_css':
                $data = DB::select("select mpt.*, rs.definition as status, rcl.definition as lvlurgent, mp.project_name,
                    concat(mu.full_name,' - ',rd.definition) as name_divisi
                    from master_project_task mpt
                    join master_project mp on mp.id = mpt.project_id
                    left join ref_status rs on rs.id = mpt.status_id
                    left join ref_critical_level rcl on rcl.id = mpt.critical_level_id
                    left join master_user mu on mu.id = mpt.user_crt_id
                    left join ref_division rd on rd.id = mu.division_id
                    where mpt.client_id = :id and mpt.status_id = 7
                    order by created_at desc", ['id' => Auth::user()->client_id]);
            break;
        }
        return DataTables::of($data)
            ->addIndexColumn()
            ->addColumn('member_name',function($data) use ($type) {
                if($type=='detailProjects') {
                    return '
                        <div class="d-flex align-items-center">
                            <div class="symbol symbol-50 flex-shrink-0 mr-4">
                                <div class="symbol-label" style="background-image: url(\''.$data->image_url.'\'); background-size: cover; background-repeat: no-repeat; background-position: center center;"></div>
                            </div>
                            <div>
                                <a href="#" class="text-dark-75 font-weight-bolder text-hover-primary mb-1 font-size-lg">'.$data->full_name.'</a>
                                <span class="text-muted font-weight-bold d-block">'.$data->team_role_name.'</span>
                            </div>
                        </div>
                    ';
                }
            })
            /* ->editColumn('jml_task', function($data) use ($type) {
                if($type=='detailProjects' || $type=='laporan_project') {
                    return '<span class="text-white font-weight-bolder font-size-lg label label-primary label-pill label-inline label-lg">'.$data->jml_task.'</span>';
                }
            }) */
            ->editColumn('task_done', function($data) use ($type) {
                if($type=='detailProjects') {
                    return '<span class="text-white font-weight-bolder font-size-lg label label-success label-pill label-inline label-lg">'.$data->task_done.' Tasks</span>';
                }
            })
            /* ->editColumn('jml_task_done', function($data) use ($type) {
                if($type=='laporan_project') {
                    return '<span class="text-white font-weight-bolder font-size-lg label label-success label-pill label-inline label-lg">'.$data->jml_task_done.'</span>';
                }
            })
            ->editColumn('jml_task_on_progress', function($data) use ($type) {
                if($type=='laporan_project') {
                    return '<span class="text-white font-weight-bolder font-size-lg label label-warning label-pill label-inline label-lg">'.$data->jml_task_on_progress.'</span>';
                }
            })
            ->editColumn('jml_task_assign', function($data) use ($type) {
                if($type=='laporan_project') {
                    return '<span class="text-white font-weight-bolder font-size-lg label label-info label-pill label-inline label-lg">'.$data->jml_task_assign.'</span>';
                }
            }) */
            ->editColumn('task_undone', function($data) use ($type) {
                if($type=='detailProjects') {
                    return '<span class="text-white font-weight-bolder font-size-lg label label-danger label-pill label-inline label-lg">'.$data->task_undone.' Tasks</span>';
                }
            })
            ->editColumn('status_task', function($data) use ($type) {
                if($type=='data_task_member') {
                    return '<span class="text-white font-weight-bolder font-size-lg label label-info label-pill label-inline label-lg">'.$data->status_task.'</span>';
                }
            })
            /* ->editColumn('due_date', function($data) use ($type) {
                if($type=='data_task_member' || $type=='laporan_project') {
                    return date('d F Y',strtotime($data->due_date));
                }
            })
            ->editColumn('days_left', function($data) use ($type) {
                if($type=='laporan_project') {
                    if($data->days_left<0) {
                        return '<span class="text-white font-weight-bolder font-size-lg label label-danger label-pill label-inline label-lg">'.$data->days_left.'</span>';
                    } else {
                        return '<span class="text-white font-weight-bolder font-size-lg label label-primary label-pill label-inline label-lg">'.$data->days_left.'</span>';
                    }
                }
            }) */
            ->editColumn('start_date', function($data) use ($type) {
                if($type=='mng_meeting') {
                    return date('d F Y',strtotime($data->start_date));
                }
            })
            ->editColumn('start_time', function($data) use ($type) {
                if($type=='mng_meeting') {
                    return date('H:i',strtotime($data->start_time));
                }
            })
            ->editColumn('end_time', function($data) use ($type) {
                if($type=='mng_meeting') {
                    return date('H:i',strtotime($data->end_time));
                }
            })
            ->editColumn('is_done', function($data) use ($type) {
                if($type=='mng_meeting') {
                    return $data->is_done?'Selesai':'Belum Selesai';
                }
            })
            ->editColumn('amount_tx', function($data) use ($type) {
                if($type=='budgeting_project') {
                    return number_format($data->amount_tx,0,',','.');
                }
            })
            ->editColumn('outstanding', function($data) use ($type) {
                if($type=='budgeting_project') {
                    return number_format($data->outstanding,0,',','.');
                }
            })
            ->editColumn('created_at', function($data) use ($type) {
                if($type=='laporan_project' || $type=='detail_helpdesk' || $type=='detail_approve_helpdesk' || $type=='approve_css') {
                    return '<span class="text-white font-weight-bolder font-size-lg label label-info label-pill label-inline label-md">'.date('d M Y H:i', strtotime($data->created_at)).'</span>';
                }
            })
            ->editColumn('implementor', function($data) use ($type) {
                if($type=='laporan_project') {
                    if($data->implementor) {
                        return '<span class="text-white font-weight-bolder font-size-lg label label-info label-pill label-inline label-md">'.$data->implementor.'</span>';
                    } else {
                        return '<span class="text-white font-weight-bolder font-size-lg label label-danger label-pill label-inline label-md">-</span>';
                    }
                }
            })
            ->editColumn('date_deadline', function($data) use ($type) {
                if($type=='laporan_project') {
                    if($data->date_deadline) {
                        return '<span class="text-white font-weight-bolder font-size-lg label label-info label-pill label-inline label-md">'.date('d M Y', strtotime($data->date_deadline)).'</span>';
                    } else {
                        return '<span class="text-white font-weight-bolder font-size-lg label label-danger label-pill label-inline label-md">-</span>';
                    }
                }
            })
            ->editColumn('status', function($data) use ($type) {
                if($type=='laporan_project' || $type=='detail_helpdesk' || $type=='detail_approve_helpdesk' || $type=='approve_css') {
                    if(Auth::user()->role_id==2) {
                        if($data->status_id==8) {
                            return '<span class="text-white font-weight-bolder font-size-sm label label-danger label-pill label-inline label-md">'.$data->status.'</span>';
                        } else {
                            return '<span class="text-white font-weight-bolder font-size-sm label label-info label-pill label-inline label-md">'.$data->status.'</span>';
                        }
                    } else {
                        if($data->send_approve==false && $data->user_approve_id==null) {
                            if($data->status_id==8) {
                                return '<span class="text-white font-weight-bolder font-size-sm label label-danger label-pill label-inline label-md">'.$data->status.'</span>';
                            } else {
                                return '<span class="text-white font-weight-bolder font-size-sm label label-info label-pill label-inline label-md">'.$data->status.'</span>';
                            }
                        } else if($data->send_approve==true && $data->user_approve_id==null) {
                            return '<span class="text-white font-weight-bolder font-size-sm label label-info label-pill label-inline label-md">Approval</span>';
                        } else {
                            if($data->status_id==8) {
                                return '<span class="text-white font-weight-bolder font-size-sm label label-danger label-pill label-inline label-md">'.$data->status.'</span>';
                            } else {
                                return '<span class="text-white font-weight-bolder font-size-sm label label-info label-pill label-inline label-md">'.$data->status.'</span>';
                            }
                        }
                    }
                }
            })
            ->addColumn('action', function ($data) use ($type, $request) {
                $btn = '';
                $btn.='<li class="navi-item">
                    <a onclick="dokumen(`'.$data->id.'`,\''.$data->task_code.'\',\''.$data->task_name.'\')" href="#" class="navi-link">
                        <span class="navi-icon"><i class="la la-arrows-alt text-danger"></i></span>
                        <span class="navi-text">Lihat Detail</span>
                    </a>
                </li>';
                if($type=='detailProjects') {
                    if($request->get('team_role_id')) {
                        return '<a href="#" class="btn btn-sm btn-success btn-shadow font-weight-bold mr-2" onclick="loadContent(`kanbanProject/'.$data->id_project.'?member_id='.$data->member_id.'`, this, event)">Lihat Rincian Task</a>';
                    } else {
                        return '<a href="#" class="btn btn-sm btn-success btn-shadow font-weight-bold mr-2" onclick="rincianTask(`'.$data->id_project.'`,`'.$data->team_code.'`,`'.$data->full_name.'`,`'.$data->member_id.'`)">Lihat Rincian Task</a>';
                    }
                } else if($type=='budgeting_project') {
                    $last = collect(DB::select("select mbr.id from master_budget_real mbr where mbr.project_id = ?",[$request->get('amp;id_project')]))->last()->id;
                    if($data->id==$last) {
                        return '<a href="#" class="btn btn-sm btn-success btn-shadow font-weight-bold mr-2" onclick="reversal(`'.$data->id.'`,`'.$data->budget_type.'`)">Reversal</a>';
                        // return '';
                    } else {
                        return '';
                    }
                } else if($type=='laporan_project') {

                    $crtDate = date("d-m-Y", strtotime($data->created_at));
                    $monthDate = date("m-d-Y", strtotime($data->created_at));
                    if($data->status_id==5) {
                        $btn.='<li class="navi-item">
                                    <a onclick="dokumen(`'.$data->id.'`,\''.$data->task_code.'\',\''.$data->task_name.'\')" href="#" class="navi-link">
                                        <span class="navi-icon"><i class="la la-arrows-alt text-danger"></i></span>
                                        <span class="navi-text">Lihat Hasil</span>
                                    </a>
                                </li>';
                        $btn.='<li class="navi-item">
                                    <a onclick="backDate(`'.$data->id.'`,\''.$crtDate.'\',\''.$monthDate.'\')" href="#" class="navi-link">
                                        <span class="navi-icon"><i class="la la-calendar-alt text-danger"></i></span>
                                        <span class="navi-text">Back Date</span>
                                    </a>
                                </li>';
                    }

                    /* if($data->status_id==1) {
                        $btn.='<li class="navi-item">
                                <a onclick="batalTask('.$data->id.')" href="#" class="navi-link">
                                    <span class="navi-icon"><i class="la la-times-circle text-danger"></i></span>
                                    <span class="navi-text">Batal</span>
                                </a>
                            </li>';
                        // return '<a href="#" class="btn btn-sm btn-danger btn-shadow font-weight-bold" onclick="batalTask('.$data->id.')">Batal</a>';
                    } */

                    if(Auth::user()->role_id==2 && $data->status_id == 4) {
                        $btn.='<li class="navi-item">
                                    <a onclick="approveTaskSpvHd('.$data->id.')" href="#" class="navi-link">
                                        <span class="navi-icon"><i class="la la-check-circle text-success"></i></span>
                                        <span class="navi-text">Approve</span>
                                    </a>
                                </li>
                                <li class="navi-item">
                                    <a onclick="rejectTaskSpvHd('.$data->id.')" href="#" class="navi-link">
                                        <span class="navi-icon"><i class="la la-times-circle text-danger"></i></span>
                                        <span class="navi-text">Reject</span>
                                    </a>
                                </li>';
                        // $btn.='<li class="navi-item">
                        //         <a onclick="dokumenSpvHd(`'.$data->id.'`,\''.$data->task_code.'\',\''.$data->task_name.'\')" href="#" class="navi-link">
                        //             <span class="navi-icon"><i class="la la-arrows-alt text-danger"></i></span>
                        //             <span class="navi-text">Lihat Dokumen</span>
                        //         </a>
                        //     </li>';
                    }
                    // ,\''.trim(strip_tags(str_replace( array("\r\n","\r","\n",'  '), array('\r\n','\r','\n',' '), $data->task_note))).'\'
                    if(Auth::user()->role_id==3 && ($data->status_id ==1 || $data->status_id ==2 || $data->status_id == 3)) {
                        $btn.='<li class="navi-item">
                                    <a onclick="prosesTaskHd('.$data->id.',\''.$data->task_code.'\',\''.$data->task_name.'\','.$data->status_id.','.$data->critical_level_id.',\''.$data->notes_imp.'\',\''.$data->date_deadline.'\',`'.trim(strip_tags(str_replace( array('\r\n','\r','\n','  '), array('\r\n','\r','\n',' '), $data->task_note))).'`)" href="#" class="navi-link">
                                        <span class="navi-icon"><i class="la la-edit text-info"></i></span>
                                        <span class="navi-text">Proses</span>
                                    </a>
                                </li>';
                        // $btn.='<li class="navi-item"> 
                        //         <a onclick="dokumenHd(`'.$data->id.'`,\''.$data->task_code.'\',\''.$data->task_name.'\')" href="#" class="navi-link">
                        //             <span class="navi-icon"><i class="la la-arrows-alt text-danger"></i></span>
                        //             <span class="navi-text">Lihat Dokumen</span>
                        //         </a>
                        //     </li>';
                        if($data->status_approve==false && $data->note_approval) {
                            $btn.='<li class="navi-item">
                                    <a onclick="lihat_catatan_approvalHd(\''.addslashes($data->note_approval).'\')" href="#" class="navi-link">
                                        <span class="navi-icon"><i class="la la-arrows-alt text-info"></i></span>
                                        <span class="navi-text">Lihat Note Approval</span>
                                    </a>
                                </li>';
                        }
                    }

                    if(Auth::user()->role_id==4 && $data->status_id == 7) {
                        $btn.='<li class="navi-item">
                                    <a onclick="approveTaskSpvIt('.$data->id.')" href="#" class="navi-link">
                                        <span class="navi-icon"><i class="la la-check-circle text-success"></i></span>
                                        <span class="navi-text">Approve</span>
                                    </a>
                                </li>
                                <li class="navi-item">
                                    <a onclick="rejectTaskSpvIt('.$data->id.')" href="#" class="navi-link">
                                        <span class="navi-icon"><i class="la la-times-circle text-danger"></i></span>
                                        <span class="navi-text">Reject</span>
                                    </a>
                                </li>';
                        // $btn.='<li class="navi-item">
                        //         <a onclick="dokumenSpvIt(`'.$data->id.'`,\''.$data->task_code.'\',\''.$data->task_name.'\')" href="#" class="navi-link">
                        //             <span class="navi-icon"><i class="la la-arrows-alt text-danger"></i></span>
                        //             <span class="navi-text">Lihat Dokumen</span>
                        //         </a>
                        //     </li>';
                    }
                } else if($type=='detail_helpdesk') {
                    // return '<a href="#" class="btn btn-sm btn-info btn-shadow font-weight-bold" onclick="prosesTask('.$data->id.',\''.$data->task_code.'\',\''.$data->task_name.'\','.$data->status_id.','.$data->critical_level_id.')">Proses</a>';
                    $btn.='<li class="navi-item">
                                <a onclick="prosesTask('.$data->id.',\''.$data->task_code.'\',\''.$data->task_name.'\','.$data->status_id.','.$data->critical_level_id.')" href="#" class="navi-link">
                                    <span class="navi-icon"><i class="la la-edit text-info"></i></span>
                                    <span class="navi-text">Proses</span>
                                </a>
                            </li>';
                    // $btn.='<li class="navi-item">
                    //         <a onclick="dokumen(`'.$data->id.'`,\''.$data->task_code.'\',\''.$data->task_name.'\')" href="#" class="navi-link">
                    //             <span class="navi-icon"><i class="la la-arrows-alt text-danger"></i></span>
                    //             <span class="navi-text">Lihat Dokumen</span>
                    //         </a>
                    //     </li>';
                    if($data->status_approve==false && $data->note_approval) {
                        $btn.='<li class="navi-item">
                                <a onclick="lihat_catatan_approval(\''.addslashes($data->note_approval).'\')" href="#" class="navi-link">
                                    <span class="navi-icon"><i class="la la-arrows-alt text-info"></i></span>
                                    <span class="navi-text">Lihat Note Approval</span>
                                </a>
                            </li>';
                    }
                } else if($type=='detail_approve_helpdesk') {
                    $btn.='<li class="navi-item">
                                <a onclick="approveTask('.$data->id.')" href="#" class="navi-link">
                                    <span class="navi-icon"><i class="la la-check-circle text-success"></i></span>
                                    <span class="navi-text">Approve</span>
                                </a>
                            </li>
                            <li class="navi-item">
                                <a onclick="rejectTask('.$data->id.')" href="#" class="navi-link">
                                    <span class="navi-icon"><i class="la la-times-circle text-danger"></i></span>
                                    <span class="navi-text">Reject</span>
                                </a>
                            </li>';
                    // $btn.='<li class="navi-item">
                    //         <a onclick="dokumen(`'.$data->id.'`,\''.$data->task_code.'\',\''.$data->task_name.'\')" href="#" class="navi-link">
                    //             <span class="navi-icon"><i class="la la-arrows-alt text-danger"></i></span>
                    //             <span class="navi-text">Lihat Dokumen</span>
                    //         </a>
                    //     </li>';
                } else if($type=='approve_css') {
                    $btn.='<li class="navi-item">
                                <a onclick="approveTask('.$data->id.')" href="#" class="navi-link">
                                    <span class="navi-icon"><i class="la la-check-circle text-success"></i></span>
                                    <span class="navi-text">Approve</span>
                                </a>
                            </li>
                            <li class="navi-item">
                                <a onclick="rejectTask('.$data->id.')" href="#" class="navi-link">
                                    <span class="navi-icon"><i class="la la-times-circle text-danger"></i></span>
                                    <span class="navi-text">Reject</span>
                                </a>
                            </li>';
                    // $btn.='<li class="navi-item">
                    //         <a onclick="dokumen(`'.$data->id.'`,\''.$data->task_code.'\',\''.$data->task_name.'\')" href="#" class="navi-link">
                    //             <span class="navi-icon"><i class="la la-arrows-alt text-danger"></i></span>
                    //             <span class="navi-text">Lihat Dokumen</span>
                    //         </a>
                    //     </li>';
                } else {
                    if($type=='data_task_member') {
                        if($data->status_id_project!=3) {
                            $btn.='<li class="navi-item">
                                <a onclick="editRincian(`'.$data->id.'`)" href="#" class="navi-link">
                                    <span class="navi-icon"><i class="la la-edit text-info"></i></span>
                                    <span class="navi-text">Edit</span>
                                </a>
                            </li>';
                            $btn.='<li class="navi-item">
                                <a onclick="hapusRincian(`'.$data->id.'`,`'.$data->project_id.'`)" href="#" class="navi-link">
                                    <span class="navi-icon"><i class="la la-trash text-danger"></i></span>
                                    <span class="navi-text">Hapus</span>
                                </a>
                            </li>';
                        }
                    } else if($type=='mng_meeting') {
                        $btn.='<li class="navi-item">
                                <a onclick="detailMeeting(`'.$data->id.'`)" href="#" class="navi-link">
                                    <span class="navi-icon"><i class="la la-arrows-alt text-info"></i></span>
                                    <span class="navi-text">Detail</span>
                                </a>
                            </li>';
                        $btn.='<li class="navi-item">
                            <a onclick="editMeeting(`'.$data->id.'`)" href="#" class="navi-link">
                                <span class="navi-icon"><i class="la la-edit text-primary"></i></span>
                                <span class="navi-text">Update Hasil</span>
                            </a>
                        </li>';
                        $btn.='<li class="navi-item">
                            <a onclick="hapusMeeting(`'.$data->id.'`)" href="#" class="navi-link">
                                <span class="navi-icon"><i class="la la-trash text-danger"></i></span>
                                <span class="navi-text">Hapus</span>
                            </a>
                        </li>';
                    }
                    /* $btn .= ' <li class="navi-item">
                        <a onclick="kembalikan(`'.$data->id.'`,`'.$data->postal.'`)" href="#" class="navi-link">
                            <span class="navi-icon"><i class="la la-undo"></i></span>
                            <span class="navi-text">Kembalikan Laporan (Undo)</span>
                        </a>
                        </li>'; */
                }

                return '
                    <div class="dropdown dropdown-inline">
                        <a href="javascript:;" class="btn btn-sm btn-clean btn-icon mr-2" data-toggle="dropdown">
                            <span class="svg-icon svg-icon-md">
                                <svg width="24px" height="24px" viewBox="0 0 24 24" version="1.1">
                                    <g stroke="none" stroke-width="1" fill="none" fill-rule="evenodd">
                                        <rect x="0" y="0" width="24" height="24"/>
                                        <path d="M5,8.6862915 L5,5 L8.6862915,5 L11.5857864,2.10050506 L14.4852814,5 L19,5 L19,9.51471863 L21.4852814,12 L19,14.4852814 L19,19 L14.4852814,19 L11.5857864,21.8994949 L8.6862915,19 L5,19 L5,15.3137085 L1.6862915,12 L5,8.6862915 Z M12,15 C13.6568542,15 15,13.6568542 15,12 C15,10.3431458 13.6568542,9 12,9 C10.3431458,9 9,10.3431458 9,12 C9,13.6568542 10.3431458,15 12,15 Z" fill="#000000"/>
                                    </g>
                                </svg>
                            </span>
                        </a>
                        <div class="dropdown-menu dropdown-menu-sm dropdown-menu-right" style="z-index:9999;">
                            <ul class="navi flex-column navi-hover py-2">
                                <li class="navi-header font-weight-bolder text-uppercase font-size-xs text-primary pb-2">
                                    Choose an action:
                                </li>
                            '.$btn.'
                            </ul>
                        </div>
                    </div>
                    ';
            })
            ->rawColumns(['action','member_name','jml_task','task_done','task_undone','due_date','status_task','days_left','jml_task_done','jml_task_on_progress','jml_task_assign','created_at','status','date_deadline','implementor'])
        ->make(true);
    }

    public function cetakDok(Request $request) {
        if($request->get('type')=='cetakLaporan') {
            if(Auth::user()->role_id == 6) {
                $wherePercent='';
                if($request->get('percent') == 'krg50') {
                    $wherePercent = 'and round(coalesce((
                        ( (select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3) /
                        nullif((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float, 0) ) * 100
                    ), 0)) < 50';
                } else if($request->get('percent') == 'btwn5070') {
                    $wherePercent = 'and round(coalesce((
                        ( (select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3) /
                        nullif((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float, 0) ) * 100
                    ), 0)) between 50 and 70';
                } else if($request->get('percent') == 'lbh70') {
                    $wherePercent = 'and round(coalesce((
                        ( (select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3) /
                        nullif((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float, 0) ) * 100
                    ), 0)) > 70';
                }
                $response['response'] = DB::select("select
                        mp.id,
                        mp.project_code,
                        mp.project_name,
                        mp.team_code,
                        mp.logo_url,
                        (
                        select
                            count(id)
                        from
                            master_project_task mpt
                        where
                            mpt.project_id = mp.id
                            and status_id not in (4)) as jml_task,
                        (
                        select
                            count(id)
                        from
                            master_project_task mpt
                        where
                            mpt.project_id = mp.id
                            and status_id = 3) as jml_task_done,
                        (
                        select
                            count(id)
                        from
                            master_project_task mpt
                        where
                            mpt.project_id = mp.id
                            and status_id = 2) as jml_task_assign,
                        (
                        select
                            count(id)
                        from
                            master_project_task mpt
                        where
                            mpt.project_id = mp.id
                            and status_id = 1) as jml_task_on_progress,
                        round(coalesce((
                            ( (select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3) /
                            nullif((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float, 0) ) * 100
                        ), 0)) as progress_task,
                        mp.due_date,
                        (DATE_PART('day', concat(mp.due_date, ' 00:00:00')::timestamp - concat(CURRENT_DATE, ' 00:00:00')::timestamp)) as days_left
                    from
                        master_project mp
                    where mp.company_id = ? ".$wherePercent."
                    order by
                        (DATE_PART('day', concat(mp.due_date, ' 00:00:00')::timestamp - concat(CURRENT_DATE, ' 00:00:00')::timestamp)) desc",[Auth::user()->company_id]
                );
            } else {
                $wherePercent='';
                if($request->get('percent') == 'krg50') {
                    $wherePercent = 'and round(coalesce((
                        ( (select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3) /
                        nullif((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float, 0) ) * 100
                    ), 0)) < 50';
                } else if($request->get('percent') == 'btwn5070') {
                    $wherePercent = 'and round(coalesce((
                        ( (select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3) /
                        nullif((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float, 0) ) * 100
                    ), 0)) between 50 and 70';
                } else if($request->get('percent') == 'lbh70') {
                    $wherePercent = 'and round(coalesce((
                        ( (select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3) /
                        nullif((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float, 0) ) * 100
                    ), 0)) > 70';
                }
                $response['response'] = DB::select("select
                        mp.id,
                        mp.project_code,
                        mp.project_name,
                        mp.team_code,
                        (
                        select
                            count(id)
                        from
                            master_project_task mpt
                        where
                            mpt.project_id = mp.id
                            and status_id not in (4)) as jml_task,
                        (
                        select
                            count(id)
                        from
                            master_project_task mpt
                        where
                            mpt.project_id = mp.id
                            and status_id = 3) as jml_task_done,
                        (
                        select
                            count(id)
                        from
                            master_project_task mpt
                        where
                            mpt.project_id = mp.id
                            and status_id = 2) as jml_task_assign,
                        (
                        select
                            count(id)
                        from
                            master_project_task mpt
                        where
                            mpt.project_id = mp.id
                            and status_id = 1) as jml_task_on_progress,
                        round(coalesce((
                            ( (select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id = 3) /
                            nullif((select count(id) from master_project_task mpt where mpt.project_id = mp.id and status_id not in (4))::float, 0) ) * 100
                        ), 0)) as progress_task,
                        mp.due_date,
                        (DATE_PART('day', concat(mp.due_date, ' 00:00:00')::timestamp - concat(CURRENT_DATE, ' 00:00:00')::timestamp)) as days_left
                    from
                        master_project mp
                    join master_team mt on mt.team_code = mp.team_code
                    where mp.company_id = ? and mt.member_id = ? ".$wherePercent."
                    order by
                        (DATE_PART('day', concat(mp.due_date, ' 00:00:00')::timestamp - concat(CURRENT_DATE, ' 00:00:00')::timestamp)) desc",[Auth::user()->company_id, Auth::user()->id]
                );
            }
            $response['perusahaan'] = collect(DB::select("select company_name, logo_url from master_company where id = ?",[Auth::user()->company_id]))->first();
            $view = 'content.others.cetak_laporan';
            $kertas = 'portrait';
            $psize = 'a3';
            // return view($view, $response);
            $pdf = PDF::loadView($view,$response)->setPaper($psize, $kertas);
            return $pdf->stream('Laporan Project_'. date('dmY_His') . '.pdf', array("Attachment" => false));
            // return $pdf->stream();
        } elseif($request->get('type')=='cetakExcel') {
            $type = $request->type;
            $path = $request->path;

            $param = [
                'type' => $type,
                'path' => $path
            ];

            if ($path == null) {
                $path = 'All';
            }

            return Excel::download(new LaporanProjectExport($param), 'laporan_project'.'_'.$path.'_'.date('dmY').'.xlsx');
        }
    }

    public function kirimEmail($email, $data) {
        // set_time_limit(200);
        ini_set('max_execution_time', '300'); // 5 second
        $res = Mail::to($email)->send(new ReportMail($data));
        return $res;
        /* if(Mail::failures()) {
            return response()->json([
                'rc' => 500,
                'rm' => 'Gagal Kirim Email',
            ]);
        } else {
            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil Kirim Email',
            ]);
        } */
    }

    function back_date(Request $request) {
        DB::beginTransaction();
        try {
            $id = $request->id;
            $date = date("Y/m/d", strtotime($request->date)) .date(" H:i:s");

            $pt = ProjectTaskModel::find($id);
            $pt->created_at = $date;
            $pt->save();
            DB::commit();

            return response()->json([
                'rc' => 0,
                'rm' => 'Berhasil Ubah Tanggal',
            ]);

        } catch (\Throwable $th) {
            //throw $th;
            DB::rollBack();
            return response()->json([
                'rc' => 99,
                'rm' => $th->getMessage()
            ]);
        } catch (QueryException $e) {
            DB::rollback();
            return response()->json([
                'rc' => 500,
                'rm' => $e->getMessage()
            ]);
        }
    }
}