public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties) { $mdlName = $postData[config("constants.tabTable")]; $mdl = "App\\Models\\{$mdlName}"; $src_type_id = $postData['CodeQltySrcType']; $date_end = $postData['date_end']; $date_end = \Helper::parseDate($date_end); $filterBy = $postData['cboFilterBy']; $extraDataSet = []; $dataSet = null; $codeQltySrcType = CodeQltySrcType::getTableName(); // $qltData = $mdl::getTableName(); $uoms = $properties['uoms']; $sourceTypekey = array_search('CodeQltySrcType', array_column($uoms, 'id')); $sourceTypes = $uoms[$sourceTypekey]['data']; $objectType = null; $src_type_ids = $src_type_id == 0 ? [1, 2, 3, 4, 5, 6] : [$src_type_id]; $query = null; // \DB::enableQueryLog(); foreach ($src_type_ids as $srcTypeId) { $where = ['SRC_TYPE' => $srcTypeId]; switch ($srcTypeId) { case 1: case 2: case 3: case 4: $objectType = $sourceTypes->find($srcTypeId); $objectType = $objectType->CODE; $cquery = $mdl::join($objectType, function ($query) use($objectType, $facility_id, $dcTable) { $query->on("{$objectType}.ID", '=', "{$dcTable}.SRC_ID")->where("{$objectType}.FACILITY_ID", '=', $facility_id); })->where($where)->whereDate("{$dcTable}.{$filterBy}", '>=', $occur_date)->whereDate("{$dcTable}.{$filterBy}", '<=', $date_end)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.ID", "{$dcTable}.*")->orderBy($dcTable); // ->get(); $query = $query == null ? $cquery : $query->union($cquery); break; case 5: $objectType = $sourceTypes->find($srcTypeId); $objectType = $objectType->CODE; $storage = Storage::getTableName(); $pdVoyageDetail = PdVoyageDetail::getTableName(); $pdVoyage = PdVoyage::getTableName(); $cquery = $mdl::join($pdVoyageDetail, "{$dcTable}.SRC_ID", '=', "{$pdVoyageDetail}.ID")->join($pdVoyage, "{$pdVoyageDetail}.VOYAGE_ID", '=', "{$pdVoyage}.ID")->join($storage, function ($query) use($storage, $facility_id, $pdVoyage) { $query->on("{$storage}.ID", '=', "{$pdVoyage}.STORAGE_ID")->where("{$storage}.FACILITY_ID", '=', $facility_id); })->where($where)->whereDate($filterBy, '>=', $occur_date)->whereDate($filterBy, '<=', $date_end)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.ID", "{$dcTable}.*")->orderBy($dcTable); // ->get(); $query = $query == null ? $cquery : $query->union($cquery); break; case 6: $objectType = $sourceTypes->find($srcTypeId); $objectType = $objectType->CODE; $cquery = $mdl::where($where)->whereDate($filterBy, '>=', $occur_date)->whereDate($filterBy, '<=', $date_end)->select("{$dcTable}.ID as {$dcTable}", "{$dcTable}.ID as DT_RowId", "{$dcTable}.ID", "{$dcTable}.*")->orderBy($dcTable); // ->get(); $query = $query == null ? $cquery : $query->union($cquery); break; } } if ($query != null) { $dataSet = $query->get(); } // \Log::info(\DB::getQueryLog()); $sourceColumn = 'SRC_TYPE'; if ($dataSet && $dataSet->count() > 0) { if ($src_type_id > 0) { $srcTypeData = $this->getExtraDatasetBy($objectType, $facility_id); if ($srcTypeData) { $extraDataSet[$sourceColumn] = []; $extraDataSet[$sourceColumn][$src_type_id] = $srcTypeData; } } else { // \DB::enableQueryLog(); $bySrcTypes = $dataSet->groupBy('SRC_ID'); // \Log::info(\DB::getQueryLog()); if ($bySrcTypes) { $extraDataSet[$sourceColumn] = []; foreach ($bySrcTypes as $key => $srcType) { $srcTypeID = $srcType[0]->SRC_TYPE; $table = $sourceTypes->find($srcTypeID); $table = $table->CODE; $srcTypeData = $this->getExtraDatasetBy($table, $facility_id); if ($srcTypeData) { $extraDataSet[$sourceColumn][$srcTypeID] = $srcTypeData; } } } } } return ['dataSet' => $dataSet, 'extraDataSet' => $extraDataSet]; }
public static function calculateBg($flow_phase, $T_obs, $P_obs, $API_obs, $occur_date, $object_id, $object_type_code) { $_Bg = null; set_error_handler("evalErrorHandler"); if ($flow_phase == 1) { try { if ($T_obs && $P_obs && $API_obs) { $_Bg = self::calculateCrudeOil($T_obs, $P_obs, $API_obs); } } catch (Exception $e) { \Log::info($e->getTraceAsString()); } } else { if ($flow_phase == 2 || $flow_phase == 21) { $cqst = CodeQltySrcType::getTableName(); $qdata = QltyData::getTableName(); $row = QltyData::getQualityRow($object_id, $object_type_code, $occur_date); /* $row= QltyData::with(['CodeQltySrcType' => function ($query) use ($object_type_code) { $query->where('CODE', '=', $object_type_code); }]) ->where('SRC_ID',$object_id) ->whereDate("$qdata.EFFECTIVE_DATE", '<=', $occur_date) ->select("$qdata.ID") ->orderBy("$qdata.EFFECTIVE_DATE",'desc') ->first(); */ //Find composition %Mol // $sSQL="select a.ID from qlty_data a, code_qlty_src_type b where a.SRC_ID='$object_id' and a.SRC_TYPE=b.ID and b.CODE='$object_type_code' and a.EFFECTIVE_DATE<=STR_TO_DATE('$occur_date', '%m/%d/%Y') order by a.EFFECTIVE_DATE desc limit 1"; if ($row) { $dataID = $row->ID; // \DB::enableQueryLog(); $querys = ['C1' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'C1'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'C2' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'C2'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'C3' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'C3'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'C4I' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'IC4'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'C4N' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'NC4'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'C5I' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'IC5'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'C5N' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'NC5'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'C6' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'C6'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'C7' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'C7+'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'H2S' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'H2S'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'CO2' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'CO2'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'N2' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'N2'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(MOLE_FACTION)")), 'M_C7' => QltyProductElementType::where('CODE', 'C7+')->select(\DB::raw("max(MOL_WEIGHT)")), 'G_C7' => QltyDataDetail::whereHas('QltyProductElementType', function ($query) { $query->where("CODE", 'C7+'); })->where('QLTY_DATA_ID', $dataID)->select(\DB::raw("max(GAMMA_C7)"))]; $qr = \DB::table(null); foreach ($querys as $key => $query) { $qr = $qr->selectSub($query->getQuery(), $key); } $qdltDatas = $qr->first(); // \Log::info("qdltDatas C1 ".$qdltDatas->C1); // \Log::info(\DB::getQueryLog()); if ($qdltDatas) { $MolWt_C7 = $qdltDatas->M_C7; $gamma_C7 = $qdltDatas->G_C7; $M_C1 = $qdltDatas->C1; $M_C2 = $qdltDatas->C2; $M_C3 = $qdltDatas->C3; $M_C4n = $qdltDatas->C4N; $M_C4i = $qdltDatas->C4I; $M_C5n = $qdltDatas->C5N; $M_C5i = $qdltDatas->C5I; $M_C6n = $qdltDatas->C6; $M_C7 = $qdltDatas->C7; $M_H2S = $qdltDatas->H2S; $M_CO2 = $qdltDatas->CO2; $M_N2 = $qdltDatas->N2; if ($T_obs && $P_obs) { try { $_Bg = self::calculateGas($T_obs, $P_obs, $MolWt_C7, $gamma_C7, $M_C1, $M_C2, $M_C3, $M_C4n, $M_C4i, $M_C5n, $M_C5i, $M_C6n, $M_C7, $M_H2S, $M_CO2, $M_N2); } catch (Exception $e) { \Log::info($e->getTraceAsString()); } } } } } } restore_error_handler(); return $_Bg; }