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(); if (isset($request->truncateGoods) && ($request->format_file_id == 196)) { $truncateGoods = true; } else { $truncateGoods = false; } $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, $request->noLimit, $truncateGoods); $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, ]); } function processPatches($sqlPackAll, $fileFormat, $genSpnSmartUpdateDecode, $noLimit, $truncateGoods) { $uid = auth()->user()->id; $pLevel = "Critical"; $nameFormat = $fileFormat->name ?? 'Unknown Format'; $pDesc = $nameFormat; $day = Date('j'); $month = Date('n'); $year = Date('Y'); $pApproveDate = date("Y-m-d h:i:s"); $partPatch = ''; $countSql = count($sqlPackAll); $part = 1; foreach ($sqlPackAll as $i__ => $sqlPack) { if ($countSql != 1 && !$noLimit) { $partPatch = 'Part ' . $part++; } $patchCode = ''; if ($truncateGoods) { $patchCode .= '$sql = "truncate table master_permissiongoods"; exec_query($sql); if ($DBTYPE == "MSSQL") { $Count_CONSTRAINT = Query2ArrayVarPack("SELECT COUNT(TC.CONSTRAINT_NAME) AS count_CONSTRAINT,TC.CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_TYPE = \'PRIMARY KEY\' AND TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME and ku.table_name=\'master_permissiongoods\' GROUP BY TC.CONSTRAINT_NAME"); if ($Count_CONSTRAINT[\'count_CONSTRAINT\'][0] != 5) { exec_query("ALTER TABLE [master_permissiongoods] DROP CONSTRAINT [" . $Count_CONSTRAINT[\'CONSTRAINT_NAME\'][0] . "]"); exec_query("ALTER TABLE [master_permissiongoods] ADD CONSTRAINT [" . $Count_CONSTRAINT[\'CONSTRAINT_NAME\'][0] . "] PRIMARY KEY([tariffClass],[tariffStat],[startDate],[permissionGoodsType],[companyTaxNo])"); } } '; } $ALL64 = base64_encode(gzcompress(var_export($sqlPack, true))); $patchCode .= '$ALL64="' . $ALL64 . '";' . "\n"; if ($genSpnSmartUpdateDecode) { $updatedecode = '$ALLVAR=gzuncompress(spnsmartupdatedecode($ALL64));' . "\n"; } else { $updatedecode = '$ALLVAR=gzuncompress(base64_decode($ALL64));' . "\n"; } $patchCode .= $updatedecode; $patchCode .= "eval('\$SQL='." . '$ALLVAR.";");' . "\n"; $patchCode .= $this->generatePatchCode($countSql, $i__); if ($noLimit && ($i__ == $countSql - 1)) { $this->savePatch($patchCode, $nameFormat, $day, $month, $year, $partPatch, $pLevel, $pDesc, $uid, $pApproveDate); } elseif (!$noLimit) { $this->savePatch($patchCode, $nameFormat, $day, $month, $year, $partPatch, $pLevel, $pDesc, $uid, $pApproveDate); } } } private function generatePatchCode($countSql, $i__) { $patchCode = ''; $patchCode .= '$insert=$SQL["insert"];' . "\n"; $patchCode .= '$update=$SQL["update"];' . "\n"; $patchCode .= '$select=$SQL["select"];' . "\n"; $patchCode .= '$select2=$SQL["select2"];' . "\n"; $patchCode .= '$DATABASE=$SQL["DATABASE"];' . "\n"; $patchCode .= '$CountSQL_=count($insert);' . "\n"; $patchCode .= '$succ="0";' . "\n"; $patchCode .= '$succ2="0";' . "\n"; $patchCode .= 'for($i_=0;$i_<$CountSQL_;$i_++){' . "\n"; $patchCode .= ' $INSERT_ID="";' . "\n"; $patchCode .= ' if($DBTYPE=="MYSQL"){' . "\n"; $patchCode .= ' $sql=$insert[$i_]." ON DUPLICATE KEY UPDATE ".$update[$i_];' . "\n"; $patchCode .= ' }' . "\n"; $patchCode .= ' if($DBTYPE=="MSSQL"){' . "\n"; $patchCode .= ' $sql="IF EXISTS (".$select[$i_].") BEGIN UPDATE $DATABASE SET ".$update[$i_].$select2[$i_]." END ELSE ".$insert[$i_];' . "\n"; $patchCode .= ' }' . "\n"; $patchCode .= ' exec_query($sql);' . "\n"; $patchCode .= ' $succ++;' . "\n"; $patchCode .= ' if($INSERT_ID!=""){$succ2++;}' . "\n"; $patchCode .= '}' . "\n"; $patchCode .= 'unset($ALL64);' . "\n"; $patchCode .= 'unset($ALLVAR);' . "\n"; $patchCode .= 'unset($SQL);' . "\n"; $patchCode .= 'unset($insert);' . "\n"; $patchCode .= 'unset($update);' . "\n"; $patchCode .= 'unset($select);' . "\n"; $patchCode .= 'unset($select2);' . "\n"; $patchCode .= '$PRINT_ .= date("h:i:s")." Process ' . ($i__ + 1) . ' / ' . $countSql . ' Successful Update $succ data(s) (Added = $succ2) from $CountSQL_.
";' . "\n"; return $patchCode; } private function savePatch($patchCode, $nameformat, $day, $month, $year, $partPatch, $PLEVEL, $PDESC, $UID, $PAPPROVEDATE) { $patchCode .= '$PRINTOUT=$PRINT_;' . "\n"; $patchCode .= '$PATCH_STATUS="OK";' . "\n"; // $patchCode = str_replace("'", "''", $patchCode); $data = [ 'PATCHNAME' => iconv("TIS-620", "UTF-8","Update Master File $nameformat Çѹ·Õè $day à´×͹ $month »Õ $year $partPatch"), 'PDATE' => Carbon::now(), 'PLEVEL' => $PLEVEL, 'PCODE' => 'SHIPPINGNET', 'MAJOR_VERSION' => 'ALL', 'PDESC' => $PDESC, 'POWNER' => $UID, 'PTYPE' => 'UPDATE MASTER', 'PAPPROVEDATE' => $PAPPROVEDATE, 'PATCHCODE' => $patchCode, 'UNINSTALL' => '$DONE=1;', 'PATCHCODE_SERVER' => '$DONE=1;', ]; DB::table('conf_smartupdate')->insert($data); } private function readTxt($filePath, $formatData, $fileFormat, $limit = 10000, $noLimit = true) { ini_set('memory_limit', '-1'); $sqlPackAll = []; $fileHandle = fopen($filePath, "r"); $count = 0; $batch = []; while (!feof($fileHandle)) { $line = fgets($fileHandle); Log::info($line); $processedLine = $this->processLine($line, $formatData, $fileFormat); $batch[] = $processedLine; if (count($batch) >= $limit && !$noLimit) { $sqlPackAll[] = $this->generateSqlPack($batch, $formatData); $batch = []; } } if (!empty($batch)) { $sqlPackAll[] = $this->generateSqlPack($batch, $formatData); } fclose($fileHandle); // dd($sqlPackAll); return $sqlPackAll; } private function processLine($line, $formatData, $fileFormat) { $X = 0; $Y = 0; $primaryKeyConditions = []; $insertFields = []; $insertValues = []; foreach ($formatData['primary_key'] as $keyIndex => $primaryKey) { $fieldFormat = $formatData['format'][$keyIndex]; $type = $this->getFieldType($fieldFormat['TYPE']); // $fieldValue = mb_substr($line, $Y, $type, 'UTF-8'); $fieldValue = substr($line, $Y, $type); $Y += $type; if (isset($fieldFormat['format']) && $fieldFormat['format'] === 'date') { $fieldValue = $this->convertDate($fieldValue); } $primaryKeyConditions[] = $primaryKey['FILD'] . " = '" . trim($fieldValue) . "'"; } $select = "SELECT * FROM " . $formatData['database'] . " WHERE " . implode(' AND ', $primaryKeyConditions); $select2 = " WHERE " . implode(' AND ', $primaryKeyConditions); foreach ($formatData['format'] as $fieldFormat) { $type = $this->getFieldType($fieldFormat['TYPE']); $fieldValue = substr($line, $X, $type); $X += $type; if (isset($fieldFormat['format']) && $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'], $line, $X, $formatData['primary_key']); return [ 'insert' => $insert, 'update' => $update, 'select' => $select, 'select2' => $select2, 'DATABASE' => $formatData['database'], ]; } private function getFieldType($type) { if (strpos($type, 'DE') !== false) { $type = str_replace('DE', '', $type); $type = (int)$type + 1; } return (int)filter_var($type, FILTER_SANITIZE_NUMBER_INT); } private function generateSqlPack($batch, $formatData) { $sqlPack = [ 'insert' => [], 'update' => [], 'select' => [], 'select2' => [], 'DATABASE' => $formatData['database'], ]; foreach ($batch as $lineData) { $sqlPack['insert'][] = $lineData['insert']; $sqlPack['update'][] = $lineData['update']; $sqlPack['select'][] = $lineData['select']; $sqlPack['select2'][] = $lineData['select2']; } return $sqlPack; } 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 = substr($text, $Z, $type); $Z += $type; if (isset($fieldFormat['format'])) { 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; } }