public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties) { $object_type = $postData['CodeInjectPoint']; $product_type = 0; $keystoreStorage = KeystoreStorage::getTableName(); $codeProductType = CodeProductType::getTableName(); $where = ["{$keystoreStorage}.FACILITY_ID" => $facility_id, "{$keystoreStorage}.FDC_DISPLAY" => 1]; if ($product_type > 0) { $where["{$keystoreStorage}.PRODUCT"] = $product_type; } // \DB::enableQueryLog(); $dataSet = KeystoreStorage::join($codeProductType, "{$keystoreStorage}.PRODUCT", '=', "{$codeProductType}.ID")->where($where)->whereDate("{$keystoreStorage}.START_DATE", '<=', $occur_date)->leftJoin($dcTable, function ($join) use($keystoreStorage, $dcTable, $occur_date) { $join->on("{$keystoreStorage}.ID", '=', "{$dcTable}.KEYSTORE_STORAGE_ID"); $join->where("{$dcTable}.OCCUR_DATE", '=', $occur_date); })->select("{$keystoreStorage}.ID as X_FL_ID", "{$keystoreStorage}.ID as DT_RowId", "{$keystoreStorage}.NAME as {$dcTable}", "{$keystoreStorage}.PRODUCT as FL_FLOW_PHASE", "{$codeProductType}.NAME as PHASE_NAME", "{$dcTable}.*")->orderBy("{$dcTable}")->orderBy("{$keystoreStorage}.PRODUCT")->get(); // \Log::info(\DB::getQueryLog()); return ['dataSet' => $dataSet]; }
public function getDataSet($postData, $dcTable, $facility_id, $occur_date, $properties) { $product_type = $postData['CodeProductType']; switch ($dcTable) { case 'storage_data_value': case 'storage_data_plan': case 'storage_data_forecast': case 'STORAGE_DATA_VALUE': case 'STORAGE_DATA_PLAN': case 'STORAGE_DATA_FORECAST': $mdlName = "Storage"; $joindField = "STORAGE_ID"; $extraColumn = false; break; default: $mdlName = "Tank"; $joindField = "TANK_ID"; $extraColumn = "STORAGE_ID"; break; } $mdl = "App\\Models\\{$mdlName}"; $mainTableName = $mdl::getTableName(); $codeProductType = CodeProductType::getTableName(); $euWheres = ['FACILITY_ID' => $facility_id, 'FDC_DISPLAY' => 1]; // \DB::enableQueryLog(); $columns = ["{$mainTableName}.name as {$dcTable}", "{$mainTableName}.ID as DT_RowId", "{$codeProductType}.name as PHASE_NAME", "{$mainTableName}.ID as " . config("constants.tankId"), "{$mainTableName}.product as OBJ_FLOW_PHASE", "{$dcTable}.*"]; if ($extraColumn) { $columns[] = "{$mainTableName}.{$extraColumn}"; } $dataSet = $mdl::join($codeProductType, function ($query) use($mainTableName, $codeProductType, $product_type) { $query->on("{$codeProductType}.ID", '=', "{$mainTableName}.PRODUCT"); if ($product_type > 0) { $query->where("{$mainTableName}.PRODUCT", '=', $product_type); } })->where($euWheres)->whereDate('START_DATE', '<=', $occur_date)->leftJoin($dcTable, function ($join) use($mainTableName, $dcTable, $occur_date, $joindField) { $join->on("{$mainTableName}.ID", '=', "{$dcTable}.{$joindField}")->where("{$dcTable}.OCCUR_DATE", '=', $occur_date); })->select($columns)->orderBy($dcTable)->orderBy("{$mainTableName}.PRODUCT")->get(); // \Log::info(\DB::getQueryLog()); return ['dataSet' => $dataSet]; }