API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 18th, 2020
196
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PHP 8.50 KB | None | 0 0
raw download clone embed print report
  1. $atts=pegawai::leftJoin('atts','atts.pegawai_id','=','pegawais.id')
  2.         ->leftJoin('jadwalkerjas','atts.jadwalkerja_id','=','jadwalkerjas.id')
  3.         ->leftJoin('instansis','pegawais.instansi_id','=','instansis.id')
  4.         ->select(
  5.                 'pegawais.id',
  6.                 'pegawais.nip',
  7.                 'pegawais.nama',
  8.                 DB::raw('DATE_FORMAT( tanggal_att, "%m-%Y" ) as periode'),
  9.                 DB::raw('
  10.                        concat(
  11.                                count(if(atts.jenisabsen_id!="9" && atts.jenisabsen_id != "11" && atts.jenisabsen_id!="13" && jadwalkerjas.sifat="WA",1,null))
  12.                                ," | ",
  13.                                count(if(atts.jenisabsen_id!="9" && atts.jenisabsen_id != "11" && atts.jenisabsen_id!="13" && jadwalkerjas.sifat="TWA",1,null))
  14.                        )
  15.                        as hari_kerja'),
  16.                 DB::raw('concat(
  17.                                count(if (atts.jenisabsen_id = "1" && atts.jam_keluar is not null  && jadwalkerjas.sifat="WA",1,null))
  18.                                ," | ",
  19.                                count(if (atts.jenisabsen_id = "1" && atts.jam_keluar is not null  && jadwalkerjas.sifat="TWA",1,null))
  20.                                )
  21.                                as hadir'),
  22.                 DB::raw('concat(
  23.                                count(if (atts.apel = "1" && jadwalkerjas.sifat="WA",1,null))
  24.                                ," | ",
  25.                                count(if (atts.apel = "1" && jadwalkerjas.sifat="TWA",1,null))
  26.                                ) as apelbulanan'),
  27.                 DB::raw('concat(
  28.                                count(if (atts.terlambat != "00:00:00" && jadwalkerjas.sifat="WA",1,null))
  29.                                ," | ",
  30.                                count(if (atts.terlambat != "00:00:00" && jadwalkerjas.sifat="TWA",1,null))
  31.                                )
  32.                        as terlambat'),
  33.                 DB::raw('concat(
  34.                                count(if ((atts.jenisabsen_id = "2" || (atts.jam_keluar is null && atts.jenisabsen_id="1")) && jadwalkerjas.sifat="WA",1,null))
  35.                                ," | ",
  36.                                count(if ((atts.jenisabsen_id = "2" || (atts.jam_keluar is null && atts.jenisabsen_id="1")) && jadwalkerjas.sifat="TWA",1,null))
  37.                                ) as tanpa_kabar'),
  38.                 DB::raw('concat(
  39.                                count(if (atts.jenisabsen_id = "3" && jadwalkerjas.sifat="WA",1,null))
  40.                                ," | ",
  41.                                count(if (atts.jenisabsen_id = "3" && jadwalkerjas.sifat="TWA",1,null))
  42.                                ) as ijin'),
  43.                 DB::raw('concat(
  44.                                count(if (atts.keteranganmasuk_id = "10" && jadwalkerjas.sifat="WA",1,null))
  45.                                ," | ",
  46.                                count(if (atts.keteranganmasuk_id = "10" && jadwalkerjas.sifat="TWA",1,null))
  47.                                ) as ijinterlambat'),
  48.                 DB::raw('concat(
  49.                                count(if (atts.jenisabsen_id = "5" && jadwalkerjas.sifat="WA",1,null))
  50.                                ," | ",
  51.                                count(if (atts.jenisabsen_id = "5" && jadwalkerjas.sifat="TWA",1,null))
  52.                                ) as sakit'),
  53.                 DB::raw('concat(
  54.                                count(if (atts.jenisabsen_id = "4" && jadwalkerjas.sifat="WA",1,null))
  55.                                ," | ",
  56.                                count(if (atts.jenisabsen_id = "4" && jadwalkerjas.sifat="TWA",1,null))
  57.                                ) as cuti'),
  58.                 DB::raw('concat(
  59.                                count(if (atts.jenisabsen_id = "7" && jadwalkerjas.sifat="WA",1,null))
  60.                                ," | ",
  61.                                count(if (atts.jenisabsen_id = "7" && jadwalkerjas.sifat="TWA",1,null))
  62.                                ) as tugas_luar'),
  63.                 DB::raw('concat(
  64.                                count(if (atts.jenisabsen_id = "6" && jadwalkerjas.sifat="WA",1,null))
  65.                                ," | ",
  66.                                count(if (atts.jenisabsen_id = "6" && jadwalkerjas.sifat="TWA",1,null))
  67.                                ) as tugas_belajar'),
  68.                 DB::raw('concat(
  69.                                count(if (atts.jenisabsen_id = "8" && jadwalkerjas.sifat="WA",1,null))
  70.                                ," | ",
  71.                                count(if (atts.jenisabsen_id = "8" && jadwalkerjas.sifat="TWA",1,null))
  72.                                ) as rapatundangan'),
  73.                 DB::raw('concat(
  74.                                count(if ((atts.jenisabsen_id < jadwalkerjas.jam_keluarjadwal && atts.jam_masuk is not null && jam_keluar is null) && jadwalkerjas.sifat="WA",1,null))
  75.                                ," | ",
  76.                                count(if ((atts.jenisabsen_id < jadwalkerjas.jam_keluarjadwal && atts.jam_masuk is not null && jam_keluar is null) && jadwalkerjas.sifat="TWA",1,null))
  77.                                ) as pulang_cepat'),
  78.                 DB::raw('concat(
  79.                                count(if (atts.keterangankeluar_id = "12" && jadwalkerjas.sifat="WA",1,null))
  80.                                ," | ",
  81.                                count(if (atts.keterangankeluar_id = "12" && jadwalkerjas.sifat="TWA",1,null))
  82.                                ) as ijinpulangcepat'),
  83.                 DB::raw('SEC_TO_TIME(SUM(time_to_sec(atts.akumulasi_sehari))) as total_akumulasi'),
  84.                 DB::raw('SEC_TO_TIME(SUM(time_to_sec(atts.terlambat))) as total_terlambat'),
  85.                 'instansis.namaInstansi',
  86.                 'pegawais.instansi_id'
  87.         )
  88.         ->orderBy(DB::raw('EXTRACT(YEAR_MONTH FROM atts.tanggal_att)'),'DESC')
  89.         ->groupBy(DB::raw('EXTRACT(YEAR_MONTH FROM atts.tanggal_att)'),DB::raw('pegawais.id'));  
  90.  
  91.         $attstwa=pegawai::leftJoin('atts','atts.pegawai_id','=','pegawais.id')
  92.         ->leftJoin('jadwalkerjas','atts.jadwalkerja_id','=','jadwalkerjas.id')
  93.         ->leftJoin('instansis','pegawais.instansi_id','=','instansis.id')
  94.         ->select(
  95.                 'pegawais.id',
  96.                 'pegawais.nip',
  97.                 'pegawais.nama',
  98.                 DB::raw('DATE_FORMAT( tanggal_att, "%m-%Y" ) as periode'),
  99.                 DB::raw('count(if(atts.jenisabsen_id!="9" && atts.jenisabsen_id != "11" && atts.jenisabsen_id!="13",1,null)) as hari_kerja'),
  100.                 DB::raw('count(if (atts.jenisabsen_id = "1" && atts.jam_keluar is not null,1,null)) as hadir'),
  101.                 DB::raw('count(if (atts.apel = "1",1,null)) as apelbulanan'),
  102.                 DB::raw('count(if (atts.terlambat != "00:00:00",1,null)) as terlambat'),
  103.                 DB::raw('count(if (atts.jenisabsen_id = "2" || (atts.jam_keluar is null && atts.jenisabsen_id="1"),1,null)) as tanpa_kabar'),
  104.                 DB::raw('count(if (atts.jenisabsen_id = "3",1,null)) as ijin'),
  105.                 DB::raw('count(if (atts.keteranganmasuk_id = "10",1,null)) as ijinterlambat'),
  106.                 DB::raw('count(if ((atts.apel = "0" && jadwalkerjas.sifat="FD") || ((atts.apel = "0" && jadwalkerjas.sifat="TWA")),1,null)) as tidakapelwajibapel'),
  107.                 DB::raw('count(if (atts.jenisabsen_id = "5",1,null)) as sakit'),
  108.                 DB::raw('count(if (atts.jenisabsen_id = "4",1,null)) as cuti'),
  109.                 DB::raw('count(if (atts.jenisabsen_id = "7",1,null)) as tugas_luar'),
  110.                 DB::raw('count(if (atts.jenisabsen_id = "6",1,null)) as tugas_belajar'),
  111.                 DB::raw('count(if (atts.jenisabsen_id = "8",1,null)) as rapatundangan'),
  112.                 DB::raw('count(if (atts.jenisabsen_id < jadwalkerjas.jam_keluarjadwal && atts.jam_masuk is not null && jam_keluar is null,1,null)) as pulang_cepat'),
  113.                 DB::raw('count(if (atts.keterangankeluar_id = "12",1,null)) as ijinpulangcepat'),
  114.                 DB::raw('SEC_TO_TIME(SUM(time_to_sec(atts.akumulasi_sehari))) as total_akumulasi'),
  115.                 DB::raw('SEC_TO_TIME(SUM(time_to_sec(atts.terlambat))) as total_terlambat'),
  116.                 'instansis.namaInstansi',
  117.                 'pegawais.instansi_id'
  118.         )
  119.         ->orderBy(DB::raw('EXTRACT(YEAR_MONTH FROM atts.tanggal_att)'),'DESC')
  120.         ->groupBy(DB::raw('EXTRACT(YEAR_MONTH FROM atts.tanggal_att)'),DB::raw('pegawais.id'))
  121.         ->where('jadwalkerjas.sifat','=','TWA');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!