using GreenPipes.Filters; using LAPS_XMLQC_Service.App_Data; using LAPS_XMLQC_Service.Models; using LAPS_XMLQC_Service.Services; using MassTransit.Initializers.Variables; using Microsoft.AspNetCore.Authorization; using Microsoft.AspNetCore.Mvc; using Microsoft.CodeAnalysis; using Microsoft.Extensions.Configuration; using Microsoft.IdentityModel.Logging; using Newtonsoft.Json; using Newtonsoft.Json.Linq; using Npgsql; using RabbitMQ.Client.Framing.Impl; using RabbitMQ.Client.Impl; using System; using System.Collections.Generic; using System.Data; using System.Diagnostics; using System.Globalization; using System.IO; using System.Linq; using System.Net.Http; using System.Net.NetworkInformation; using System.Reflection.Metadata; using System.Security.Cryptography; using System.Security.Principal; using System.Threading.Tasks; using System.Transactions; using System.Xml.Linq; using static MassTransit.Logging.LogCategoryName; namespace LAPS_XMLQC_Service.Controllers.WorkArea { [Route("api/[controller]")] [ApiController] public class WorkAreaApiController : ControllerBase { private readonly FileSearchService _fileSearchService; private readonly IWorkAreaService oWorkAreaService; private readonly CommonRepository oCommonRepository; private readonly GrantFolderPermission oGrantFolderPermission; private readonly string _connectionString; //public WorkAreaApiController(IConfiguration configuration) //{ // _connectionString = configuration.GetConnectionString("DbConnection"); //} public WorkAreaApiController(FileSearchService fileSearchService, IWorkAreaService workAreaService, CommonRepository commonRepository, GrantFolderPermission grantFolderPermission, IConfiguration configuration) { _fileSearchService = fileSearchService; oWorkAreaService = workAreaService; oCommonRepository = commonRepository; oGrantFolderPermission = grantFolderPermission; _connectionString = configuration.GetConnectionString("DbConnection"); } [Authorize] [HttpGet("GetServerPath")] public ActionResult GetServerPath(string tranid) { string serverpath = oWorkAreaService.GetServerPath(tranid, false, "serverinfo"); if (!string.IsNullOrEmpty(serverpath)) serverpath = System.IO.Path.Combine(serverpath, "IN"); else serverpath = string.Empty; return Ok(serverpath); } // [Authorize] [HttpGet("XmlReviewQC")] public async Task XmlReviewQC(string tranid, long jobid, long lotid, bool islot) { string xmlserverpath = string.Empty; string serverpath = string.Empty; string serverinpath = string.Empty; long transactionid = 0; var results = new List(); // oGrantFolderPermission.FolderPermission("", "", "", jobid.ToString()); //{ try { using (var connection = new NpgsqlConnection(_connectionString)) { await connection.OpenAsync(); Console.WriteLine("Database connection opened."); using (var command = new NpgsqlCommand( "SELECT lt.transactionid, lt.batchid " + "FROM tbljobmaster jm " + "JOIN tbljobworkflowdefinition jwm ON jwm.jobid = jm.jobid " + "JOIN tbllottransaction lt ON jwm.jobworkflowid = lt.jobworkflowid " + "WHERE jm.jobid = @jobid AND jwm.stagename_alias = 'PDF Analysis' " + "ORDER BY lt.createdon DESC LIMIT 1;", connection)) { command.Parameters.AddWithValue("@jobid", jobid); // execute reader or scalar here // command.Parameters.AddWithValue("@lotid", lotid); Console.WriteLine($"Executing SQL with jobid: {jobid}, lotid: {lotid}"); using (var reader = await command.ExecuteReaderAsync()) { if (await reader.ReadAsync()) // Use ReadAsync instead of Read { transactionid = reader.GetInt64(0); // Read as long instead of string Console.WriteLine("Transaction ID found: " + transactionid); } else { Console.WriteLine("No data found for the given jobid and lotid."); } } } } if (islot == false) { // Use async database operation if (transactionid != 0) { serverpath = oWorkAreaService.GetServerPath(transactionid.ToString(), false, "serverinfo"); Console.WriteLine("Server path retrieved: " + serverpath); } else { Console.WriteLine("Transaction ID is empty, unable to retrieve server path."); } } else { serverpath = oWorkAreaService.GetServerPath(transactionid.ToString(), false, "serverinfo"); } serverinpath = Path.Combine(serverpath, "IN"); Console.WriteLine("Server IN Path: " + serverinpath); if (!string.IsNullOrEmpty(serverinpath)) { oGrantFolderPermission.FolderPermission_vlex(serverinpath, "", "", "", jobid.ToString()); var identity = oGrantFolderPermission.GetImpartunate()?.AllowAccesstoServer(); WindowsIdentity.RunImpersonated(identity.AccessToken, () => { WindowsIdentity useri = WindowsIdentity.GetCurrent(); }); // Offload file system search to another thread asynchronously using Task.Run //var filePaths = await Task.Run(() => Directory.GetFiles(serverinpath, "*.pdf", SearchOption.AllDirectories)); //results = filePaths.Select(path => new MatchedResult //{ // FilePath = path //}).ToList(); var filePaths = await Task.Run(() => Directory.GetFiles(serverinpath, "*.pdf", SearchOption.AllDirectories) .Where(f => !f.EndsWith("_High.pdf", StringComparison.OrdinalIgnoreCase)) ); results = filePaths.Select(path => new MatchedResult { FilePath = path }).ToList(); // var filePaths = await Task.Run(() => //Directory.GetFiles(serverinpath, "*.pdf", SearchOption.AllDirectories) // .Where(f => !f.EndsWith("_High.pdf", StringComparison.OrdinalIgnoreCase)) // .ToArray()); Console.WriteLine($"Found {results.Count} files."); NetworkShareUtility.DisconnectFromShare(serverinpath); } return Ok(results); } catch (Exception ex) { Console.WriteLine($"Running as: {Environment.UserDomainName}\\{Environment.UserName}"); Console.WriteLine("An error occurred: " + ex.Message); return StatusCode(500, "Internal Server Error: " + string.Concat(ex.Message, Environment.UserName, Environment.UserDomainName)); } // } } //public async Task XmlReviewQC(string tranid, long jobid, long lotid, bool islot) //{ // string xmlserverpath = string.Empty; // string serverpath = string.Empty; // string serverinpath = string.Empty; // long transactionid = 0; // Use long instead of string for bigint column // var results = new List(); // try // { // if (islot == false) // { // // Open database connection and fetch transaction id // using (var connection = new NpgsqlConnection(_connectionString)) // { // connection.Open(); // Console.WriteLine("Database connection opened."); // using (var command = new NpgsqlCommand( // "SELECT lt.transactionid FROM tbljobmaster jm " + // "JOIN tbllotmaster lm ON jm.jobid = lm.jobid " + // "JOIN tbllottransaction lt ON lm.lotid = lt.lotid " + // "WHERE jm.jobid = @jobid AND lm.lotid = @lotid ORDER BY jm.createdon desc LIMIT 1", connection)) // { // // Pass the jobid and lotid as long (Int64) // command.Parameters.AddWithValue("@jobid", jobid); // command.Parameters.AddWithValue("@lotid", lotid); // using (var reader = command.ExecuteReader()) // { // if (reader.Read()) // { // transactionid = reader.GetInt64(0); // Read as long instead of string // Console.WriteLine("Transaction ID found: " + transactionid); // } // else // { // Console.WriteLine("No data found for the given jobid and lotid."); // } // } // } // } // // Check if transactionid is found before proceeding // if (transactionid != 0) // { // // Retrieve server path based on the transaction id // serverpath = oWorkAreaService.GetServerPath(transactionid.ToString(), false, "serverinfo"); // Console.WriteLine("Server path retrieved successfully."); // } // else // { // Console.WriteLine("Transaction ID is empty, unable to retrieve server path."); // } // } // else // { // // If islot is true, use the provided tranid // serverpath = oWorkAreaService.GetServerPath(tranid, false, "serverinfo"); // } // // Construct the path for the "IN" folder // serverinpath = Path.Combine(serverpath, "IN"); // // Optional permission logic (if applicable) // // oGrantFolderPermission.FolderPermission("", "", "", jobid); // // Perform file search if serverpath is valid // if (!string.IsNullOrEmpty(serverinpath)) // { // var filePaths = Directory.GetFiles(serverinpath, "*.pdf", SearchOption.AllDirectories); // results = filePaths.Select(path => new MatchedResult // { // FilePath = path // Adjust the property name as per your class // }).ToList(); // } // // Return the results as a response // return Ok(results); // } // catch (Exception ex) // { // // Log the error and return an internal server error status code // Console.WriteLine("An error occurred: " + ex.Message); // return StatusCode(500, "Internal Server Error: " + ex.Message); // } //} // [Authorize] //[HttpGet("PreviewPdf")] //public IActionResult PreviewPdf([FromQuery] string path) //{ // if (string.IsNullOrWhiteSpace(path) || !System.IO.File.Exists(path)) // return NotFound(); // var stream = new FileStream(path, FileMode.Open, FileAccess.Read); // return File(stream, "application/pdf"); //} //public async Task PreviewPdf([FromQuery] string path) //{ // if (string.IsNullOrWhiteSpace(path)) // return BadRequest("Missing PDF path."); // // Serve local file // if (System.IO.File.Exists(path)) // { // try // { // var stream = new FileStream(path, FileMode.Open, FileAccess.Read); // return File(stream, "application/pdf"); // } // catch (Exception ex) // { // Console.WriteLine($"Local file error: {ex.Message}"); // return StatusCode(500, "Error reading local file."); // } // } // // Try remote download if not a local path // if (Uri.TryCreate(path, UriKind.Absolute, out Uri? uriResult) && uriResult.Scheme.StartsWith("http")) // { // try // { // using var httpClient = new HttpClient(); // var response = await httpClient.GetAsync(path); // if (!response.IsSuccessStatusCode) // return StatusCode((int)response.StatusCode, "Unable to fetch PDF from URL."); // var stream = await response.Content.ReadAsStreamAsync(); // return File(stream, "application/pdf"); // } // catch (Exception ex) // { // Console.WriteLine($"Remote fetch error: {ex.Message}"); // return StatusCode(500, "Error fetching remote PDF."); // } // } // return BadRequest("Invalid path or URL."); //} [HttpGet("PreviewPdf")] public async Task PreviewPdf([FromQuery] string path) { if (string.IsNullOrWhiteSpace(path)) return BadRequest("Missing PDF path."); // Local file case if (System.IO.File.Exists(path)) { try { var stream = new FileStream(path, FileMode.Open, FileAccess.Read); var fileName = Path.GetFileName(path); Response.Headers["Content-Disposition"] = $"inline; filename=\"{fileName}\""; Response.Headers["Content-Type"] = "application/pdf"; Response.Headers["X-Content-Type-Options"] = "nosniff"; Response.Headers.Remove("X-Frame-Options"); // Optional: allow iframe embedding return File(stream, "application/pdf"); } catch (Exception ex) { Console.WriteLine($"Local file error: {ex.Message}"); return StatusCode(500, "Error reading local file."); } } // Remote file case if (Uri.TryCreate(path, UriKind.Absolute, out Uri? uriResult) && uriResult.Scheme.StartsWith("http")) { try { using var httpClient = new HttpClient(); var response = await httpClient.GetAsync(path); if (!response.IsSuccessStatusCode) return StatusCode((int)response.StatusCode, "Unable to fetch PDF from URL."); var stream = await response.Content.ReadAsStreamAsync(); var fileName = Path.GetFileName(uriResult.LocalPath); Response.Headers["Content-Disposition"] = $"inline; filename=\"{fileName}\""; Response.Headers["Content-Type"] = "application/pdf"; Response.Headers["X-Content-Type-Options"] = "nosniff"; Response.Headers.Remove("X-Frame-Options"); return File(stream, "application/pdf"); } catch (Exception ex) { Console.WriteLine($"Remote fetch error: {ex.Message}"); return StatusCode(500, "Error fetching remote PDF."); } } return BadRequest("Invalid path or URL."); } //public async Task PreviewPdf([FromQuery] string path) //{ // if (!Uri.TryCreate(path, UriKind.Absolute, out Uri? uriResult)) // return BadRequest("Invalid URL."); // // Create a temporary file // var tempFile = Path.Combine(Path.GetTempPath(), Path.GetFileName(uriResult.LocalPath)); // using (var httpClient = new HttpClient()) // using (var response = await httpClient.GetAsync(path, HttpCompletionOption.ResponseHeadersRead)) // { // if (!response.IsSuccessStatusCode) // return StatusCode((int)response.StatusCode, "Unable to fetch remote PDF."); // using (var fs = new FileStream(tempFile, FileMode.Create, FileAccess.Write)) // { // await response.Content.CopyToAsync(fs); // } // } // // Serve the file with range support // return PhysicalFile(tempFile, "application/pdf", Path.GetFileName(tempFile), enableRangeProcessing: true); //} //public async Task PreviewPdf([FromQuery] string path) //{ // if (string.IsNullOrWhiteSpace(path)) // return BadRequest("Missing PDF path."); // // Local file case // if (System.IO.File.Exists(path)) // { // try // { // var stream = new FileStream(path, FileMode.Open, FileAccess.Read); // var fileName = Path.GetFileName(path); // Response.Headers["Content-Disposition"] = $"inline; filename=\"{fileName}\""; // Response.Headers["Content-Type"] = "application/pdf"; // Response.Headers["X-Content-Type-Options"] = "nosniff"; // Response.Headers.Remove("X-Frame-Options"); // Optional: allow iframe embedding // return File(stream, "application/pdf"); // } // catch (Exception ex) // { // Console.WriteLine($"Local file error: {ex.Message}"); // return StatusCode(500, "Error reading local file."); // } // } // // Remote file case // if (Uri.TryCreate(path, UriKind.Absolute, out Uri? uriResult) && uriResult.Scheme.StartsWith("http")) // { // try // { // using var httpClient = new HttpClient(); // var response = await httpClient.GetAsync(path); // if (!response.IsSuccessStatusCode) // return StatusCode((int)response.StatusCode, "Unable to fetch PDF from URL."); // var stream = await response.Content.ReadAsStreamAsync(); // var fileName = Path.GetFileName(uriResult.LocalPath); // Response.Headers["Content-Disposition"] = $"inline; filename=\"{fileName}\""; // Response.Headers["Content-Type"] = "application/pdf"; // Response.Headers["X-Content-Type-Options"] = "nosniff"; // Response.Headers.Remove("X-Frame-Options"); // return File(stream, "application/pdf"); // } // catch (Exception ex) // { // Console.WriteLine($"Remote fetch error: {ex.Message}"); // return StatusCode(500, "Error fetching remote PDF."); // } // } // return BadRequest("Invalid path or URL."); //} //[HttpGet("PreviewPdf")] //public IActionResult PreviewPdf([FromQuery] string path) //{ // if (string.IsNullOrEmpty(path)) // return BadRequest("Path is required."); // var baseFolder = @"\\172.20.254.139\files_internal_laps\ROOT_UAT\LNBPM\150\2547"; // // Normalize and validate the requested path // var fullPath = Path.GetFullPath(path); // //if (!fullPath.StartsWith(baseFolder, StringComparison.OrdinalIgnoreCase)) // //{ // // return BadRequest("Invalid path."); // //} // if (!System.IO.File.Exists(fullPath)) // { // return NotFound(); // } // var fileBytes = System.IO.File.ReadAllBytes(fullPath); // var fileName = Path.GetFileName(fullPath); // return File(fileBytes, "application/pdf", fileName); //} // [Authorize] [HttpGet("GetLotTitles")] public IActionResult GetLotTitles(long jobid) { var titles = new List(); string imgTitle = null; using (var connection = new NpgsqlConnection(_connectionString)) { connection.Open(); using (var command = new NpgsqlCommand( "SELECT lm.title, lm.lotid, tlp.mainpagerange, tlp.supplementrypagerange, lm.totalunits " + "FROM tbljobmaster jm " + "JOIN tbllotmaster lm ON jm.jobid = lm.jobid " + "JOIN tbllotpagedetails tlp ON lm.lotid = tlp.lotid " + "WHERE jm.jobid = @jobid order by lm.lotid", connection)) { command.Parameters.AddWithValue("@jobid", jobid); using (var reader = command.ExecuteReader()) { while (reader.Read()) { var title = reader.IsDBNull(0) ? null : reader.GetString(0); // Handle null for title (string) var lotid = reader.IsDBNull(1) ? 0 : reader.GetInt32(1); // Handle null for lotid (int) var mainpagerange = reader.IsDBNull(2) ? string.Empty : reader.GetString(2); // Handle null for mainpagerange (string) var supplementrypagerange = reader.IsDBNull(3) ? string.Empty : reader.GetString(3); // Handle null for supplementrypagerange (string) var totalunits = reader.IsDBNull(4) ? 0 : reader.GetInt32(4); // Handle null for totalunits (int) // Determine if the title ends with '-img' imgTitle = !string.IsNullOrEmpty(title) && title.EndsWith("-img", StringComparison.OrdinalIgnoreCase) ? "1" : "0"; // Add all the fields to the LotTitleDto object var lotTitleDto = new LotTitleDto { Title = title ?? string.Empty, // Default to empty if null lotid = lotid, mainpagerange = mainpagerange, // Assuming it's a string, not converting to ToString() supplementrypagerange = supplementrypagerange, // Same here ImgTitle = imgTitle, DefaultDocType = 1 // Assuming DefaultDocType is always 1 }; titles.Add(lotTitleDto); } } } } // Log the data to inspect it (if you're debugging) Console.WriteLine($"Returned Titles: {titles.Count} rows"); foreach (var title in titles) { Console.WriteLine($"Title: {title.Title}, Lotid: {title.lotid}, ImgTitle: {title.ImgTitle}"); } return Ok(titles); // Return the list of titles } //public IActionResult GetLotTitles(long jobid) //{ // var titles = new List(); // using (var connection = new NpgsqlConnection(_connectionString)) // { // connection.Open(); // using (var command = new NpgsqlCommand( // "SELECT lm.title,lm.lotid, lm.totalunits " + // "FROM tbljobmaster jm " + // "JOIN tbllotmaster lm ON jm.jobid = lm.jobid " + // "WHERE jm.jobid = @jobid", connection)) // { // command.Parameters.AddWithValue("@jobid", jobid); // Now jobid is long // using (var reader = command.ExecuteReader()) // { // while (reader.Read()) // { // titles.Add(new LotTitleDto // { // Title = reader.GetString(0), // lotid = reader.GetInt32(1), // TotalUnits = reader.GetInt32(2) // }); // } // } // } // } // return Ok(titles); //} [Authorize] [HttpPost("usrstagemove")] public IActionResult usrstagemove([FromBody] lottraninfo data) { try { var _list = oWorkAreaService.usrstagemove(data); return Ok(_list); } catch (Exception ex) { return BadRequest(new { message = ex.Message }); } } [Authorize] [HttpPost("getJobDetails")] public IActionResult getJobDetails([FromBody] object data) { var _list = oWorkAreaService.getJobDetails(data); return Ok(_list); } // [Authorize] [HttpGet("GetAllFilesForDownload")] public List GetAllFilesForDownload(int jobid, int tranid) { List AllFiles = new List(); oGrantFolderPermission.FolderPermission("", "", "", tranid.ToString()); string serverpath = oWorkAreaService.GetServerPath(tranid.ToString(), false, "serverinfo"); serverpath = Path.Combine(serverpath, "IN"); var filetype = oWorkAreaService.getFileTypeInput(tranid.ToString()); var jsonList = new List(); string[] inputfiles; //if (filetype != null) //{ // var i_filetype = JsonConvert.DeserializeObject(filetype.ToString()); // JArray a_filetype = JArray.Parse(i_filetype.ToString()); // foreach (var a in a_filetype) // { // JObject obj_filetype = JObject.Parse(a.ToString()); // jsonList.Add(obj_filetype["filetype"].ToString().ToLower()); // } //} if (jsonList.Count() == 1 && jsonList[0].ToString().Contains(".*")) { inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.AllDirectories).ToArray(); } else { inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.TopDirectoryOnly).Where(x => jsonList.Contains(Path.GetExtension(x.ToString()).ToLower())).ToArray(); if (inputfiles.Length == 0) { inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.AllDirectories).ToArray(); } } foreach (var item in inputfiles) { FileDetails filedetails = new FileDetails(); filedetails.FileName = Path.GetFileName(item); filedetails.FilePath = item; AllFiles.Add(filedetails); } return AllFiles; } // [Authorize] [HttpGet("GetAllOutputFiles")] public List GetAllOutputFiles(int jobid, int tranid) { List AllFiles = new List(); oGrantFolderPermission.FolderPermission("", "", "", tranid.ToString()); string serverpath = oWorkAreaService.GetServerPath(tranid.ToString(), false, "serverinfo"); serverpath = Path.Combine(serverpath, "OUT"); string[] outputfiles = null; if (Directory.Exists(serverpath)) { outputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.AllDirectories); } else { AllFiles = null; } if (outputfiles.Length == 0) { outputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.AllDirectories); } foreach (var item in outputfiles) { FileDetails filedetails = new FileDetails(); filedetails.FileName = Path.GetFileName(item); filedetails.FilePath = item; AllFiles.Add(filedetails); } return AllFiles; } //[Authorize] [HttpPost("DownloadSingleFile")] public IActionResult DownloadSingleFile([FromBody] filedownload fileData) { try { string filePath = fileData.path; string tranid = fileData.jobid.ToString(); // 1️⃣ Validate file path if (string.IsNullOrWhiteSpace(filePath)) return BadRequest("File path is required."); // 2️⃣ Grant folder permission using your existing method oGrantFolderPermission.FolderPermission("", "", "", tranid); // 3️⃣ Check if file exists if (!System.IO.File.Exists(filePath)) return NotFound("File not found on server."); // 4️⃣ Open file as stream for large-file support var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read); // 5️⃣ Detect MIME type string contentType = "application/octet-stream"; var provider = new Microsoft.AspNetCore.StaticFiles.FileExtensionContentTypeProvider(); provider.TryGetContentType(filePath, out contentType); string fileName = Path.GetFileName(filePath); // 6️⃣ Return file as FileStreamResult return File(stream, contentType, fileName); } catch (Exception ex) { // 7️⃣ Log the error var data_input = new { errormessage = "Error: " + ex.Message, createdby = 0, controller = "UploaderApi: Downloader" }; // oCommonRepository.addlog(data_input, "errorlog"); return StatusCode(500, "An error occurred while downloading the file: " + ex.Message); } } // [Authorize] [HttpPost("DownloadInOutFiles")] public IActionResult DownloadInOutFiles(filedownload fileData) { try { string jobid = fileData.jobid.ToString(); string tranid = fileData.tranid.ToString(); string pdid = fileData.pdid.ToString(); string option = fileData.option.ToString(); string jobtitle = fileData.jobtitle.ToString(); string serverpath; serverpath = oWorkAreaService.GetServerPath(tranid, false, "serverinfo"); serverpath = Path.Combine(serverpath, option); string zipname = string.Empty; if (option.IndexOf("IN") != -1 || option.IndexOf("OUT") != -1) { zipname = jobtitle + "_" + tranid + "_" + option + ".zip"; } else { zipname = jobtitle + "_" + tranid + ".zip"; } string[] inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.TopDirectoryOnly).ToArray(); byte[] fileBytes = null; using (System.IO.MemoryStream memoryStream = new System.IO.MemoryStream()) { using (System.IO.Compression.ZipArchive zip = new System.IO.Compression.ZipArchive(memoryStream, System.IO.Compression.ZipArchiveMode.Create, true)) { foreach (string f in inputfiles) { // add the item name to the zip System.IO.Compression.ZipArchiveEntry zipItem = zip.CreateEntry(Path.GetFileName(f) + "." + Path.GetExtension(f)); // add the item bytes to the zip entry by opening the original file and copying the bytes using (System.IO.MemoryStream originalFileMemoryStream = new System.IO.MemoryStream(System.IO.File.ReadAllBytes(f))) { using (System.IO.Stream entryStream = zipItem.Open()) { originalFileMemoryStream.CopyTo(entryStream); } } } } fileBytes = memoryStream.ToArray(); return Ok(new { File = fileBytes }); // return Ok(new { File = File(fileBytes, "application/zip", zipname) }); } } catch (Exception ex) { var data_input = new { errormessage = "Error: " + ex.Message, createdby = 0, controller = "UploaderApi: Downloader" }; // oCommonRepository.addlog(data_input, "errorlog"); return BadRequest(new { message = "File notfound" }); } } // [Authorize] [HttpPost("DownloadSingleOutFile")] public IActionResult DownloadSingleOutFile(filedownload fileData) { try { string type = fileData.type.ToString(); string path = fileData.path.ToString(); string jobid = fileData.jobid.ToString(); byte[] fileBytes = null; oGrantFolderPermission.FolderPermission_vlex("", "", "", jobid.ToString()); fileBytes = System.IO.File.ReadAllBytes(path); string fileName = Path.GetFileName(path); return Ok(new { File = fileBytes }); } catch (Exception ex) { var data_input = new { errormessage = "Error: " + ex.Message, createdby = 0, controller = "UploaderApi: Downloader" }; //oCommonRepository.addlog(data_input, "errorlog"); throw; } } // [Authorize] [HttpPost("RemoveIOFiles")] public object RemoveIOFiles(filedownload fileData) { string path = fileData.path.ToString(); string jobid = fileData.jobid.ToString(); try { oGrantFolderPermission.FolderPermission("", "", "", jobid.ToString()); System.IO.File.Delete(path); var json = new { result = true, filename = Path.GetFileName(path) }; return json; } catch (Exception ex) { var data_input = new { errormessage = "Error: " + ex.Message, createdby = 0, controller = "UploaderApi: Downloader" }; // oCommonRepository.addlog(data_input, "errorlog"); var json = new { result = false, filename = Path.GetFileName(path) }; return json; } } // [Authorize] [HttpGet("GetAllOutFilesForDownload")] public List GetAllOutFilesForDownload(int jobid, int tranid) { List AllFiles = new List(); string serverpath = oWorkAreaService.GetServerPath(tranid.ToString(), false, "serverinfo"); serverpath = Path.Combine(serverpath, "OUT"); var filetype = oWorkAreaService.getFileTypeOutput(tranid.ToString()); var jsonList = new List(); if (filetype != null) { var i_filetype = JsonConvert.DeserializeObject(filetype.ToString()); JArray a_filetype = JArray.Parse(i_filetype.ToString()); foreach (var a in a_filetype) { JObject obj_filetype = JObject.Parse(a.ToString()); jsonList.Add(obj_filetype["filetype"].ToString().ToLower()); } } string[] inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.TopDirectoryOnly) .Where(x => jsonList.Contains(Path.GetExtension(x.ToString()).ToLower())).ToArray(); if (inputfiles.Length == 0) { inputfiles = Directory.GetFiles(serverpath, "*.*", SearchOption.AllDirectories) .Where(x => jsonList.Contains(Path.GetExtension(x.ToString()).ToLower())).ToArray(); } foreach (var item in inputfiles) { FileDetails filedetails = new FileDetails(); filedetails.FileName = Path.GetFileName(item); filedetails.FilePath = item; AllFiles.Add(filedetails); } return AllFiles; } [Authorize] [HttpPost("InsertPageDetails")] public async Task InsertPageDetails([FromBody] PageDetailDto dto) { // If you expect file uploads, use IFormFile // If you're sending a file as part of FormData // public IFormFile File { get; set; } await using var conn = new NpgsqlConnection(_connectionString); await conn.OpenAsync(); try { using (var cmd = new NpgsqlCommand("SELECT upsert_lotpagedetails(@jobid, @lotid, @maincount, @doctype,@updatedby ,@mode)", conn)) { cmd.Parameters.AddWithValue("jobid", dto.JobId); cmd.Parameters.AddWithValue("lotid", dto.LotId); cmd.Parameters.AddWithValue("maincount", dto.MainPage); // cmd.Parameters.AddWithValue("supplementcount", dto.SupplementPage); cmd.Parameters.AddWithValue("doctype", dto.DocType); cmd.Parameters.AddWithValue("updatedby", dto.UpdatedBy); cmd.Parameters.AddWithValue("mode", dto.Mode); // Add this to match the new SP signature // Add this to match the new SP signature // Execute and get the returned lotid var result = cmd.ExecuteScalar(); int returnedLotId = result != null ? Convert.ToInt32(result) : 0; return Ok(new { InsertedLotId = result }); } } catch (Exception Ex) { throw; } } [HttpGet("getreporterkey")] public IActionResult GetReporterKey(int jobid, string jobtitle) { if (string.IsNullOrWhiteSpace(jobtitle)) return BadRequest(new { message = "Job title is required." }); try { // Extract reporter key (everything before "__") int secondUnderscore = jobtitle.IndexOf("_", jobtitle.IndexOf("_") + 1); string reporterKey = secondUnderscore != -1 ? jobtitle.Substring(0, secondUnderscore + 1) : jobtitle; // 🔹 if it ends with "__", trim one underscore if (reporterKey.EndsWith("__")) reporterKey = reporterKey.TrimEnd('_') + "_"; int? reporterId = null; using (var conn = new NpgsqlConnection(_connectionString)) { conn.Open(); using (var cmd = new NpgsqlCommand(@" SELECT reporterid FROM tblvlexreporterkey WHERE reporter_key = @rkey LIMIT 1;", conn)) { cmd.Parameters.AddWithValue("@rkey", reporterKey); var result = cmd.ExecuteScalar(); if (result != null && result != DBNull.Value) reporterId = Convert.ToInt32(result); } } if (!reporterId.HasValue) return NotFound(new { message = $"No reporter found for key '{reporterKey}'." }); return Ok(new { reporterId = reporterId.Value }); } catch (Exception ex) { return StatusCode(500, new { error = "An error occurred while fetching reporter key.", details = ex.Message }); } } [Authorize] [HttpGet("getxmlfile")] public IActionResult getxmlfile(string tranid, string lotid, string userid) { if (string.IsNullOrWhiteSpace(tranid)) return BadRequest("tranid is required."); oGrantFolderPermission.FolderPermission("", "", "", tranid.ToString()); // Validate lotid before using it in query if (!long.TryParse(lotid, out long lotIdNumeric)) return BadRequest("Invalid lotid format. It must be a numeric value."); // Validate and convert userid to int if (!int.TryParse(userid, out int userIdNumeric)) return BadRequest("Invalid userid format. It must be an integer."); using var conn = new NpgsqlConnection(_connectionString); conn.OpenAsync(); string serverpath = oWorkAreaService.GetServerPath(tranid, false, "serverinfo"); serverpath = Path.Combine(serverpath, "OUT"); // 1. Locate file string filePath = Path.Combine(serverpath, "pageinfo.xml"); if (!System.IO.File.Exists(filePath)) return NotFound("File not found for given tranid."); try { // 2. Read XML string xmlContent = System.IO.File.ReadAllText(filePath); var doc = XDocument.Parse(xmlContent); var files = doc.Descendants("file"); foreach (var file in files) { string fileName = file.Attribute("name")?.Value ?? "unknown"; string main = file.Element("main")?.Value ?? ""; string supp = file.Element("supp")?.Value ?? ""; using (var cmd = new NpgsqlCommand("UPDATE tbllotpagedetails SET supplementrypagerange=@supprange, mainpagerange=@mainrange, updatedby=@updatedby, updatedon=NOW() WHERE lotid=@lotid", conn)) { cmd.Parameters.AddWithValue("supprange", supp); cmd.Parameters.AddWithValue("mainrange", main); cmd.Parameters.AddWithValue("updatedby", userIdNumeric); // Convert userid to int cmd.Parameters.AddWithValue("lotid", lotIdNumeric); // Execute the update var result = cmd.ExecuteScalar(); int returnedLotId = result != null ? Convert.ToInt32(result) : 0; } } return Ok("XML processed and values saved to database."); } catch (Exception ex) { return StatusCode(500, $"Error processing XML: {ex.Message}"); } } [HttpPost("SaveLots")] public IActionResult SaveLots([FromBody] List lots) { if (lots == null || lots.Count == 0) return BadRequest("No lots provided."); int jobId = lots[0].jobid; long ocrtranid = 0; int lastTransactionId = 0; bool firstLotUpdated = false; string serverPathIn = string.Empty; int batchId = 0; long nextTransactionId = 0; try { using (var conn = new NpgsqlConnection(_connectionString)) { conn.Open(); // 1️⃣ Get last transaction ID for PDF Analysis using (var command = new NpgsqlCommand(@" SELECT lt.transactionid, lt.batchid FROM tbljobmaster jm JOIN tbljobworkflowdefinition jwm ON jwm.jobid = jm.jobid JOIN tbllottransaction lt ON jwm.jobworkflowid = lt.jobworkflowid WHERE jm.jobid = @jobid AND jwm.stagename_alias = 'PDF Analysis' ORDER BY lt.createdon DESC LIMIT 1", conn)) { command.Parameters.AddWithValue("@jobid", jobId); using (var reader = command.ExecuteReader()) { if (reader.Read()) { lastTransactionId = reader.GetInt32(0); batchId = reader.GetInt32(1); } } } string pdfAnalysisServerPath = oWorkAreaService.GetServerPath(lastTransactionId.ToString(), false, "serverinfo"); // 🔹 Collect ALL split files from all lots List allSplitFiles = new List(); // 2️⃣ Process each lot foreach (var lot in lots) { int lotId = 0; Guid lotKey = Guid.NewGuid(); int latestSequence = -1; // 🔹 Check existing lots int latestLotId = 0; Guid latestLotKey = Guid.Empty; using (var cmdCheck = new NpgsqlCommand(@" SELECT lotid, sequence, lotkey FROM tbllotmaster WHERE jobid = @jobid ORDER BY sequence DESC LIMIT 1;", conn)) { cmdCheck.Parameters.AddWithValue("@jobid", jobId); using (var reader = cmdCheck.ExecuteReader()) { if (reader.Read()) { latestLotId = reader.GetInt32(0); latestSequence = reader.GetInt32(1); latestLotKey = reader.GetGuid(2); } } } using (var tran = conn.BeginTransaction()) { if (latestLotId > 0 && !firstLotUpdated) { // ✅ Update the first lot only once firstLotUpdated = true; lotId = latestLotId; lotKey = latestLotKey; using (var cmdUpdate = new NpgsqlCommand(@" UPDATE tbllotmaster SET title = @title, filename = @filename, updatedon = @updatedon WHERE jobid = @jobid AND lotid = @lotid;", conn, tran)) { string lotNameTitle = $"{lot.Volume} {lot.Edition} {lot.Printpage}"; cmdUpdate.Parameters.AddWithValue("@title", lotNameTitle); cmdUpdate.Parameters.AddWithValue("@filename", lotNameTitle + ".pdf"); cmdUpdate.Parameters.AddWithValue("@updatedon", DateTime.Now); cmdUpdate.Parameters.AddWithValue("@jobid", jobId); cmdUpdate.Parameters.AddWithValue("@lotid", lotId); cmdUpdate.ExecuteNonQuery(); } } else { // ✅ Insert new lot int nextSequence = (latestSequence >= 0 ? latestSequence + 1 : 0); lotKey = Guid.NewGuid(); using (var cmdInsertLot = new NpgsqlCommand("insert_lot_details", conn, tran)) { cmdInsertLot.CommandType = CommandType.StoredProcedure; cmdInsertLot.Parameters.AddWithValue("p_jobid", jobId); string lotNameTitle = $"{lot.Volume} {lot.Edition} {lot.Printpage}"; cmdInsertLot.Parameters.AddWithValue("p_title", lotNameTitle); cmdInsertLot.Parameters.AddWithValue("p_sequence", nextSequence); cmdInsertLot.Parameters.AddWithValue("p_lotkey", lotKey); cmdInsertLot.Parameters.AddWithValue("p_filename", lotNameTitle + ".pdf"); lotId = Convert.ToInt32(cmdInsertLot.ExecuteScalar()); } } // ✅ Insert transaction (common for both update/insert) nextTransactionId = InsertLotTransaction(conn, tran, lotId, lotKey); using (var cmdUpdate = new NpgsqlCommand(@" UPDATE tbllottransaction SET lotstatus = @lotstatus, pickedby = @pickedby WHERE transactionid = @transactionid;", conn, tran)) { cmdUpdate.Parameters.AddWithValue("@lotstatus", "C"); cmdUpdate.Parameters.AddWithValue("@pickedby", 0); cmdUpdate.Parameters.AddWithValue("@transactionid", nextTransactionId); cmdUpdate.ExecuteNonQuery(); } tran.Commit(); } // 3️⃣ Create server paths string serverPath = oWorkAreaService.GetServerPath(nextTransactionId.ToString(), false, "serverinfo") ?? throw new Exception("Server path not found"); if (!Directory.Exists(serverPath)) Directory.CreateDirectory(serverPath); oGrantFolderPermission.FolderPermission_vlex(serverPath, "", "", "", jobId.ToString()); WindowsIdentity newId = oGrantFolderPermission.GetImpartunate().AllowAccesstoServer(); WindowsIdentity.RunImpersonated(newId.AccessToken, () => { WindowsIdentity useri = WindowsIdentity.GetCurrent(); }); serverPathIn = Path.Combine(serverPath, "IN"); string serverPathOut = Path.Combine(serverPath, "OUT"); string pdfAnalysisServerPathIn = Path.Combine(pdfAnalysisServerPath ?? throw new Exception("PDF Analysis path not found"), "IN"); Directory.CreateDirectory(serverPathIn); Directory.CreateDirectory(serverPathOut); Appsettings High_appset = new Appsettings(); //var pdfFiles = Directory.GetFiles(pdfAnalysisServerPathIn, "*.pdf"); var pdfFiles = Directory.GetFiles(pdfAnalysisServerPathIn, "*.pdf", SearchOption.TopDirectoryOnly) .Where(f => Path.GetFileName(f) .EndsWith("_High"+ ".pdf", StringComparison.OrdinalIgnoreCase)) .ToArray(); if (pdfFiles.Length == 0) throw new FileNotFoundException("No PDF files found in PDF Analysis path."); string pdfFile = pdfFiles[0]; // 4️⃣ Run splitter RunSplitterExe(serverPathIn, pdfFile, lot.Range, jobId.ToString()); // 🔹 Collect split files string splitterOutputFolder = Path.Combine(serverPathIn, "splitter_files"); if (!Directory.Exists(splitterOutputFolder)) throw new DirectoryNotFoundException("Splitter output folder not found: " + splitterOutputFolder); var splitFiles = Directory.GetFiles(splitterOutputFolder, "*.pdf"); if (splitFiles.Length == 0) throw new FileNotFoundException("No split PDF files found in splitter output folder."); // 🔹 Rename split files with lot prefix foreach (var file in splitFiles) { string fileNameOnly = Path.GetFileName(file); string newFileName = $"{lot.Volume} {lot.Edition} {lot.Printpage}"; newFileName= string.Concat(newFileName, ".pdf"); string newFilePath = Path.Combine(splitterOutputFolder, newFileName); if (System.IO.File.Exists(newFilePath)) System.IO.File.Delete(newFilePath); System.IO.File.Move(file, newFilePath); } // 🔹 Add renamed files to list for OCR allSplitFiles.AddRange(Directory.GetFiles(splitterOutputFolder, "*.pdf")); // 5️⃣ Insert Vlex Report long reportId = InsertVlexReport(conn, jobId, lotId, lot.Report, lot.Volume, lot.Edition, lot.Issue, lot.Printpage, lot.createadby, 1); } // 🔹 OCR Transaction only once after all lots using (var tran = conn.BeginTransaction()) { ocrtranid = InsertLotTransactionocr(conn, tran, batchId); tran.Commit(); } // 🔹 Create OCR paths string serverocrPath = oWorkAreaService.GetServerPath(ocrtranid.ToString(), false, "serverinfo") ?? throw new Exception("OCR server path not found"); string serverocrPathIn = Path.Combine(serverocrPath, "IN"); string serverocrPathOut = Path.Combine(serverocrPath, "OUT"); Directory.CreateDirectory(serverocrPathIn); Directory.CreateDirectory(serverocrPathOut); // 🔹 Move ALL renamed split files into OCR IN folder foreach (var splitFile in allSplitFiles) { string destinationFilePath = Path.Combine(serverocrPathIn, Path.GetFileName(splitFile)); if (System.IO.File.Exists(destinationFilePath)) System.IO.File.Delete(destinationFilePath); System.IO.File.Move(splitFile, destinationFilePath); } return Ok(new { message = "Lots saved/updated with transactions successfully!", lastTransactionId }); } } catch (Exception ex) { return StatusCode(500, new { error = ex.Message }); } } private long InsertLotTransaction(NpgsqlConnection conn, NpgsqlTransaction tran, int lotId, Guid lotkey) { int jobWorkflowId = 0; long transactionId = 0; // Get the workflow ID using (var cmdWF = new NpgsqlCommand(@" SELECT jwm.jobworkflowid FROM tbllotmaster lm LEFT JOIN tbljobworkflowdefinition jwm ON jwm.jobid = lm.jobid WHERE lm.lotid = @lotid AND jwm.stagename_alias='PDF Splitting'", conn, tran)) { cmdWF.Parameters.AddWithValue("@lotid", lotId); var result = cmdWF.ExecuteScalar(); if (result != null && result != DBNull.Value) jobWorkflowId = Convert.ToInt32(result); } if (jobWorkflowId > 0) { using (var cmdTran = new NpgsqlCommand("insert_lot_transaction_details", conn, tran)) { cmdTran.CommandType = CommandType.StoredProcedure; cmdTran.Parameters.AddWithValue("p_jobworkflowid", jobWorkflowId); cmdTran.Parameters.AddWithValue("p_lotid", lotId); cmdTran.Parameters.AddWithValue("p_lotkey", lotkey); // ✅ ExecuteScalar will return the inserted transaction ID var result = cmdTran.ExecuteScalar(); if (result != null && result != DBNull.Value) transactionId = Convert.ToInt64(result); } } return transactionId; } private long InsertLotTransactionocr(NpgsqlConnection conn, NpgsqlTransaction tran, int lotId) { int jobWorkflowId = 0; long transactionId = 0; // Get the workflow ID using (var cmdWF = new NpgsqlCommand(@" SELECT jwm.jobworkflowid FROM tbllotbatch lm LEFT JOIN tbljobworkflowdefinition jwm ON jwm.jobid = lm.jobid WHERE lm.batchid = @lotid AND jwm.stagename_alias='OCR Conversion'", conn, tran)) { cmdWF.Parameters.AddWithValue("@lotid", lotId); var result = cmdWF.ExecuteScalar(); if (result != null && result != DBNull.Value) jobWorkflowId = Convert.ToInt32(result); } if (jobWorkflowId > 0) { using (var cmdTran = new NpgsqlCommand("insert_lot_transaction_details_batch_only", conn, tran)) { cmdTran.CommandType = CommandType.StoredProcedure; cmdTran.Parameters.AddWithValue("p_jobworkflowid", jobWorkflowId); cmdTran.Parameters.AddWithValue("p_batchid", lotId); // cmdTran.Parameters.AddWithValue("p_lotkey", lotkey); // ✅ ExecuteScalar will return the inserted transaction ID var result = cmdTran.ExecuteScalar(); if (result != null && result != DBNull.Value) transactionId = Convert.ToInt64(result); } } return transactionId; } private void RunSplitterExe(string serverPathIn, string pdfAnalysisServerPathIn, string range, string jobId) { try { // 📌 Read exe path from appsettings.json // string exePath = @""; oGrantFolderPermission.FolderPermission_vlex(serverPathIn, "", "", "", jobId.ToString()); var exePath = Path.Combine(AppContext.BaseDirectory, "Tools", "Vlex_LAPS_PDF_Spiltter.exe"); //var exePath = @"D:\pdfanalysis_vlex\Service\bin\Debug\netcoreapp3.1\Tools\Vlex_LAPS_PDF_Spiltter.exe"; // if (string.IsNullOrEmpty(exePath) || !File.Exists(exePath)) // throw new FileNotFoundException("Splitter EXE path not found in configuration or file missing.", exePath); // 🔒 Apply folder permission before running EXE //oGrantFolderPermission.FolderPermission("", "", "", jobId); // Arguments: input folder, PDF Analysis folder, range var arguments = $"\"{serverPathIn},{pdfAnalysisServerPathIn},{range}\""; var processInfo = new ProcessStartInfo { FileName = exePath, Arguments = arguments, CreateNoWindow = true, UseShellExecute = false, RedirectStandardOutput = true, RedirectStandardError = true }; using (var process = new Process { StartInfo = processInfo }) { process.Start(); string output = process.StandardOutput.ReadToEnd(); string error = process.StandardError.ReadToEnd(); process.WaitForExit(); if (process.ExitCode != 0) { throw new Exception($"Splitter EXE failed. Error: {error}. Output: {output}"); } } } catch (Exception ex) { throw new Exception( $"Failed to run splitter EXE for paths '{serverPathIn}' and '{pdfAnalysisServerPathIn}': {ex.Message}", ex ); } } private long InsertVlexReport( NpgsqlConnection conn, long jobId, long lotId, string report, string volume, string edition, string issue, string printpage, long createdBy, int active) { using (var cmd = new NpgsqlCommand("spvlexreport_insert", conn)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("p_jobid", jobId); cmd.Parameters.AddWithValue("p_lotid", lotId); cmd.Parameters.AddWithValue("p_report", (object?)report ?? DBNull.Value); cmd.Parameters.AddWithValue("p_volume", (object?)volume ?? DBNull.Value); cmd.Parameters.AddWithValue("p_edition", (object?)edition ?? DBNull.Value); cmd.Parameters.AddWithValue("p_issue", (object?)issue ?? DBNull.Value); cmd.Parameters.AddWithValue("p_printpage", (object?)printpage ?? DBNull.Value); cmd.Parameters.AddWithValue("p_createdby", createdBy); cmd.Parameters.AddWithValue("p_active", active); object result = cmd.ExecuteScalar(); return result != null && result != DBNull.Value ? Convert.ToInt64(result) : 0; } } public class MatchedResult { public string FilePath { get; set; } // Add other properties if needed } //public class LotTitleDto //{ // public string Title { get; set; } // public int TotalUnits { get; set; } // public int lotid { get; set; } //} public class PageDetailDto { public int JobId { get; set; } public int LotId { get; set; } public string MainPage { get; set; } // public string SupplementPage { get; set; } public int DocType { get; set; } public int UpdatedBy { get; set; } public string? Mode { get; set; } } public class LotTitleDto { public string Title { get; set; } public int lotid { get; set; } //public int TotalUnits { get; set; } public string mainpagerange { get; set; } public string supplementrypagerange { get; set; } public string ImgTitle { get; set; } // optional, for image title if found public int DefaultDocType { get; set; } = 1; // assuming 1 is main } public class LotDto { public string Report { get; set; } public string Volume { get; set; } public string Edition { get; set; } public string Issue { get; set; } public string Range { get; set; } public string Printpage { get; set; } public string LotName { get; set; } // concatenated in Angular public int jobid { get; set; } public int? LotId { get; set; } public int createadby { get; set; } } } }