667 lines
19 KiB
PHP
667 lines
19 KiB
PHP
<?php
|
|
|
|
namespace App\Models\Pelaporan;
|
|
|
|
use App\Models\Verifikasi;
|
|
use Illuminate\Database\Eloquent\Model;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
class Pelaporan extends Model
|
|
{
|
|
protected $table = 'Pelaporan';
|
|
protected $primaryKey = 'PelaporanId';
|
|
public $timestamps = false;
|
|
|
|
// Constan nama tabel referensi
|
|
const PERIODE = 'PeriodePelaporan';
|
|
const PERUSAHAAN = 'Perusahaan';
|
|
|
|
/**
|
|
* Ambil semua data pelaporan.
|
|
*
|
|
* @return array
|
|
*/
|
|
public static function getData()
|
|
{
|
|
return DB::table('Pelaporan as a')
|
|
->select('a.*', 'b.*', 'b.Nama as Periode')
|
|
->join(self::PERIODE . ' as b', 'b.id', '=', 'a.PeriodePelaporanId')
|
|
->orderByDesc('a.Tahun')
|
|
->orderBy('a.PeriodePelaporanId')
|
|
->get()
|
|
->toArray();
|
|
}
|
|
|
|
/**
|
|
* Ambil nilai SKL berdasarkan id pelaporan dan id ref pelaporan.
|
|
*/
|
|
public static function getNilaiSkl($idmcpelaporan, $idrefpelaporan)
|
|
{
|
|
$kode = self::getPelaporanCode($idrefpelaporan);
|
|
$column = 'skl_' . $kode;
|
|
|
|
$row = DB::table('Pelaporan')
|
|
->where('PelaporanId', $idmcpelaporan)
|
|
->first();
|
|
|
|
return $row ? ($row->$column ?? null) : null;
|
|
}
|
|
|
|
/**
|
|
* Ambil nilai SPL berdasarkan id pelaporan dan id ref pelaporan.
|
|
*/
|
|
public static function getNilaiSpl($idmcpelaporan, $idrefpelaporan)
|
|
{
|
|
$kode = self::getPelaporanCode($idrefpelaporan);
|
|
if ($kode != 'il') {
|
|
$column = 'spl_' . $kode;
|
|
$row = DB::table('Pelaporan')
|
|
->where('PelaporanId', $idmcpelaporan)
|
|
->first();
|
|
|
|
return $row ? ($row->$column ?? null) : null;
|
|
}
|
|
return null;
|
|
}
|
|
|
|
/**
|
|
* Ambil data pelaporan dengan filter.
|
|
*
|
|
* @param array $filter
|
|
* @return array
|
|
*/
|
|
public static function getDataBy(array $filter)
|
|
{
|
|
return DB::table('Pelaporan as a')
|
|
->select('a.*', 'a.id as PelaporanId', 'b.*', 'b.nama as Periode')
|
|
->where($filter)
|
|
->join(self::PERIODE . ' as b', 'b.PelaporanId', '=', 'a.PeriodePelaporanId')
|
|
->orderByDesc('a.Tahun')
|
|
->orderBy('a.PeriodePelaporanId')
|
|
->get()
|
|
->toArray();
|
|
}
|
|
|
|
/**
|
|
* Ambil data untuk admin dengan filter, gabungan perusahaan.
|
|
*
|
|
* @param array $filter
|
|
* @return array
|
|
*/
|
|
public static function adminGetDataBy(array $filter)
|
|
{
|
|
return DB::table('Pelaporan as a')
|
|
->select('a.*', 'a.PelaporanId as PelaporanId', 'b.*', 'b.Nama as Periode', 'c.Nama as NamaPerusahaan')
|
|
->where($filter)
|
|
->join(self::PERIODE . ' as b', 'b.id', '=', 'a.PeriodePelaporanId')
|
|
->join(self::PERUSAHAAN . ' as c', 'c.id', '=', 'a.PerusahaanId')
|
|
->orderByDesc('a.Tahun')
|
|
->orderBy('a.PeriodePelaporanId')
|
|
->get()
|
|
->toArray();
|
|
}
|
|
|
|
/**
|
|
* Ambil id perusahaan berdasarkan idmcusers.
|
|
*/
|
|
public static function getIdPerusahaan($idmcusers)
|
|
{
|
|
$row = DB::table('GroupPerusahaan')
|
|
->where('users', $idmcusers)
|
|
->first();
|
|
|
|
return $row ? $row->idrefperusahaan : null;
|
|
}
|
|
|
|
/**
|
|
* Ambil data perusahaan berdasarkan idrefperusahaan.
|
|
*/
|
|
public static function getPerusahaan($idrefperusahaan)
|
|
{
|
|
return DB::table('Perusahaan')
|
|
->where('PerusahaanId', $idrefperusahaan)
|
|
->first();
|
|
}
|
|
|
|
/**
|
|
* Ambil semua perusahaan, dengan opsional filter idrefverifikator dan status aktif.
|
|
*/
|
|
public static function getAllPerusahaan($idrefverifikator = null, $is_active = null)
|
|
{
|
|
$query = DB::table('Perusahaan');
|
|
|
|
if ($idrefverifikator && $idrefverifikator !== "1") {
|
|
$query->where('VerifikatorId', $idrefverifikator);
|
|
}
|
|
if ($is_active !== null && $is_active !== '') {
|
|
$query->where('IsPublish', $is_active);
|
|
}
|
|
|
|
return $query->get()->toArray();
|
|
}
|
|
|
|
/**
|
|
* Ambil data user perusahaan berdasarkan idrefverifikator.
|
|
*/
|
|
public static function getDataUserPerusahaan($idrefverifikator)
|
|
{
|
|
$idrefusergroups_perusahaan = 4;
|
|
|
|
return DB::table('users')
|
|
->select('users.*')
|
|
->join('GroupPerusahaan', 'GroupPerusahaan.UserId', '=', 'users.id')
|
|
->join('Perusahaan', 'GroupPerusahaan.PerusahaanId', '=', 'Perusahaan.PerusahaanId')
|
|
->where('users.UserGroupId', $idrefusergroups_perusahaan)
|
|
->where('Perusahaan.VerifikatorId', $idrefverifikator)
|
|
->get()
|
|
->toArray();
|
|
}
|
|
|
|
/**
|
|
* Ambil data pelaporan berdasarkan ID.
|
|
*/
|
|
public static function getById($idpelaporan)
|
|
{
|
|
return self::_getQuery()
|
|
->where('a.id', $idpelaporan)
|
|
->first();
|
|
}
|
|
|
|
/**
|
|
* Mendapatkan kode pelaporan berdasarkan idrefpelaporan.
|
|
*/
|
|
public static function getPelaporanCode($idrefpelaporan)
|
|
{
|
|
$row = DB::table('Pelaporan')
|
|
->select('Kode')
|
|
->where('PelaporanId', $idrefpelaporan)
|
|
->first();
|
|
|
|
return $row ? $row->kode : null;
|
|
}
|
|
|
|
/**
|
|
* Mendapatkan semua kode pelaporan.
|
|
*/
|
|
public static function getAllPelaporanCode()
|
|
{
|
|
return DB::table('Pelaporan')
|
|
->select('Kode')
|
|
->get()
|
|
->toArray();
|
|
}
|
|
|
|
/**
|
|
* Mendapatkan data referensi pelaporan berdasarkan kode.
|
|
*/
|
|
public static function getRefPelaporanByCode($code = '')
|
|
{
|
|
return DB::table('Pelaporan')
|
|
->where('Kode', $code)
|
|
->first();
|
|
}
|
|
|
|
/**
|
|
* Mendapatkan data pelaporan dari mcpelaporan by id.
|
|
*/
|
|
public static function getMclaporan($idpelaporan)
|
|
{
|
|
return DB::table('Pelaporan')
|
|
->where('PelaporanId', $idpelaporan)
|
|
->first();
|
|
}
|
|
|
|
/**
|
|
* Update atau insert tanggal pelaporan.
|
|
*/
|
|
public static function updateTanggalPelaporan(array $data, $date)
|
|
{
|
|
$exists = DB::table('PelaporanDate')
|
|
->where($data)
|
|
->exists();
|
|
|
|
if ($exists) {
|
|
return DB::table('PelaporanDate')
|
|
->where($data)
|
|
->update(['ReportDate' => $date]);
|
|
} else {
|
|
$data['ReportDate'] = $date;
|
|
return DB::table('PelaporanDate')->insert($data);
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Ambil pelaporan by range tanggal dan opsional refpelaporan.
|
|
*/
|
|
public static function getPelaporanByDate($start_date, $end_date, $refpelaporan = '')
|
|
{
|
|
$query = DB::table('PelaporanDate')
|
|
->whereBetween('ReportDate', [$start_date, $end_date]);
|
|
|
|
if ($refpelaporan) {
|
|
$query->where('RefPelaporanId', $refpelaporan);
|
|
}
|
|
|
|
return $query->get()->toArray();
|
|
}
|
|
|
|
/**
|
|
* Ambil semua perusahaan yang ada (del = 'n').
|
|
*/
|
|
public static function getAllPerusahaanExist()
|
|
{
|
|
return DB::table('Perusahaan')
|
|
->where('IsPublish', '0')
|
|
->get()
|
|
->toArray();
|
|
}
|
|
|
|
/**
|
|
* Update nilai SKL di tabel mcpelaporan.
|
|
*/
|
|
public static function updateNilaiSkl($idpelaporan, $idrefpelaporan, $skl)
|
|
{
|
|
$kode = self::getPelaporanCode($idrefpelaporan);
|
|
$skl = $skl === null ? 0 : $skl;
|
|
|
|
return DB::table('Pelaporan')
|
|
->where('PelaporanId', $idpelaporan)
|
|
->update(["skl_$kode" => $skl]);
|
|
}
|
|
|
|
/**
|
|
* Update nilai SPL di tabel mcpelaporan.
|
|
*/
|
|
public static function updateNilaiSpl($idpelaporan, $idrefpelaporan, $spl)
|
|
{
|
|
$kode = self::getPelaporanCode($idrefpelaporan);
|
|
|
|
return DB::table('Pelaporan')
|
|
->where('PelaporanId', $idpelaporan)
|
|
->update(["spl_$kode" => $spl]);
|
|
}
|
|
|
|
/**
|
|
* Hitung rata-rata SKL untuk pelaporan tertentu.
|
|
*/
|
|
public static function getAvgSkl($idpelaporan)
|
|
{
|
|
$query = self::getMclaporan($idpelaporan);
|
|
if (!$query) return null;
|
|
|
|
$periode = $query->idrefperiodepelaporan;
|
|
|
|
if ($periode == 2 || $periode == 4) {
|
|
$codes = self::getAllPelaporanCode();
|
|
$sum = 0;
|
|
$n = DB::table('Pelaporan')->count();
|
|
|
|
foreach ($codes as $code) {
|
|
$col = 'skl_' . $code->kode;
|
|
$sum += $query->$col ?? 0;
|
|
}
|
|
} else {
|
|
$sum = ($query->skl_al ?? 0) + ($query->skl_lb3 ?? 0) + ($query->skl_lp ?? 0);
|
|
$n = 3;
|
|
}
|
|
|
|
return $n > 0 ? $sum / $n : null;
|
|
}
|
|
|
|
/**
|
|
* Hitung rata-rata SPL untuk pelaporan tertentu.
|
|
*/
|
|
public static function getAvgSpl($idpelaporan)
|
|
{
|
|
$query = self::getMclaporan($idpelaporan);
|
|
if (!$query) return null;
|
|
|
|
$periode = $query->idrefperiodepelaporan;
|
|
|
|
if ($periode == 2 || $periode == 4) {
|
|
$codes = self::getAllPelaporanCode();
|
|
$sum = 0;
|
|
$n = DB::table('Pelaporan')->count() - 2;
|
|
|
|
foreach ($codes as $code) {
|
|
if ($code->kode != 'il' && $code->kode != 'kdm') {
|
|
$col = 'spl_' . $code->kode;
|
|
$sum += $query->$col ?? 0;
|
|
}
|
|
}
|
|
} else {
|
|
$sum = ($query->spl_al ?? 0) + ($query->spl_lb3 ?? 0) + ($query->spl_lp ?? 0);
|
|
$n = 3;
|
|
}
|
|
|
|
return $n > 0 ? $sum / $n : null;
|
|
}
|
|
|
|
/**
|
|
* Update rata-rata SKL pada pelaporan.
|
|
*/
|
|
public static function updateAvgSkl($idpelaporan)
|
|
{
|
|
$avg_skl = self::getAvgSkl($idpelaporan);
|
|
|
|
return DB::table('Pelaporan')
|
|
->where('PelaporanId', $idpelaporan)
|
|
->update(['skl' => $avg_skl]);
|
|
}
|
|
|
|
/**
|
|
* Update rata-rata SPL pada pelaporan.
|
|
*/
|
|
public static function updateAvgSpl($idpelaporan)
|
|
{
|
|
$avg_spl = self::getAvgSpl($idpelaporan);
|
|
|
|
return DB::table('Pelaporan')
|
|
->where('PelaporanId', $idpelaporan)
|
|
->update(['spl' => $avg_spl]);
|
|
}
|
|
|
|
/**
|
|
* Ambil satu data verifikasi berdasarkan filter.
|
|
*/
|
|
public static function getOneVerifikasi(array $filter)
|
|
{
|
|
return DB::table('Verifikasi')
|
|
->where($filter)
|
|
->first();
|
|
}
|
|
|
|
/**
|
|
* Ambil banyak data verifikasi berdasarkan filter.
|
|
*/
|
|
public static function getVerifikasi(array $filter)
|
|
{
|
|
return DB::table('Verifikasi')
|
|
->where($filter)
|
|
->get()
|
|
->toArray();
|
|
}
|
|
|
|
/**
|
|
* Ambil data evaluasi berdasarkan filter.
|
|
*/
|
|
public static function getEvaluasi(array $filter)
|
|
{
|
|
return DB::table('Evaluasi')
|
|
->where($filter)
|
|
->first();
|
|
}
|
|
|
|
/**
|
|
* Ambil data tanda terima berdasarkan filter.
|
|
*/
|
|
public static function getTandaterima(array $filter)
|
|
{
|
|
return DB::table('TandaTerima')
|
|
->where($filter)
|
|
->first();
|
|
}
|
|
|
|
/**
|
|
* Perbaiki status verifikasi.
|
|
*
|
|
* @param array $filter
|
|
* @param array $data
|
|
*/
|
|
public static function fixStatusVerifikasi(array $filter, array $data)
|
|
{
|
|
$result = DB::table('Verifikasi')
|
|
->join('Pelaporan', 'Verifikasi.PelaporanId', '=', 'Pelaporan.PelaporanId')
|
|
->where('Verifikasi.Verif', 0)
|
|
->where('Pelaporan.SKL', '>', 0)
|
|
->select('Verifikasi.VerifikasiId')
|
|
->get();
|
|
|
|
if ($result->isNotEmpty()) {
|
|
$data_id = [];
|
|
foreach ($result as $row) {
|
|
$array_data = $data;
|
|
$array_data['VerifikasiId'] = $row->id;
|
|
$array_data['Verifikasi'] = 1;
|
|
$data_id[] = $array_data;
|
|
}
|
|
// Laravel batch update requires package or raw query, simplified as loop update:
|
|
foreach ($data_id as $item) {
|
|
DB::table('Verifikasi')
|
|
->where('VerifikasiId', $item['VerifikasiId'])
|
|
->update(['Verif' => $item['Verif']] + $item);
|
|
}
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Update status verifikasi.
|
|
*/
|
|
public static function updateStatusVerifikasi(array $filter, array $data)
|
|
{
|
|
$verifikasi = self::getOneVerifikasi($filter);
|
|
|
|
if ($verifikasi) {
|
|
if ($verifikasi->verifikasi < ($data['Verif'] ?? 0)) {
|
|
DB::table('Verifikasi')
|
|
->where($filter)
|
|
->update($data);
|
|
}
|
|
} else {
|
|
DB::table('Verifikasi')->insert(array_merge($filter, $data));
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Insert pelaporan dan dapatkan ID baru.
|
|
*/
|
|
public static function insertPelaporan(array $data)
|
|
{
|
|
return DB::table('Pelaporan')->insertGetId($data);
|
|
}
|
|
|
|
/**
|
|
* Insert data verifikasi.
|
|
*/
|
|
public static function insertVerifikasi(array $data)
|
|
{
|
|
return DB::table('Verifikasi')->insert($data);
|
|
}
|
|
|
|
/**
|
|
* Ambil nilai konfigurasi batas penguncian pelaporan dari mcsystem.
|
|
*
|
|
* @return int|null
|
|
*/
|
|
public static function checkReportLockedAfter()
|
|
{
|
|
$row = DB::table('System')
|
|
->where('Name', 'ReportLockedAfter')
|
|
->first();
|
|
|
|
return $row ? (int)$row->val : null;
|
|
}
|
|
|
|
/**
|
|
* Update kolom is_checked pada pelaporan.
|
|
*/
|
|
public static function updateIsChecked(array $filter, $is_checked)
|
|
{
|
|
return DB::table('Pelaporan')
|
|
->where($filter)
|
|
->update(['IsChecked' => $is_checked]);
|
|
}
|
|
|
|
/**
|
|
* Cek apakah pelaporan editable.
|
|
*
|
|
* @param int $idmcpelaporan
|
|
* @param string|null $parameter
|
|
* @param object|null $currentUser // objek user untuk cek hak admin/granted, harus diberikan saat pemanggilan
|
|
* @return bool
|
|
*/
|
|
public static function checkEditable($idmcpelaporan, $parameter = null, $currentUser = null)
|
|
{
|
|
// Jika user admin, bisa edit
|
|
if ($currentUser && method_exists($currentUser, 'isAdmin') && $currentUser->isAdmin()) {
|
|
return true;
|
|
}
|
|
|
|
$pelaporan = self::getById($idmcpelaporan);
|
|
if (empty($pelaporan)) {
|
|
return false;
|
|
}
|
|
|
|
$refperusahaan = self::getPerusahaan($pelaporan->idrefperusahaan);
|
|
if ($refperusahaan && $refperusahaan->is_report_locked) {
|
|
$tahun = $pelaporan->tahun;
|
|
$periode = $pelaporan->idrefperiodepelaporan;
|
|
$periode_tenggang = self::checkReportLockedAfter();
|
|
|
|
$periode_akhir_bulan = ($periode * 3) + $periode_tenggang;
|
|
$periode_akhir_tahun = $tahun;
|
|
|
|
if ($periode_akhir_bulan > 12) {
|
|
$periode_akhir_bulan -= 12;
|
|
$periode_akhir_tahun++;
|
|
}
|
|
|
|
$waktu_periode_akhir = $periode_akhir_tahun . ($periode_akhir_bulan <= 9 ? '0' . $periode_akhir_bulan : $periode_akhir_bulan);
|
|
$waktu_sekarang = date('Ym');
|
|
|
|
if ($waktu_sekarang > $waktu_periode_akhir) {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
if (!empty($pelaporan->is_checked)) {
|
|
$is_checked = json_decode($pelaporan->is_checked, true);
|
|
if ($parameter && isset($is_checked[$parameter]) && $is_checked[$parameter] == 1) {
|
|
return false;
|
|
}
|
|
}
|
|
|
|
return true;
|
|
}
|
|
|
|
/**
|
|
* Cek editable khusus verifikasi.
|
|
*
|
|
* @param int $idmcpelaporan
|
|
* @param string $idrefpelaporan
|
|
* @param object|null $currentUser
|
|
* @return bool
|
|
*/
|
|
public static function checkEditableVerifikasi($idmcpelaporan, $idrefpelaporan = '', $currentUser = null)
|
|
{
|
|
if (!$currentUser || !method_exists($currentUser, 'isGranted') || !method_exists($currentUser, 'isAdmin')) {
|
|
return false;
|
|
}
|
|
|
|
$result = false;
|
|
$pelaporan = self::getById($idmcpelaporan);
|
|
if (!$pelaporan) {
|
|
return false;
|
|
}
|
|
|
|
$tahun = $pelaporan->tahun;
|
|
$periode = $pelaporan->idrefperiodepelaporan;
|
|
|
|
if ($periode != 4) {
|
|
$bulan_akhir = $periode * 3;
|
|
$awal_tenggang = '01-' . ($bulan_akhir + 1) . '-' . $tahun;
|
|
} else {
|
|
$awal_tenggang = '01-01-' . ($tahun + 1);
|
|
}
|
|
|
|
$get_report_locked = self::checkReportLockedAfter();
|
|
|
|
$akhir_tenggang = strtotime($awal_tenggang . ' + ' . (30 * $get_report_locked) . ' days');
|
|
$current_date = strtotime(date('Y-m-d'));
|
|
|
|
if ($current_date <= $akhir_tenggang
|
|
&& $currentUser->isGranted('Verifikasi.edit')
|
|
&& !$currentUser->isAdmin()) {
|
|
// Cek id verifikator
|
|
// Di sini harus implementasi pemanggilan model VerifikasiModel sesuai kebutuhan anda
|
|
// Untuk contoh saya asumsikan ada method statis getIdVerifikator
|
|
$idmcusers = $currentUser->id ?? null;
|
|
if (!$idmcusers) return false;
|
|
|
|
// Contoh pemanggilan VerifikasiModel (harus dibuat)
|
|
$idrefverifikator = Verifikasi::getIdVerifikator($idmcusers);
|
|
|
|
if ($idrefverifikator !== null && $idrefverifikator !== "0") {
|
|
$query = DB::table('Pelaporan as a')
|
|
->join('Perusahaan as b', 'a.PerusahaanId', '=', 'b.id')
|
|
->where('a.PelaporanId', $idmcpelaporan)
|
|
->select('b.VerifikatorId')
|
|
->first();
|
|
|
|
if ($query && $query->idrefverifikator == $idrefverifikator) {
|
|
$result = true;
|
|
}
|
|
} else {
|
|
$result = true;
|
|
}
|
|
}
|
|
|
|
if ($result) {
|
|
$result = false;
|
|
$get_verifikasi = self::getVerifikasi(['PelaporanId' => $idmcpelaporan]);
|
|
if (!empty($get_verifikasi)) {
|
|
$last_verifikasi = end($get_verifikasi);
|
|
if ($last_verifikasi['verif'] == 2) {
|
|
$result = true;
|
|
} else {
|
|
// Dinonaktifkan berdasarkan task T7579
|
|
// logic lama bisa diaktifkan kembali bila diperlukan
|
|
return true;
|
|
}
|
|
}
|
|
}
|
|
|
|
return $result;
|
|
}
|
|
|
|
/**
|
|
* Insert atau update kolom is_checked jika belum ada.
|
|
*/
|
|
public static function insertUpdateIsChecked($idmcpelaporan)
|
|
{
|
|
$pelaporan = self::getById($idmcpelaporan);
|
|
if (empty($pelaporan->is_checked)) {
|
|
$all_refpelaporan = self::getAllPelaporanCode();
|
|
$is_checked = [];
|
|
foreach ($all_refpelaporan as $c) {
|
|
$is_checked[$c->kode] = 1;
|
|
}
|
|
self::updateIsChecked(['PelaporanId' => $idmcpelaporan], json_encode($is_checked));
|
|
}
|
|
}
|
|
|
|
/**
|
|
* Query dasar untuk pengambilan data pelaporan dengan join periode dan perusahaan.
|
|
*/
|
|
private static function _getQuery()
|
|
{
|
|
return DB::table('Pelaporan as a')
|
|
->select(
|
|
'a.*',
|
|
'b.Nama as Periode',
|
|
'b.BulanAwal',
|
|
'b.BulanAkhir',
|
|
'c.Nama as Perusahaan',
|
|
'c.Alamat',
|
|
'c.NomorInduk',
|
|
'c.PelaporanId as PerusahaanId',
|
|
'c.doc_pdl_orig',
|
|
'c.doc_pdl_path'
|
|
)
|
|
->join(self::PERIODE . ' as b', 'b.id', '=', 'a.PeriodedPelaporanId')
|
|
->join(self::PERUSAHAAN . ' as c', 'c.id', '=', 'a.PerusahaanId');
|
|
}
|
|
}
|
|
|
|
|