<?php namespace App\Http\Controllers;

    use Session;
	use Request;
	use DB;
	use Carbon;

    class SimpulBansosController extends Controller {
        public function getIndex() {
            $data = [];
            $data['app_name'] = "Simpul Bansos";
            
            $data['jumlah_warga_sungai_ulin'] = DB::table('warga_bjb')->where('nama_kel','6372041004')->count();
            
            $data['jumlah_penerima_bansos'] = DB::table('warga_bjb')->where('nama_kel','6372041004')->whereExists(function ($query) {
               $query->select(DB::raw(1))
                     ->from('history_bantuan')
                     ->whereRaw('history_bantuan.warga_bjb_id = warga_bjb.id');
           })->count();
           
           $data['jumlah_penerima_bansos_by_jk'] = DB::table('warga_bjb')->where('nama_kel','6372041004')->whereExists(function ($query) {
               $query->select(DB::raw(1))
                     ->from('history_bantuan')
                     ->whereRaw('history_bantuan.warga_bjb_id = warga_bjb.id');
           })->select(DB::raw('sum(case when jk="L" then 1 else 0 end) as laki,sum(case when jk="P" then 1 else 0 end) as perempuan'))->get();
            
            $data['penerima_bansos'] = DB::table('warga_bjb')->where('nama_kel','6372041004') ->whereExists(function ($query) {
               $query->select(DB::raw(1))
                     ->from('history_bantuan')
                     ->whereRaw('history_bantuan.warga_bjb_id = warga_bjb.id');
           })->select(DB::raw('warga_bjb.*, TIMESTAMPDIFF(YEAR, tgl_lhr, CURDATE()) AS umur'))->paginate(10);
           
            
            return view('simpul-bansos.examples.dashboard',$data);
        }
    }