public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties) { $date_end = $postData['date_end']; $date_end = \Helper::parseDate($date_end); $auditTrail = AuditTrail::getTableName(); $codeAuditReason = CodeAuditReason::getTableName(); $beginDate = $occur_date; if ($postData['IntObjectType'] > 0) { $objectName = IntObjectType::where("ID", $postData['IntObjectType'])->select("CODE")->first(); $objectName = $objectName ? $objectName->CODE : ""; $objectType = strtoupper(str_replace(' ', '_', $objectName)) . '_%'; } else { $objectType = '%'; } // $result = array(); // \DB::enableQueryLog(); $dataSet = AuditTrail::leftjoin($codeAuditReason, "{$auditTrail}.REASON", '=', "{$codeAuditReason}.ID")->where(["{$auditTrail}.FACILITY_ID" => $facility_id])->whereDate("{$auditTrail}.WHEN", '>=', $occur_date)->whereDate("{$auditTrail}.WHEN", '<=', $date_end)->where('TABLE_NAME', 'like', $objectType)->select(['ACTION', 'WHO', 'WHEN', 'TABLE_NAME', 'COLUMN_NAME', 'RECORD_ID', 'OBJECT_DESC', 'OLD_VALUE', 'NEW_VALUE', "{$codeAuditReason}.NAME AS REASON"])->get(); // \Log::info(\DB::getQueryLog()); /* foreach ($loadAudittrail as $v){ $v->WHEN = date('m-d-Y', strtotime($v->WHEN)); array_push($result, $v); } return response ()->json ( array ( 'result' => $result ) ); */ /* $dataSet = $mdl::whereDate("$dcTable.BEGIN_DATE",'>=',$occur_date) ->whereDate("$dcTable.BEGIN_DATE",'<=',$date_end) ->select( "$dcTable.ID as $dcTable", "$dcTable.ID as DT_RowId", "$dcTable.*") ->get(); */ return ['dataSet' => $dataSet]; }
public function getSurveillanceSetting(Request $request) { $data = $request->all(); $cfgFieldProps = array(); $tags = array(); $surs = array(); $objType_id = -100; $objectType = ""; $tag_other = array(); $other = ""; $strMessage = null; if (isset($data['OBJECT_TYPE'])) { $objectType = $data['OBJECT_TYPE']; } if (isset($data['OBJECT_ID'])) { $objType_id = $data['OBJECT_ID']; } if (isset($data['SUR'])) { $surs = explode('@', $data['SUR']); } $cfgFields = CfgFieldProps::where(['USE_DIAGRAM' => 1])->where('TABLE_NAME', 'like', $objectType . '%')->orderBy('TABLE_NAME', 'COLUMN_NAME')->get(['COLUMN_NAME', 'TABLE_NAME', 'LABEL']); if (count($cfgFields) > 0) { foreach ($cfgFields as $v) { $value = $v->TABLE_NAME . "/" . $v->COLUMN_NAME; $checked = in_array($value, $surs, TRUE) ? "checked" : ""; $v['CHECK'] = $checked; array_push($cfgFieldProps, $v); } } $intConnection = IntConnection::all(['ID', 'NAME']); $intTagMapping = IntTagMapping::getTableName(); $intObjectType = IntObjectType::getTableName(); //\DB::enableQueryLog (); $vTags = DB::table($intTagMapping . ' AS a')->join($intObjectType . ' AS b', 'a.OBJECT_TYPE', '=', 'b.ID')->where(['b.CODE' => $objectType])->where(function ($q) use($objType_id) { if ($objType_id != -100) { $q->where(['a.OBJECT_ID' => $objType_id]); } })->distinct()->orderBy('a.TAG_ID')->get(['a.TAG_ID', 'a.TAG_ID AS CHECK']); //\Log::info ( \DB::getQueryLog () ); if (count($vTags) > 0) { foreach ($vTags as $t) { $checked = in_array($t->TAG_ID, $surs, TRUE) ? "checked" : ""; $t->CHECK = $checked; array_push($tags, $t); } } if (count($vTags) <= 0) { $names = IntObjectType::where(['CODE' => $objectType])->select('NAME')->first(); $sname = count($names) > 0 ? $names->NAME : ""; $strMessage = '<br><br><br><br><center><font color="#88000">No tag configured for <b>' . $sname . '</b>.</font><br><br><input type="button" style="width:145px;" id="btnTagsMapping" value="Config Tag Mapping"></center>'; } if ($objType_id == -100) { $strMessage = '<br><br><br><br><center><font color="#880000">No tag displayed because object is not mapped.</font><br><br><input type="button" style="width:160px;" id="openSurveillanceSetting" value="Object Mapping"></center>'; } return response()->json(['cfgFieldProps' => $cfgFieldProps, 'intConnection' => $intConnection, 'tags' => $tags, 'strMessage' => $strMessage]); }