233 lines
8.8 KiB
PHP
233 lines
8.8 KiB
PHP
<?php
|
|
|
|
namespace App\Services\Cartables\SafetyAndPrivacy;
|
|
|
|
use App\Exceptions\ProhibitedAction;
|
|
use App\Facades\DataTable\DataTableFacade;
|
|
use App\Models\SafetyAndPrivacy;
|
|
use Illuminate\Http\Request;
|
|
use Illuminate\Support\Facades\DB;
|
|
|
|
class OperatorService
|
|
{
|
|
public function dataTable(Request $request)
|
|
{
|
|
$user = auth()->user();
|
|
|
|
$fields = [
|
|
'id','lat','lon', 'recognize_picture','action_picture','created_at','step', 'final_description',
|
|
'info_fa', 'activity_date_time', 'action_picture_document_upload_date', 'judiciary_document_upload_date',
|
|
'step_fa', 'axis_type_id', 'axis_type_name', 'action_date', 'is_finished', 'status_fa', 'supervisor_description',
|
|
'operator_description', 'finish_picture', 'evidence_picture', 'recognize_picture_second'
|
|
];
|
|
|
|
throw_if(is_null($user->edarate_shahri_id), new ProhibitedAction('اداره ای برای شما در سامانه ثبت نشده است!'));
|
|
|
|
$query = SafetyAndPrivacy::query()->where('edare_shahri_id', '=', $user->edarate_shahri_id);
|
|
|
|
return DataTableFacade::run(
|
|
$query,
|
|
$request,
|
|
allowedFilters: ['*'],
|
|
allowedSortings: ['*'],
|
|
allowedSelects: $fields
|
|
);
|
|
}
|
|
|
|
public function countryActivity(int $info_id, Request $request): array
|
|
{
|
|
$params = [
|
|
'infoId' => $info_id,
|
|
];
|
|
|
|
$dateFields = [
|
|
'created_at',
|
|
'activity_date_time',
|
|
'judiciary_document_upload_date',
|
|
'action_date',
|
|
];
|
|
|
|
|
|
foreach ($dateFields as $field) {
|
|
$params["{$field}_from"] = null;
|
|
$params["{$field}_to"] = null;
|
|
$params["{$field}_from_2"] = null;
|
|
$params["{$field}_to_2"] = null;
|
|
}
|
|
|
|
foreach (json_decode($request->filters, true) as $filter) {
|
|
if ($filter['fn'] === "between")
|
|
{
|
|
$field = $filter['id'];
|
|
$params["{$field}_from"] = $filter['value'][0] . ' 00:00:00';
|
|
$params["{$field}_to"] = $filter['value'][1] . ' 23:59:59';
|
|
$params["{$field}_from_2"] = $filter['value'][0] . ' 00:00:00';
|
|
$params["{$field}_to_2"] = $filter['value'][1] . ' 23:59:59';
|
|
}
|
|
}
|
|
|
|
return DB::select("
|
|
WITH base AS (
|
|
SELECT
|
|
p.id AS province_id,
|
|
p.name_fa AS province_name,
|
|
a.id AS axis_type_id,
|
|
COALESCE(SUM(t.step = 1), 0) AS s1,
|
|
COALESCE(SUM(t.step = 2), 0) AS s2,
|
|
COALESCE(SUM(t.step = 3), 0) AS s3
|
|
FROM provinces p
|
|
JOIN axis_types a
|
|
LEFT JOIN safety_and_privacy t
|
|
ON t.province_id = p.id
|
|
AND t.axis_type_id = a.id
|
|
AND t.info_id = :infoId
|
|
AND (
|
|
(:created_at_from IS NULL OR :created_at_to IS NULL)
|
|
OR t.created_at BETWEEN :created_at_from_2 AND :created_at_to_2)
|
|
AND (
|
|
(:activity_date_time_from IS NULL OR :activity_date_time_to IS NULL)
|
|
OR t.activity_date_time BETWEEN :activity_date_time_from_2 AND :activity_date_time_to_2
|
|
)
|
|
AND (
|
|
(:judiciary_document_upload_date_from IS NULL OR :judiciary_document_upload_date_to IS NULL)
|
|
OR t.judiciary_document_upload_date BETWEEN :judiciary_document_upload_date_from_2 AND :judiciary_document_upload_date_to_2
|
|
)
|
|
AND (
|
|
(:action_date_from IS NULL OR :action_date_to IS NULL)
|
|
OR t.action_date BETWEEN :action_date_from_2 AND :action_date_to_2
|
|
)
|
|
GROUP BY p.id, a.id
|
|
)
|
|
SELECT
|
|
province_id,
|
|
province_name,
|
|
axis_type_id,
|
|
s1,
|
|
s2,
|
|
s3,
|
|
COALESCE(
|
|
SUM(s1 + s2 + s3)
|
|
OVER (PARTITION BY province_id), 0
|
|
) AS total_sum
|
|
FROM base
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
-1 AS province_id,
|
|
'کل کشور' AS province_name,
|
|
axis_type_id,
|
|
COALESCE(SUM(s1), 0) AS s1,
|
|
COALESCE(SUM(s2), 0) AS s2,
|
|
COALESCE(SUM(s3), 0) AS s3,
|
|
COALESCE(
|
|
SUM(SUM(s1 + s2 + s3)) OVER (), 0
|
|
) AS total_sum
|
|
FROM base
|
|
GROUP BY axis_type_id
|
|
|
|
ORDER BY province_id, axis_type_id
|
|
|
|
",$params);
|
|
}
|
|
public function provinceActivity(Request $request, int $info_id): array
|
|
{
|
|
$params = [
|
|
'infoId' => $info_id,
|
|
];
|
|
|
|
$dateFields = [
|
|
'created_at',
|
|
'activity_date_time',
|
|
'judiciary_document_upload_date',
|
|
'action_date',
|
|
];
|
|
|
|
|
|
foreach ($dateFields as $field) {
|
|
$params["{$field}_from"] = NULL;
|
|
$params["{$field}_to"] = NULL;
|
|
$params["{$field}_from_2"] = NULL;
|
|
$params["{$field}_to_2"] = NULL;
|
|
}
|
|
|
|
foreach (json_decode($request->filters, true) as $filter) {
|
|
if ($filter['fn'] === "between")
|
|
{
|
|
$field = $filter['id'];
|
|
$params["{$field}_from"] = $filter['value'][0] . ' 00:00:00';
|
|
$params["{$field}_to"] = $filter['value'][1] . ' 23:59:59';
|
|
$params["{$field}_from_2"] = $filter['value'][0] . ' 00:00:00';
|
|
$params["{$field}_to_2"] = $filter['value'][1] . ' 23:59:59';
|
|
}
|
|
if ($filter['id'] === "province_id")
|
|
{
|
|
$params["provinceId"] = $filter['value'];
|
|
}
|
|
}
|
|
|
|
return DB::select("
|
|
WITH edareh_data AS (
|
|
SELECT
|
|
e.id AS edare_id,
|
|
e.name_fa AS edare_name,
|
|
a.id AS axis_type_id,
|
|
COALESCE(SUM(t.step = 1), 0) AS s1,
|
|
COALESCE(SUM(t.step = 2), 0) AS s2,
|
|
COALESCE(SUM(t.step = 3), 0) AS s3
|
|
FROM edarate_shahri e
|
|
JOIN axis_types a ON 1
|
|
LEFT JOIN safety_and_privacy t
|
|
ON e.id = t.edare_shahri_id
|
|
AND t.axis_type_id = a.id
|
|
AND t.info_id = :infoId
|
|
AND (
|
|
(:created_at_from IS NULL OR :created_at_to IS NULL)
|
|
OR t.created_at BETWEEN :created_at_from_2 AND :created_at_to_2)
|
|
AND (
|
|
(:activity_date_time_from IS NULL OR :activity_date_time_to IS NULL)
|
|
OR t.activity_date_time BETWEEN :activity_date_time_from_2 AND :activity_date_time_to_2
|
|
)
|
|
AND (
|
|
(:judiciary_document_upload_date_from IS NULL OR :judiciary_document_upload_date_to IS NULL)
|
|
OR t.judiciary_document_upload_date BETWEEN :judiciary_document_upload_date_from_2 AND :judiciary_document_upload_date_to_2
|
|
)
|
|
AND (
|
|
(:action_date_from IS NULL OR :action_date_to IS NULL)
|
|
OR t.action_date BETWEEN :action_date_from_2 AND :action_date_to_2
|
|
)
|
|
WHERE e.province_id = :provinceId
|
|
GROUP BY e.id, a.id
|
|
)
|
|
SELECT
|
|
edare_id,
|
|
edare_name,
|
|
axis_type_id,
|
|
s1,
|
|
s2,
|
|
s3,
|
|
COALESCE(
|
|
SUM(s1 + s2 + s3)
|
|
OVER (PARTITION BY edare_id), 0
|
|
) AS total_sum
|
|
FROM edareh_data
|
|
|
|
UNION ALL
|
|
|
|
SELECT
|
|
-1 AS edare_id,
|
|
'کل استان' AS edare_name,
|
|
axis_type_id,
|
|
COALESCE(SUM(s1), 0) AS s1,
|
|
COALESCE(SUM(s2), 0) AS s2,
|
|
COALESCE(SUM(s3), 0) AS s3,
|
|
COALESCE(
|
|
SUM(SUM(s1 + s2 + s3)) OVER (), 0
|
|
) AS total_sum
|
|
FROM edareh_data
|
|
GROUP BY axis_type_id
|
|
|
|
ORDER BY edare_id , axis_type_id
|
|
", $params);
|
|
}
|
|
} |