validate([ // 'format_file_id' => 'required|exists:conf_format_file,formatservice_ID', // 'file' => 'required|file|max:12209752', // 'limit' => 'nullable|integer', // 'noLimit' => 'nullable|boolean', // 'genWithSmartUpdate' => 'nullable|boolean', // 'createPatch' => 'nullable|boolean', // 'sendToEec' => 'nullable|boolean', // ]); $file = $request->file('file'); $filePath = $file->getRealPath(); $fileFormat = ConfFormatFile::where('formatservice_ID', $request->format_file_id)->first(); $jsonFilePath = storage_path(str_replace(".inc", ".json", $fileFormat->file)); if (!file_exists($jsonFilePath)) { return redirect()->back()->withErrors(['error' => 'Format JSON file not found.']); } $formatData = json_decode(file_get_contents($jsonFilePath), true); $message = ''; if ($request->createPatch) { $sqlPackAll = $this->readTxt($filePath, $formatData, $fileFormat, $request->limit, $request->noLimit); $this->processPatches($sqlPackAll, $fileFormat, $request->genWithSmartUpdate); $message .= 'Patch master file created successfully. '; } if ($request->sendToEec && !empty($fileFormat->ac)) { $timestamp = Carbon::now()->toDateString(); $response = $this->sendMasterFileToEcc(config('services.eec.url'), [ 'type' => $fileFormat->ac, 'file' => fopen($filePath, 'r'), 'timestamp' => $timestamp, ]); $responseStatus = $response->status(); $responseData = $response->json(); $this->keepTransactionToEcc(auth()->id(), $fileFormat->ac, $file->getClientOriginalName(), json_encode([ 'type' => $fileFormat->ac, 'timestamp' => $timestamp, ]), Carbon::now(), $responseStatus, json_encode($responseData)); $message .= 'Send to EEC successfully.'; } return redirect()->route('patch.index')->with('message', $message); } private function sendMasterFileToEcc($url, $postData) { return Http::attach('file', $postData['file']) ->asMultipart() ->post($url, [ 'type' => $postData['type'], 'timestamp' => $postData['timestamp'], ]); } private function keepTransactionToEcc($uid, $type, $filename, $requestData, $datetime, $responseStatus, $responseData) { DB::table('log_send_master2eec')->insert([ 'UID' => $uid, 'TYPE' => $type, 'FILENAME' => $filename, 'REQDATA' => $requestData, 'ACTDATETIME' => $datetime, 'RESPSTATUS' => $responseStatus, 'RESPDATA' => $responseData, ]); } private function processPatches($sqlPackAll, $fileFormat, $genWithSmartUpdate) { $uid = auth()->id(); $nameFormat = $fileFormat->name ?? 'Unknown Format'; $level = "Critical"; $date = Carbon::now(); foreach ($sqlPackAll as $index => $sqlPack) { $patchCode = $this->generatePatchCode($sqlPack, $genWithSmartUpdate); $this->savePatch($patchCode, $nameFormat, $level, $uid, $date); } } private function generatePatchCode($sqlPack, $genWithSmartUpdate) { $all64 = base64_encode(gzcompress(iconv('UTF-8', 'TIS-620', var_export($sqlPack, true)))); $patchCode = "\$ALL64=\"{$all64}\";\n"; if ($genWithSmartUpdate) { $patchCode .= "\$ALLVAR=gzuncompress(spnsmartupdatedecode(\$ALL64));\n"; } else { $patchCode .= "\$ALLVAR=gzuncompress(base64_decode(\$ALL64));\n"; } return $patchCode; } private function savePatch($patchCode, $nameFormat, $level, $uid, $date) { DB::table('conf_smartupdate')->insert([ 'PATCHNAME' => "Update Master File {$nameFormat} " . $date->toFormattedDateString(), 'PDATE' => $date, 'PLEVEL' => $level, 'PCODE' => 'SHIPPINGNET', 'PDESC' => $nameFormat, 'POWNER' => $uid, 'PTYPE' => 'UPDATE MASTER', 'PATCHCODE' => iconv("UTF-8", "TIS-620", $patchCode), 'PAPPROVEDATE' => now(), 'MAJOR_VERTSION' => 'ALL' ]); } private function readTxt($filePath, $formatData, $ac, $limit = 10000 , $noLimit = false) { ini_set('memory_limit', '-1'); $sqlPackAll = []; $count = 0; $fileHandle = fopen($filePath, "r"); while (!feof($fileHandle)) { $sqlPack = []; $text = fgets($fileHandle); try { $text = iconv('TIS-620', 'UTF-8', $text); } catch (\Throwable $th) { $encoding = mb_detect_encoding($text, ['UTF-8', 'ISO-8859-1', 'ASCII'], true); $text = iconv($encoding, 'UTF-8', $text); } $X = $Y = $Z = 0; $primaryKeyConditions = []; foreach ($formatData['primary_key'] as $keyIndex => $primaryKey) { $fieldFormat = $formatData['format'][$keyIndex]; $type = $fieldFormat['TYPE']; if (strpos($type, 'DE') !== false) { $type = str_replace('DE', '', $type); $type = (int) $type + 1; } $type = (int) filter_var($type, FILTER_SANITIZE_NUMBER_INT); $fieldValue = mb_substr($text, $Y, $type, 'UTF-8'); $Y += $type; if ($fieldFormat['FORMAT'] === 'date') { $fieldValue = $this->convertDate($fieldValue); } $primaryKeyConditions[] = $primaryKey['FILD'] . " = '" . trim($fieldValue) . "'"; } $select2[] = " WHERE ".implode(' AND ', $primaryKeyConditions); $select[] = "SELECT * FROM " . $formatData['database'] . " ". " WHERE ".implode(' AND ', $primaryKeyConditions); $insertFields = []; $insertValues = []; foreach ($formatData['format'] as $fieldFormat) { $type = $fieldFormat['TYPE']; if (strpos($type, 'DE') !== false) { $type = str_replace('DE', '', $type); $type = (int) $type + 1; } $type = (int) filter_var($type, FILTER_SANITIZE_NUMBER_INT); $fieldValue = mb_substr($text, $X, $type, 'UTF-8'); $X += $type; if ($fieldFormat['FORMAT'] === 'date') { $fieldValue = $this->convertDate($fieldValue); } $insertFields[] = "`" . $fieldFormat['FILD'] . "`"; $insertValues[] = "'" . str_replace("'", "''", trim($fieldValue)) . "'"; } $insert[] = "INSERT INTO " . $formatData['database'] . " (". implode(',', $insertFields) . ") VALUES (" . implode(',', $insertValues) . ")"; $update[] = $this->generateUpdateQuery($formatData['format'], $text, $Z, $formatData['primary_key']); $sqlPack = [ 'insert' => $insert, 'update' => $update, 'select' => $select, 'select2' => $select2, 'DATABASE' => $formatData['database'], 'counter' => $count+=1 ]; if ($count > $limit) { $sqlPackAll[]=$sqlPack; $count = 0; unset($sqlPack); } } fclose($fileHandle); return $sqlPackAll; } private function generateUpdateQuery($formatData, $text, &$Z, $primaryKeys) { $update = ""; foreach ($formatData as $index => $fieldFormat) { $type = $fieldFormat['TYPE']; if (strpos($type, 'DE') !== false) { $type = str_replace('DE', '', $type); $type = (int) $type + 1; } $type = (int) filter_var($type, FILTER_SANITIZE_NUMBER_INT); $fieldValue = mb_substr($text, $Z, $type, 'UTF-8'); $Z += $type; if ($fieldFormat['FORMAT'] === 'date') { $fieldValue = $this->convertDate($fieldValue); } if (!in_array($fieldFormat['FILD'], array_column($primaryKeys, 'FILD'))) { if ($update != "") { $update .= ", "; } $update .= "`" . $fieldFormat['FILD'] . "` = '" . str_replace("'", "''", trim($fieldValue)) . "'"; } } return $update; } }