public static function findManyWithConfig($updatedIds) { $tableName = static::getTableName(); $euPhaseConfig = EuPhaseConfig::getTableName(); $result = static::join($euPhaseConfig, function ($query) use($tableName, $euPhaseConfig) { $query->on("{$euPhaseConfig}.EU_ID", '=', "{$tableName}.EU_ID")->on("{$tableName}.EVENT_TYPE", '=', "{$euPhaseConfig}.EVENT_TYPE")->on("{$euPhaseConfig}.FLOW_PHASE", '=', "{$tableName}.FLOW_PHASE"); })->whereIn("{$tableName}.ID", $updatedIds)->select("{$euPhaseConfig}.ID as " . config("constants.euPhaseConfigId"), "{$tableName}.*")->get(); return $result; }
public function CodeFlowPhase() { $EuPhaseConfig = EuPhaseConfig::getTableName(); $code_flow_phase = CodeFlowPhase::getTableName(); $eu_id = $this->ID; $result = CodeFlowPhase::whereHas("EuPhaseConfig", function ($query) use($EuPhaseConfig, $eu_id) { $query->where("{$EuPhaseConfig}.EU_ID", $eu_id); })->get(["{$code_flow_phase}.ID", "{$code_flow_phase}.NAME"]); return $result; }
public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties) { $eu_group_id = $postData['EnergyUnitGroup']; $record_freq = $postData['CodeReadingFrequency']; $phase_type = $postData['CodeFlowPhase']; $event_type = $postData['CodeEventType']; $alloc_type = array_key_exists('CodeAllocType', $postData) ? $postData['CodeAllocType'] : 0; $eu = EnergyUnit::getTableName(); $codeFlowPhase = CodeFlowPhase::getTableName(); $codeStatus = CodeStatus::getTableName(); $euPhaseConfig = EuPhaseConfig::getTableName(); $codeEventType = CodeEventType::getTableName(); $euWheres = ['FACILITY_ID' => $facility_id, 'FDC_DISPLAY' => 1]; if ($record_freq > 0) { $euWheres["{$eu}.DATA_FREQ"] = $record_freq; } if ($eu_group_id > 0) { $euWheres["{$eu}.EU_GROUP_ID"] = $eu_group_id; } // else $euWheres["$eu.EU_GROUP_ID"]= null; // \DB::enableQueryLog(); $dataSet = EnergyUnit::join($codeStatus, 'STATUS', '=', "{$codeStatus}.ID")->join($euPhaseConfig, function ($query) use($eu, $euPhaseConfig, $phase_type, $event_type) { $query->on("{$euPhaseConfig}.EU_ID", '=', "{$eu}.ID"); if ($phase_type > 0) { $query->where("{$euPhaseConfig}.FLOW_PHASE", '=', $phase_type); } if ($event_type > 0) { $query->where("{$euPhaseConfig}.EVENT_TYPE", '=', $event_type); } //TODO note chu y active co the se dung // $query->with('CodeFlowPhase'); // $query->select('FLOW_PHASE as EU_FLOW_PHASE'); })->join($codeFlowPhase, "{$euPhaseConfig}.FLOW_PHASE", '=', "{$codeFlowPhase}.ID")->join($codeEventType, "{$euPhaseConfig}.EVENT_TYPE", '=', "{$codeEventType}.ID")->where($euWheres)->whereDate('EFFECTIVE_DATE', '<=', $occur_date)->leftJoin($dcTable, function ($join) use($eu, $dcTable, $occur_date, $alloc_type, $euPhaseConfig) { //TODO add table name $join->on("{$eu}.ID", '=', "{$dcTable}.EU_ID")->on("{$dcTable}.FLOW_PHASE", '=', "{$euPhaseConfig}.FLOW_PHASE")->on("{$dcTable}.EVENT_TYPE", '=', "{$euPhaseConfig}.EVENT_TYPE")->where("{$dcTable}.OCCUR_DATE", '=', $occur_date); $energyUnitDataAlloc = EnergyUnitDataAlloc::getTableName(); $energyUnitCompDataAlloc = EnergyUnitCompDataAlloc::getTableName(); if ($alloc_type > 0 && ($dcTable == $energyUnitDataAlloc || $dcTable == $energyUnitCompDataAlloc)) { $join->where("{$dcTable}.ALLOC_TYPE", '=', $alloc_type); } })->select("{$eu}.name as {$dcTable}", "{$euPhaseConfig}.ID as DT_RowId", "{$codeFlowPhase}.name as PHASE_NAME", "{$codeEventType}.name as TYPE_NAME", "{$euPhaseConfig}.EVENT_TYPE as " . config("constants.eventType"), "{$eu}.ID as " . config("constants.euId"), "{$euPhaseConfig}.FLOW_PHASE as EU_FLOW_PHASE", "{$codeStatus}.NAME as STATUS_NAME", "{$codeFlowPhase}.CODE as PHASE_CODE", "{$codeEventType}.CODE as TYPE_CODE", "{$dcTable}.*")->orderBy($dcTable)->orderBy('EU_FLOW_PHASE')->get(); // \Log::info(\DB::getQueryLog()); return ['dataSet' => $dataSet]; }
private function loadPlotObjectname($data) { $facility_id = $data['facility_id']; $product_type = $data['product_type']; $object_type = $data['object_type']; $table_name = $obj_types[0]; $entity = strtolower(str_replace('_', ' ', $table_name)); $entity = ucwords($entity); $entity = str_replace(' ', '', $entity); $tmp = []; $model = 'App\\Models\\' . $entity; //\DB::enableQueryLog (); switch ($table_name) { case "TANK": case "STORAGE": $tmp = $model::where(['FACILITY_ID' => $facility_id])->where(function ($q) use($product_type) { if ($product_type != 0) { $q->where(['PRODUCT' => $product_type]); } })->get(['ID', 'NAME']); break; case "FLOW": $tmp = $model::where(['FACILITY_ID' => $facility_id])->where(function ($q) use($product_type) { if ($product_type != 0) { $q->where(['PHASE_ID' => $product_type]); } })->get(['ID', 'NAME']); break; case "ENERGY_UNIT": $tableName = $model::getTableName(); $euPhaseConfig = EuPhaseConfig::getTableName(); $tmp = DB::table($tableName . ' AS a')->where(['FACILITY_ID' => $facility_id])->whereNotExists(function ($query) use($euPhaseConfig, $product_type) { $query->select(DB::raw('A.ID'))->from($euPhaseConfig . ' AS b')->whereRaw('b.EU_ID = a.ID')->where(['b.FLOW_PHASE' => $product_type]); })->get(['ID', 'NAME']); break; } //\Log::info ( \DB::getQueryLog () ); return $tmp; }