using FileHelpers; using FileHelpers.RunTime; using GemBox.Spreadsheet; using LumenWorks.Framework.IO.Csv; using Microsoft.Extensions.Configuration; using Newtonsoft.Json; using Newtonsoft.Json.Linq; using Revalsys.BusinessLogic; using Revalsys.Common; using Revalsys.Common.BusinessLogic; using Revalsys.Common.DataAccess; using Revalsys.Properties; using Revalsys.Security; using Revalsys.TimeZone; using Revalsys.Upload.BusinessLogic; using Revalsys.Upload.DataAccess; using Revalsys.Upload.Properties; using Revalsys.Upload.RevalProperties.Models; using System; using RevalCrypt; using System.Collections.Generic; using System.Data; using System.Globalization; using System.IO.Compression; using System.Linq; using System.Net; using System.Reflection; using System.Text; using System.Text.RegularExpressions; using System.Threading.Tasks; namespace RevalERPFileUploadProcessorWorkerServicve { public class RevalERPFileUploadProcessorService { public RevalERPFileUploadProcessorService(ConfigurationSettingsListDTO objconfigurationSettingsListDTO, IConfiguration configuration) { _ConfigurationSettingsListDTO = objconfigurationSettingsListDTO; _configuration = configuration; } #region Decalred variables public string TimeZone { get; set; } = "India Standard Time"; public int NoOfHours { get; set; } = 5; public int NoOfMinutes { get; set; } = 30; private readonly ILogger _logger; private readonly IConfiguration _configuration; private readonly ConfigurationSettingsListDTO _ConfigurationSettingsListDTO; public string strServiceName = string.Empty; System.Timers.Timer timer = new System.Timers.Timer(); bool IsInProcess = false; string SubDominePath = string.Empty, AzureBlobUrl = string.Empty, BlobImageContainer = string.Empty, StorageAccount = string.Empty, ServerPath = string.Empty, strLocalStoragePath = string.Empty, strBlobLocalStoragePath = string.Empty, strFilePath = string.Empty; bool IsAzureBlobStorage = false, IsLoyaltySubscriptionEnable = false; string SubDomineUrl = string.Empty; #endregion #region properties public bool IsServiceStartOrStop { get { return _ConfigurationSettingsListDTO.IsServiceStartOrStop; } } public int WorkBookPageSize { get { return _ConfigurationSettingsListDTO.WorkBookPageSize; } } public string EncryptionKey { get { return _ConfigurationSettingsListDTO.EncryptionKey; } } #endregion0 #region AssignSiteValues public void AssignSiteValues(SiteDetails objSiteDetails) { TimeZone = objSiteDetails.TimeZone; NoOfHours = objSiteDetails.NoOfHours; NoOfMinutes = objSiteDetails.NoOfMinutes; } #endregion #region GetFileUPloads //************************************************************************************************************* // Purpose : To Get the unProcessed FileUploads, // : Validate the uploaded data with master data and regular expression, // : Save the success data into DB and Physical folder(in respective text file), // : And save the error records into Physical filder(in respective text file) // Layer : UI // Method Name : BindCustomerDetails // Input Parameters : // Return Values : Nothing // ------------------------------------------------------------------------------------------------------------ // Version Author Date Remarks // ----------------------------------------------------------------------------------------------------------- // Sanjay kalwa 1.0.0 20 Aug 2014 Creation //************************************************************************************************************* public async Task GetFileUploadsAsync(List lstSiteListDTO) { string SiteCode = string.Empty; int intSiteId = 0; string strMethodName = "GetFileUploads"; string strSiteConnectionString = string.Empty; string strEncryptionKey = string.Empty; int SiteLogTypeId = 0; int IntErrorLog = 0; DateTime dtCurrentDate = DateTime.MinValue; try { if (lstSiteListDTO != null) { AssignSiteValues(lstSiteListDTO[0]); dtCurrentDate = RevalTimeZone.GetDateByTimezone(DateTime.Now, TimeZone, NoOfHours, NoOfMinutes); SiteCode = lstSiteListDTO[0].SiteCode; intSiteId = lstSiteListDTO[0].SiteId; strSiteConnectionString = lstSiteListDTO[0].ConnectionString; strEncryptionKey = _ConfigurationSettingsListDTO.EncryptionKey; General.CreateCodeLog("Step 2.1", "Before InsertFileUploadFromDownLoadPathAsync method strSiteCode start is", "", strMethodName, lstSiteListDTO, null, strServiceName); await InsertFileUploadFromDownLoadPathAsync(strSiteConnectionString, strEncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO); General.CreateCodeLog("Step 2.2", "After InsertFileUploadFromDownLoadPathAsync method strSiteCode end is", "", strMethodName, lstSiteListDTO, null, strServiceName); } } catch (Exception ex) { General.CreateCodeLog("Step 2.1.1", "Step Exception In GetFileUploads Method" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } finally { //IsInProcess = false; } } #endregion #region ProcessUploadedFiles //************************************************************************************************************* // Purpose : This method is used to // 1) Get the files from download path and store data into tblFileUpload // 2) Get the records from tblFileUPload which are in initiated (new) status. // 3) Get the Upload Template column list based on FileUploadTypeId. // 4) Validate every field in fileupload using UploadTemplateColumnList. // 5) And doing custom validations - DB Level. // 6) Stores the success data into respective success table. // 7) Created error file for respcetive file upload. and update the statuses in tblFileUplaod. // Layer : Service // Method Name : PrcoessUploadedFiles // Input Parameters : Nothing // Return Values : Nothing // ------------------------------------------------------------------------------------------------------------ // Version Author Date Remarks // -----------------------------------------------------------------------------------------------------------r // Rajesh T 1.0.0 17 Nov 2014 Creation // Rajireddy P 1.0.1 27 Oct 2016 added allowing Upload filetype extesions // N.Naresh 1.0.2 04 Nov 2016 added Delimiter and inCustemerDelimiter // Akhila R 1.0.3 10 April 2025 REV0036T11496 Implementing Blob //************************************************************************************************************* public async Task ProcessUploadedFilesAsync(string strConnectionString, string EncryptionKey, int SiteLogTypeId, List lstSiteListDTO) { int IntErrorLog = 0; string strMethodName = MethodBase.GetCurrentMethod()?.Name ?? string.Empty; //Get files from down load path and store into physical path and insert data into tblFileUpload FileUploadServiceListDTO objFileUploadListDTO = null; FileUploadBAL objFileUploadBAL = null; List lstFileUploadListDTO = null; List lstFileUploadTypeListDTO = null; List lstBackgroundServiceListDTO = null; List lstUploadTemplateColumnListDTO = null; List lstColumnMappingDTO = null; SearchListDTO objSearchListDTO = null; UploadTemplateColumnBAL objUploadTemplateColumnBAL = null; string HeaderInformation = string.Empty; string TrailerInformation = string.Empty; bool IsStoreHeaderInSuccess = false; int intHeaderRecordCountStartPosition = 0; int intHeaderRecordCountLength = 0, intBankId = 0, intIOTTypeId = 0, intIOTProviderId = 0, intDestinationFileUploadTypeId = 0; bool IsStoreErrorRecordInSuccess = false; bool IsSuccess = true; string strInvalidReason = string.Empty; string strInvalidHeaderColumns = string.Empty; string strToken = string.Empty; DateTime dtCurrentDate = DateTime.MinValue; try { if (lstSiteListDTO != null) { AssignSiteValues(lstSiteListDTO[0]); dtCurrentDate = RevalTimeZone.GetDateByTimezone(DateTime.Now, TimeZone, NoOfHours, NoOfMinutes); #region To get the unProcessedFileUploads By FileUploadTypeId string strHeaderLineText = string.Empty; string strTrailerLineText = string.Empty; string UploadErrorPrefix = string.Empty; string UploadSuccessPrefix = string.Empty; string UploadErrorPath = string.Empty, BlobUploadErrorPath = string.Empty; string UploadSuccessPath = string.Empty, BlobUploadSuccessPath = string.Empty; string strErrorFileName = string.Empty; string strSuccessFileName = string.Empty; string strErrorfilePath = string.Empty, strBlobErrorfilePath = string.Empty; string strFileExtension = string.Empty; string strMasterColumnNames = string.Empty; string strDateFormatColumnNames = string.Empty; string RemoveErrorColumnNames = string.Empty, RemoveSuccessColumnNames = string.Empty; string FieldDateFormat = string.Empty; string strHeaderDateFormat = "yyyyMMdd";//by Default Julian date format. string FileUniqueID = string.Empty; string ErrorDescriptionColumnName = string.Empty; string RecordIdColumnName = string.Empty; string TrailerRecordText = string.Empty; string HeaderRecordText = string.Empty; string SuccessFileFolderName = string.Empty; string ErrorFileFolderName = string.Empty; string SuccessTableName = string.Empty; string SuccessStoredProcedureName = string.Empty; string strFilePath = string.Empty; string strClassBuilderName = string.Empty; string PasswordGenerationColumnName = string.Empty; string strUserName = string.Empty; string RecordCounterColumnName = string.Empty; char Delimiter = ','; int PasswordLength = 0; int PasswordTypeGeneration = 0; int HeaderDaysCount = 0; int intFileUploadTypeId = 0; int intUplaodTemplateId = 0; int SiteId = 0; string SiteCode = string.Empty; int PageSize = 0; int UploadFileTypeId = 0; int DecimalColumnIndex = 0; Int64 intFileUploadId = 0; Int64 ErrorRecords = 0; Int64 ProcessedRecords = 0; Int64 TotalRecords = 0; IntErrorLog = 0; string strIsErroneousColumnName = string.Empty; bool IsPasswordGenerationRequired = false; bool IsDeleteSuccessRecords = false; bool IsCheckFileUploadHeader = false; bool IsStoreHeader = false; int InvalidFileError = 0; bool IsCustomDelimiter = false; string strSecondServiceName = string.Empty; string strDownlodaFileExtension = string.Empty; DataTable dtResultSet = null; DataSet dsMasterData = null; int intSiteId = 0; int intModuleStorageId = 0, intSplitCount = 0; string strEndTrim = string.Empty; Int64 intWebSiteLanguageId = 0; bool IsAllowValidation = false, IsMandatoryAllField = false, IsValidateDuplicateHeader = false; lstFileUploadListDTO = new List(); objFileUploadListDTO = new FileUploadServiceListDTO(); objFileUploadBAL = new FileUploadBAL(strConnectionString, EncryptionKey, _ConfigurationSettingsListDTO); objFileUploadListDTO.CompanyId = _ConfigurationSettingsListDTO.CompanyId; objFileUploadListDTO.SiteId = lstSiteListDTO[0].SiteId; objFileUploadListDTO.FileUploadStatusId = Convert.ToInt32(General.FileUploadStatus.New); objFileUploadListDTO.ConnectionString = lstSiteListDTO[0].ConnectionString; //objFileUploadListDTO.IsProcessed = false; //objFileUploadListDTO.IsScheduled = true; //objFileUploadListDTO.IsInProcessStatus = false; General.CreateCodeLog("Step 3.2", "Before calling GetUnProcessedFileUploadsByTypeId Method", "", strMethodName, lstSiteListDTO, null, strServiceName); lstFileUploadListDTO = await objFileUploadBAL.GetUnProcessedFileUploadsByTypeIdAsync(objFileUploadListDTO); General.CreateCodeLog("Step 3.3", "After calling GetUnProcessedFileUploadsByTypeId Method", "", strMethodName, lstSiteListDTO, null, strServiceName); if (lstFileUploadListDTO != null && lstFileUploadListDTO.Count > 0) { List lstUploadStorageListDTO = null; foreach (FileUploadServiceListDTO objFileUploadDTO in lstFileUploadListDTO) { try { if (objFileUploadDTO != null && objFileUploadDTO.FileUploadTypeId > 0) { #region Getting ModuleStorage Details intModuleStorageId = Convert.ToInt32(General.ModuleStorageType.InsertFileUploadAPI); // Added by Akhila R on 02 April 2025 objFileUploadDTO.ConnectionString = objFileUploadListDTO.ConnectionString; if (intModuleStorageId > 0) { lstUploadStorageListDTO = new List(); lstUploadStorageListDTO = GetUploadStorageByModuleStorageId(1, 1, 1, intModuleStorageId, objFileUploadDTO.SiteCode, _ConfigurationSettingsListDTO); } #endregion strConnectionString = objFileUploadDTO.ConnectionString; General.CreateCodeLog("Step 3.2", "Before calling GetUnProcessedFileUploadsByTypeId Method", "", strMethodName, lstSiteListDTO, null, strServiceName); lstFileUploadTypeListDTO = await objFileUploadBAL.GetFileUploadsByTypeId(objFileUploadDTO, strConnectionString); General.CreateCodeLog("Step 3.2", "Before calling GetUnProcessedFileUploadsByTypeId Method", "", strMethodName, lstSiteListDTO, null, strServiceName); } InvalidFileError = 0; General.CreateCodeLog("Step 3.4", "After calling GetUnProcessedFileUploadsByTypeId Method - contains result set " + objFileUploadDTO.FileUploadId, "", strMethodName, lstSiteListDTO, null, strServiceName); IsStoreHeaderInSuccess = false; intHeaderRecordCountStartPosition = 0; intHeaderRecordCountLength = 0; strHeaderLineText = string.Empty; strInvalidReason = string.Empty; strTrailerLineText = string.Empty; UploadErrorPrefix = string.Empty; UploadSuccessPrefix = string.Empty; UploadErrorPath = string.Empty; BlobUploadErrorPath = string.Empty; UploadSuccessPath = string.Empty; BlobUploadSuccessPath = string.Empty; strErrorFileName = string.Empty; strSuccessFileName = string.Empty; strErrorfilePath = string.Empty; strBlobErrorfilePath = string.Empty; strFileExtension = string.Empty; strMasterColumnNames = string.Empty; strDateFormatColumnNames = string.Empty; FieldDateFormat = string.Empty; strHeaderDateFormat = "yyyyMMdd";//by Default Julian date format. FileUniqueID = string.Empty; ErrorDescriptionColumnName = string.Empty; RecordIdColumnName = string.Empty; TrailerRecordText = string.Empty; HeaderRecordText = string.Empty; SuccessFileFolderName = string.Empty; ErrorFileFolderName = string.Empty; SuccessTableName = string.Empty; SuccessStoredProcedureName = string.Empty; strFilePath = string.Empty; strClassBuilderName = string.Empty; PasswordGenerationColumnName = string.Empty; strUserName = string.Empty; RecordCounterColumnName = string.Empty; Delimiter = ','; strDownlodaFileExtension = string.Empty; PasswordLength = 0; PasswordTypeGeneration = 0; HeaderDaysCount = 0; intFileUploadTypeId = 0; intUplaodTemplateId = 0; PageSize = 0; UploadFileTypeId = 0; DecimalColumnIndex = 0; intFileUploadId = 0; ErrorRecords = 0; ProcessedRecords = 0; TotalRecords = 0; IsPasswordGenerationRequired = false; IsDeleteSuccessRecords = false; IsCheckFileUploadHeader = false; IsStoreHeader = false; RemoveErrorColumnNames = string.Empty; RemoveSuccessColumnNames = string.Empty; IsCustomDelimiter = false; string strModuleCode = string.Empty; dtResultSet = null; dsMasterData = null; if (lstFileUploadTypeListDTO != null && lstFileUploadTypeListDTO.Count > 0) { strClassBuilderName = lstFileUploadTypeListDTO[0].ClassBuilderName; strHeaderLineText = lstFileUploadTypeListDTO[0].HeaderLineText; strTrailerLineText = lstFileUploadTypeListDTO[0].TrailerLineText; UploadErrorPrefix = lstFileUploadTypeListDTO[0].UploadErrorPrefix; UploadSuccessPrefix = lstFileUploadTypeListDTO[0].UploadSuccessPrfix; //UploadErrorPath = lstFileUploadTypeListDTO[0].UploadErrorPath; //UploadSuccessPath = lstFileUploadTypeListDTO[0].UploadSuccessPath; if (IsAzureBlobStorage) { BlobUploadErrorPath = objFileUploadDTO.ErrorFilePath; BlobUploadSuccessPath = objFileUploadDTO.SuccessFilePath; if (!string.IsNullOrEmpty(SubDominePath) && !string.IsNullOrEmpty(strBlobLocalStoragePath)) { //string strPath = strBlobLocalStoragePath + strModuleCode + "/";//[#sitecode#]/ProcessedFiles/strModuleCode/ UploadErrorPath = SubDominePath + strBlobLocalStoragePath; UploadSuccessPath = SubDominePath + strBlobLocalStoragePath; } } else { UploadErrorPath = objFileUploadDTO.ErrorFilePath; UploadSuccessPath = objFileUploadDTO.SuccessFilePath; } strFileExtension = lstFileUploadTypeListDTO[0].FileExtenstion; HeaderDaysCount = lstFileUploadTypeListDTO[0].HeaderDaysCount; strHeaderDateFormat = lstFileUploadTypeListDTO[0].HeaderDateFormat; FieldDateFormat = lstFileUploadTypeListDTO[0].DateFormat; FileUniqueID = lstFileUploadTypeListDTO[0].FileUniqueID; PasswordLength = lstFileUploadTypeListDTO[0].PasswordLength; PasswordTypeGeneration = lstFileUploadTypeListDTO[0].PasswordTypeGeneration; RecordIdColumnName = lstFileUploadTypeListDTO[0].RecordIdColumnName; TrailerRecordText = lstFileUploadTypeListDTO[0].TrailerRecordText; HeaderRecordText = lstFileUploadTypeListDTO[0].HeaderRecordText; SuccessFileFolderName = lstFileUploadTypeListDTO[0].SuccessFileFolderName; ErrorFileFolderName = lstFileUploadTypeListDTO[0].ErrorFileFolderName; IsPasswordGenerationRequired = lstFileUploadTypeListDTO[0].IsPasswordGenerationRequired; PasswordGenerationColumnName = lstFileUploadTypeListDTO[0].PasswordGenerationColumnName; PageSize = lstFileUploadTypeListDTO[0].PageSize; SuccessTableName = lstFileUploadTypeListDTO[0].SuccessTableName; SuccessStoredProcedureName = lstFileUploadTypeListDTO[0].SuccessStoredProcedureName; IsDeleteSuccessRecords = lstFileUploadTypeListDTO[0].IsDeleteSuccessRecords; intFileUploadTypeId = (int)lstFileUploadTypeListDTO[0].FileUploadTypeId; intUplaodTemplateId = lstFileUploadTypeListDTO[0].UploadTemplateId; // Need clarification IsCheckFileUploadHeader = lstFileUploadTypeListDTO[0].IsCheckFileUploadHeader; RecordCounterColumnName = lstFileUploadTypeListDTO[0].RecordCounterColumnName; ErrorDescriptionColumnName = lstFileUploadTypeListDTO[0].ErrorDescriptionColumnName; Int32.TryParse(lstFileUploadTypeListDTO[0].UploadFileTypeId.ToString(), out UploadFileTypeId); Int32.TryParse(lstFileUploadTypeListDTO[0].DecimalColumnIndex.ToString(), out DecimalColumnIndex); IsStoreErrorRecordInSuccess = lstFileUploadTypeListDTO[0].IsStoreErrorRecordInSuccess; //strStartTrim = lstFileUploadTypeListDTO[0].StartTrimCharacter; //strEndTrim = lstFileUploadTypeListDTO[0].EndTrimCharacter; //RemoveSuccessColumnNames = lstFileUploadTypeListDTO[0].RemoveSuccessColumnNames; //RemoveErrorColumnNames = lstFileUploadTypeListDTO[0].RemoveErrorColumnNames; strDownlodaFileExtension = lstFileUploadTypeListDTO[0].DownLoadFileExtension; IsCustomDelimiter = lstFileUploadTypeListDTO[0].IsCustomDelimiter; intWebSiteLanguageId = lstFileUploadTypeListDTO[0].WebSiteLanguageId; IsAllowValidation = lstFileUploadTypeListDTO[0].IsAllowValidation; IsMandatoryAllField = lstFileUploadTypeListDTO[0].IsMandatoryAllField; intSplitCount = lstFileUploadTypeListDTO[0].SplitCount; IsValidateDuplicateHeader = lstFileUploadTypeListDTO[0].IsValidateDuplicateHeader; intBankId = lstFileUploadListDTO[0].BankId; intIOTTypeId = lstFileUploadListDTO[0].IOTTypeId; intIOTProviderId = lstFileUploadListDTO[0].IOTProviderId; intDestinationFileUploadTypeId = lstFileUploadListDTO[0].DestinationFileUploadTypeId; if (intUplaodTemplateId != Convert.ToInt32(General.UploadTemplate.HRMSEmployeeSalaryUpload) && intUplaodTemplateId != Convert.ToInt32(General.UploadTemplate.EmployeeAttandanceBulkUpload)) { strModuleCode = lstFileUploadTypeListDTO[0].ClassBuilderName.Replace(" ", ""); } if (IsCustomDelimiter) { char.TryParse(lstFileUploadTypeListDTO[0].Delimiter, out Delimiter); } } strSecondServiceName = _ConfigurationSettingsListDTO.SecondServiceName; intFileUploadId = objFileUploadDTO.FileUploadId; strUserName = objFileUploadDTO.CreatedBy; strFilePath = objFileUploadDTO.FilePath; intUplaodTemplateId = objFileUploadDTO.UploadTemplateId; FileUniqueID = objFileUploadDTO.FileUniqueID; bool IsHeaderCheckingSucceess = false; SiteCode = objFileUploadDTO.SiteCode; SiteId = objFileUploadDTO.SiteId; strConnectionString = objFileUploadDTO.ConnectionString; strToken = objFileUploadDTO.Token; // added on 27 Oct 2016 by Rajireddy for for DownloadfileExtention #region upload File type extensions string strExtension = Path.GetExtension(objFileUploadDTO.FilePath); string strFileName = Path.GetFileName(objFileUploadDTO.FilePath); General.CreateCodeLog("Step 3.5", "After calling GetExtension strExtension: " + strExtension, "", strMethodName, lstSiteListDTO, null, strServiceName); bool IsvalidFileExtension = false; string FileExtention = string.Empty; FileExtention = strExtension; FileExtention = "*" + strExtension; if (strDownlodaFileExtension != string.Empty) { List lstDownLoadFileExtension = strDownlodaFileExtension.Split(',').ToList(); if (lstDownLoadFileExtension != null && lstDownLoadFileExtension.Count > 0) { foreach (string strDownLoadFileExtention in lstDownLoadFileExtension) { if (strDownLoadFileExtention.ToLower() == FileExtention.ToLower()) { IsvalidFileExtension = true; if (strExtension.Replace(".", string.Empty).ToLower() == General.UploadFileType.xls.ToString().ToLower()) { UploadFileTypeId = Convert.ToInt32(General.UploadFileType.xls); } else if (strExtension.Replace(".", string.Empty).ToLower() == General.UploadFileType.xlsx.ToString().ToLower()) { UploadFileTypeId = Convert.ToInt32(General.UploadFileType.xlsx); } else if (strExtension.Replace(".", string.Empty).ToLower() == General.UploadFileType.CSV.ToString().ToLower()) { UploadFileTypeId = Convert.ToInt32(General.UploadFileType.CSV); } else if (strExtension.Replace(".", string.Empty).ToLower() == General.UploadFileType.txt.ToString().ToLower()) { UploadFileTypeId = Convert.ToInt32(General.UploadFileType.FixedLength); } break; } } } } #endregion if (IsvalidFileExtension) { General.CreateCodeLog("Step 3.7", "After calling IsvalidFileExtension: " + IsvalidFileExtension, "", strMethodName, lstSiteListDTO, null, strServiceName); bool.TryParse(objFileUploadDTO.IsStoreHeaderInSuccess.ToString(), out IsStoreHeaderInSuccess); Int32.TryParse(objFileUploadDTO.HeaderRecordCountStartPosition.ToString(), out intHeaderRecordCountStartPosition); Int32.TryParse(objFileUploadDTO.HeaderRecordCountLength.ToString(), out intHeaderRecordCountLength); List lstHeaderUploadTemplateColumnList = new List(); #region Starting of process #region Get Upload template columns list by UploadTempalteId if (intFileUploadTypeId > 0) { General.CreateCodeLog("Step 3.8", "Start of GetUploadTemplateColumns getting columns by FileUploadTypeId " + objFileUploadDTO.FileName, "", strMethodName, lstSiteListDTO, null, strServiceName); try { objSearchListDTO = new SearchListDTO(); objSearchListDTO.ProgramCode = SiteCode; objSearchListDTO.CompanyId = _ConfigurationSettingsListDTO.CompanyId; objSearchListDTO.SiteId = SiteId; objSearchListDTO.intRecordId = intUplaodTemplateId; objUploadTemplateColumnBAL = new UploadTemplateColumnBAL(strConnectionString, EncryptionKey, _ConfigurationSettingsListDTO); //lstUploadTemplateColumnListDTO = await objUploadTemplateColumnBAL.GetUploadTemplateColumnsAsync(objSearchListDTO); // Added by Mahesh Gupta on 12 Apr 2025 // Summary: // Determines the upload template column list based on the file upload type. // If the upload type is HRMS Employee Salary Upload, retrieves salary-specific template columns; // otherwise, retrieves the standard upload template columns. if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.HRMSEmployeeSalaryUpload)) { lstUploadTemplateColumnListDTO = await objUploadTemplateColumnBAL.GetSalaryComponenetUploadTemplateColumnsAsync(objSearchListDTO); } else if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.FinanceStatementUpload)) { /// /// Processes the UploadTemplateColumnListDTO by swapping source and destination column names, /// then updates the destination column names based on the mapping data retrieved for the specified bank. /// /// /// Steps: /// 1. Swap the SourceColumnName and DestinationColoumnName in each UploadTemplateColumnListDTO item. /// 2. Retrieve the column mappings for the given BankId from the database. /// 3. Use the retrieved mappings to update the DestinationColoumnName in UploadTemplateColumnListDTO /// based on matching ColumnId. /// /// try { General.CreateCodeLog("Step 3.8", "Before Calling GetUploadTemplateColumnsAsync Method " + objFileUploadDTO.FileName, "", strMethodName, lstSiteListDTO, null, strServiceName); lstUploadTemplateColumnListDTO = await objUploadTemplateColumnBAL.GetUploadTemplateColumnsAsync(objSearchListDTO); General.CreateCodeLog("Step 3.8", "After Calling GetUploadTemplateColumnsAsync Method " + objFileUploadDTO.FileName, "", strMethodName, lstSiteListDTO, null, strServiceName); if (lstUploadTemplateColumnListDTO != null && lstUploadTemplateColumnListDTO.Count > 0) { string drpart1 = string.Empty; string drpart2 = string.Empty; string crpart1 = string.Empty; string crpart2 = string.Empty; string combinedDrCrColumnName = string.Empty; // Step 1: Swap Source and Destination column names in the list for each column foreach (var col in lstUploadTemplateColumnListDTO) { var temp = col.SourceColumnName; col.SourceColumnName = col.DestinationColoumnName; col.DestinationColoumnName = temp; } //int BankId = 3; General.CreateCodeLog("Step 3.8", "Before Calling GetStatementMapperByBankIdAsync Method " + objFileUploadDTO.FileName, "", strMethodName, lstSiteListDTO, null, strServiceName); if (intBankId > 0) { lstColumnMappingDTO = await objUploadTemplateColumnBAL.GetStatementMapperByBankIdAsync(intBankId); } // Step 2: Retrieve the mapping data from DB for the specified bank General.CreateCodeLog("Step 3.8", "After Calling GetStatementMapperByBankIdAsync Method " + objFileUploadDTO.FileName, "", strMethodName, lstSiteListDTO, null, strServiceName); if (lstColumnMappingDTO != null && lstColumnMappingDTO.Count > 0) { var mappingDict = lstColumnMappingDTO.ToDictionary(m => m.ColumnId, m => m.DestinationColoumnName); // Step 4: Update DestinationColoumnName using DB mapping foreach (var col in lstUploadTemplateColumnListDTO) { if (mappingDict.TryGetValue(col.ColumnId, out string mappedDestName)) { // string normalized = mappedDestName.Trim().ToLower(); string original = mappedDestName.Trim(); // Debit pattern var debitPattern = new Regex( @"\b(withdrawal|debit|dr\.?|amt[\s_]?debited|purchase|payment|transfer\s+to|upi\s+payment|atm\s+withdrawal|emi\s+debit|charges|fee[s]?|auto[\s-]?debit)(?:\s+amount\s*\(inr\))?[\s:#|\\/]*₹?\s*(?[\d,]+(\.\d{1,2})?)", RegexOptions.IgnoreCase ); // Credit pattern var creditPattern = new Regex( @"\b(deposit|credit|cr\.?|amt[\s_]?credited|salary\s+credit|interest\s+credit|refund|reversal\s+credit|upi\s+credit|neft\s+credit|imps\s+credit|ft\s+credit)(?:\s+amount\s*\(inr\))?[\s:#|\\/]*₹?\s*(?[\d,]+(\.\d{1,2})?)", RegexOptions.IgnoreCase ); var debitMatch = debitPattern.Match(original); var creditMatch = creditPattern.Match(original); if (debitMatch.Success) { drpart1 = debitMatch.Groups[1].Value.Trim(); drpart2 = debitMatch.Groups["value"].Value.Trim(); col.DestinationColoumnName = drpart2; // preserves original case combinedDrCrColumnName = drpart2; } else if (creditMatch.Success) { crpart1 = creditMatch.Groups[1].Value.Trim(); crpart2 = creditMatch.Groups["value"].Value.Trim(); col.DestinationColoumnName = $"{crpart1}/{drpart1}"; combinedDrCrColumnName = col.DestinationColoumnName; } else { col.DestinationColoumnName = mappedDestName; } } } } } } catch (Exception ex) { await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, true, TotalRecords, ProcessedRecords, ErrorRecords, ex.Message, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); General.CreateCodeLog("Step 3.8.1", "Exception in GetUploadTemplateColumns " + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } else if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTDataUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTPeopleCountUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTIFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTPFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTTDSUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTVolumeUpload)) { try { General.CreateCodeLog("Step 3.8", "Before Calling GetUploadTemplateColumnsAsync Method " + objFileUploadDTO.FileName, "", strMethodName, lstSiteListDTO, null, strServiceName); lstUploadTemplateColumnListDTO = await objUploadTemplateColumnBAL.GetUploadTemplateColumnsAsync(objSearchListDTO); General.CreateCodeLog("Step 3.8", "After Calling GetUploadTemplateColumnsAsync Method " + objFileUploadDTO.FileName, "", strMethodName, lstSiteListDTO, null, strServiceName); // Step 1: Swap Source and Destination column names in the list for each column foreach (var col in lstUploadTemplateColumnListDTO) { var temp = col.SourceColumnName; col.SourceColumnName = col.DestinationColoumnName; col.DestinationColoumnName = temp; } if (lstUploadTemplateColumnListDTO != null && lstUploadTemplateColumnListDTO.Count > 0) { if (intUplaodTemplateId > 0) { General.CreateCodeLog("Step 3.8", "Before Calling GetGetIOTUploadMapperByIdAsync Method " + objFileUploadDTO.FileName, "", strMethodName, lstSiteListDTO, null, strServiceName); lstColumnMappingDTO = await objUploadTemplateColumnBAL.GetIOTUploadMapperByIdAsync(intUplaodTemplateId); General.CreateCodeLog("Step 3.8", "After Calling GetGetIOTUploadMapperByIdAsync Method " + objFileUploadDTO.FileName, "", strMethodName, lstSiteListDTO, null, strServiceName); } if (lstColumnMappingDTO != null && lstColumnMappingDTO.Count > 0) { // Step 1: Build dictionary from mapping var mappingDict = lstColumnMappingDTO .GroupBy(m => m.ColumnId) .ToDictionary(g => g.Key, g => g.FirstOrDefault()); // Step 2: Filter list to only valid ColumnIds with non-empty DestinationColoumnName lstUploadTemplateColumnListDTO = lstUploadTemplateColumnListDTO .Where(col => mappingDict.ContainsKey(col.ColumnId) && !string.IsNullOrWhiteSpace(mappingDict[col.ColumnId].DestinationColoumnName) ) .ToList(); // Step 3: Assign DestinationColoumnName from mapping foreach (var col in lstUploadTemplateColumnListDTO) { if (mappingDict.TryGetValue(col.ColumnId, out var map)) { col.DestinationColoumnName = map.DestinationColoumnName?.Trim(); } } // Step 4: Final clean-up lstUploadTemplateColumnListDTO = lstUploadTemplateColumnListDTO .Where(x => !string.IsNullOrWhiteSpace(x.DestinationColoumnName)) .ToList(); } } } catch (Exception ex) { General.CreateCodeLog("Step 3.8.1", "Exception in GetUploadTemplateColumns " + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } General.CreateCodeLog("Step 3.8", "After Calling GetGetIOTUploadMapperByIdAsync Method " + objFileUploadDTO.FileName, "", strMethodName, lstSiteListDTO, null, strServiceName); } else { lstUploadTemplateColumnListDTO = await objUploadTemplateColumnBAL.GetUploadTemplateColumnsAsync(objSearchListDTO); } } catch (Exception ex) { //IsSuccess = false; await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, true, TotalRecords, ProcessedRecords, ErrorRecords, ex.Message, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); General.CreateCodeLog("Step 3.8.1", "Exception in GetUploadTemplateColumns " + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } finally { General.CreateCodeLog("Step 3.8.2", "End of GetUploadTemplateColumns Method Calling", "", strMethodName, lstSiteListDTO, null, strServiceName); objUploadTemplateColumnBAL = null; objSearchListDTO = null; } } #endregion if (lstUploadTemplateColumnListDTO != null && lstUploadTemplateColumnListDTO.Count > 0) { try { #region Convert to CSV file if (UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xls) || UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xlsx) || UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xls_xlsx)) { General.CreateCodeLog("Step 3.9", "Start of Convert of file type xls or xlsx into csv.", "", strMethodName, lstSiteListDTO, null, strServiceName); var workbook = new ExcelFile(); string strExt = string.Empty; string strNewFilePath = string.Empty; if (lstUploadStorageListDTO != null && lstUploadStorageListDTO.Count > 0) { if (IsAzureBlobStorage) { #region Assigning ModuleCode if (intUplaodTemplateId > 0) { if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.HRMSEmployeeSalaryUpload) && intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.EmployeeAttandanceBulkUpload)) { if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.HRMSEmployeeSalaryUpload)) { strModuleCode = General.UploadTemplate.HRMSEmployeeSalaryUpload.ToString(); } else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.EmployeeAttandanceBulkUpload)) { strModuleCode = General.UploadTemplate.EmployeeAttandanceBulkUpload.ToString(); } #region olg module //if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.ProductUpload)) //{ // strModuleCode = General.UploadTemplate.ProductUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.OrderUpload)) //{ // strModuleCode = General.UploadTemplate.OrderUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.CustomerUpload)) //{ // strModuleCode = General.UploadTemplate.CustomerUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.PriceUpload)) //{ // strModuleCode = General.UploadTemplate.PriceUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.InventoryUpload)) //{ // strModuleCode = General.UploadTemplate.InventoryUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.TransactionUpload)) //{ // strModuleCode = General.UploadTemplate.TransactionUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.EmployeeRelativeUpload)) //{ // strModuleCode = General.UploadTemplate.EmployeeRelativeUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.ProjectEstimationUpload)) //{ // strModuleCode = General.UploadTemplate.ProjectEstimationUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.SalaryComponentUpload)) //{ // strModuleCode = General.UploadTemplate.SalaryComponentUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.EmployeeUpload)) //{ // strModuleCode = General.UploadTemplate.EmployeeUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.EmployeeLeaveTemplateUpload)) //{ // strModuleCode = General.UploadTemplate.EmployeeLeaveTemplateUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.BranchUpload)) //{ // strModuleCode = General.UploadTemplate.BranchUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.BankUpload)) //{ // strModuleCode = General.UploadTemplate.BankUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.EmployeeWorkLocationUpload)) //{ // strModuleCode = General.UploadTemplate.EmployeeWorkLocationUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.EmployeeDesignationUpload)) //{ // strModuleCode = General.UploadTemplate.EmployeeDesignationUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.EmployeeDepartmentUpload)) //{ // strModuleCode = General.UploadTemplate.EmployeeDepartmentUpload.ToString(); //} //else if (intUplaodTemplateId == Convert.ToInt32(General.UploadTemplate.HealthInsuranceUpload)) //{ // strModuleCode = General.UploadTemplate.HealthInsuranceUpload.ToString(); //} #endregion olg module } } #endregion #region new code to get the file form the blob path string strBlobFilePath = await ReadFile(strFilePath, strFileName, SiteCode, lstUploadStorageListDTO); //string downloadedFilePath = DownloadFile(strFileName, "12345GUID", 0, 1, 1, 1, strModuleCode, lstUploadStorageListDTO, SiteCode); #endregion if (!string.IsNullOrEmpty(strBlobFilePath)) { strFilePath = strBlobFilePath; if (UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xls)) { strNewFilePath = strFilePath.Replace(".xls", ".csv"); workbook.LoadXls(strFilePath, XlsOptions.None); General.CreateCodeLog("Step 3.10", "Converting xls to csv", "", strMethodName, lstSiteListDTO, null, strServiceName); } else if (UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xlsx)) { strNewFilePath = strFilePath.Replace(".xlsx", ".csv"); workbook.LoadXlsx(strFilePath, XlsxOptions.PreserveKeepOpen); General.CreateCodeLog("Step 3.11", "Converting xlsx to csv", "", strMethodName, lstSiteListDTO, null, strServiceName); } else if (UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xls_xlsx)) { General.CreateCodeLog("Step 3.12", "Converting xlsx or xls to csv", "", strMethodName, lstSiteListDTO, null, strServiceName); strExt = Path.GetExtension(strFilePath); if (strExt.ToLower() == ".xls" || strExt.ToLower() == ".xlsx") { if (strExt.ToLower() == ".xls") { strNewFilePath = strFilePath.Replace(".xls", ".csv"); workbook.LoadXls(strFilePath, XlsOptions.PreserveWorksheetRecords); } else if (strExt.ToLower() == ".xlsx") { strNewFilePath = strFilePath.Replace(".xlsx", ".csv"); workbook.LoadXlsx(strFilePath, XlsxOptions.PreserveMakeCopy); } } } workbook.CsvAlwaysQuoteValues = false; //string strFileName = //"D:\\Live\\ThirdPartyEasyRewardz.Revalweb.com\\Docs\Uploads\Customer\CustomerTest3.csv";//Uploads/SampleExcel.xlsx string strHeader = string.Empty; //if (workbook.Worksheets.ActiveWorksheet.Rows.Count <= 1) //{ // IsCheckFileUploadHeader = false; // IsHeaderCheckingSucceess = false; // strInvalidReason = "There are no records in the imported file!"; // UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.InValidFile), true, false, 0, 0, 0, strInvalidReason); //} string strHeaderName = string.Empty; if (workbook.Worksheets.ActiveWorksheet.Rows != null && workbook.Worksheets.ActiveWorksheet.Rows.Count > 0) { try { General.CreateCodeLog("Step 3.13", "Starting of reading WorkSheet to Workbook And Removing the Spaces in Headers.", "", strMethodName, lstSiteListDTO, null, strServiceName); foreach (ExcelRow row in workbook.Worksheets.ActiveWorksheet.Rows) { CellRange range = workbook.Worksheets.ActiveWorksheet.GetUsedCellRange(true); int ColumnCount = 0; ColumnCount = range.LastColumnIndex; for (int Counter = 0; Counter <= ColumnCount; Counter++) { //Reading Header if (row.Index == 0) { if (workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value != null && workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value.ToString() != string.Empty) { try { strHeaderName = workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value.ToString().Trim(); UploadTemplateColumnListDTO objHeaderList = (from objUploadTemplateColumnListDTO in lstUploadTemplateColumnListDTO where objUploadTemplateColumnListDTO.Tag.ToLower() == strHeaderName.ToLower() select objUploadTemplateColumnListDTO).FirstOrDefault(); //CreateCodeLogProgramWise("Step - 26.1", "Tag" + objHeaderList.Tag); //CreateCodeLogProgramWise("Step - 26.2", "strHeaderName" + strHeaderName); //CreateCodeLogProgramWise("Step - 26.3", "ColumnCount " + ColumnCount); if (objHeaderList != null) { General.CreateCodeLog("Step 3.14", "objHeaderList ", "", strMethodName, lstSiteListDTO, null, strServiceName); lstHeaderUploadTemplateColumnList.Add(objHeaderList); } objHeaderList = null; if (strHeaderName != string.Empty) { //CreateCodeLogProgramWise("Step - 26.5", "strHeaderName" + strHeaderName); strHeaderName = strHeaderName.Replace(" ", string.Empty); strHeaderName = strHeaderName.Replace("_", string.Empty); workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value = strHeaderName; strHeaderName = string.Empty; } } catch (Exception ex) { //IsSuccess = false; //await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, true, TotalRecords, ProcessedRecords, ErrorRecords, ex.Message, strConnectionString, EncryptionKey, SiteCode, SiteLogTypeId, IntErrorLog, intSiteId); await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, true, TotalRecords, ProcessedRecords, ErrorRecords, ex.Message, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); General.CreateCodeLog("Step 3.15", "Header validation Exception" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } General.CreateCodeLog("Step 3.16", "After ReadingHEader ", "", strMethodName, lstSiteListDTO, null, strServiceName); if (workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value != null && workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value.ToString() != string.Empty) { workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value = workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value.ToString().Replace("\"", "\'"); } } } } catch (Exception ex) { IsSuccess = false; General.CreateCodeLog("Step 3.17", "Exception in reading WorkSheet to Workbook" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } finally { } } if (IsCustomDelimiter) { workbook.SaveCsv(strNewFilePath, Delimiter); } else { workbook.SaveCsv(strNewFilePath, CsvType.CommaDelimited); } workbook.ClosePreservedXlsx(); General.CreateCodeLog("Step 3.18", "End of saving xls or xlsx to csv", "", strMethodName, lstSiteListDTO, null, strServiceName); strFilePath = strNewFilePath; //UploadFileTypeId = Convert.ToInt32(General.UploadFileType.CSV); } } else { if (UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xls)) { strNewFilePath = strFilePath.Replace(".xls", ".csv"); workbook.LoadXls(strFilePath, XlsOptions.None); General.CreateCodeLog("Step 3.10", "Converting xls to csv", "", strMethodName, lstSiteListDTO, null, strServiceName); } else if (UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xlsx)) { strNewFilePath = strFilePath.Replace(".xlsx", ".csv"); workbook.LoadXlsx(strFilePath, XlsxOptions.PreserveKeepOpen); General.CreateCodeLog("Step 3.11", "Converting xlsx to csv", "", strMethodName, lstSiteListDTO, null, strServiceName); } else if (UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xls_xlsx)) { General.CreateCodeLog("Step 3.12", "Converting xlsx or xls to csv", "", strMethodName, lstSiteListDTO, null, strServiceName); strExt = Path.GetExtension(strFilePath); if (strExt.ToLower() == ".xls" || strExt.ToLower() == ".xlsx") { if (strExt.ToLower() == ".xls") { strNewFilePath = strFilePath.Replace(".xls", ".csv"); workbook.LoadXls(strFilePath, XlsOptions.PreserveWorksheetRecords); } else if (strExt.ToLower() == ".xlsx") { strNewFilePath = strFilePath.Replace(".xlsx", ".csv"); workbook.LoadXlsx(strFilePath, XlsxOptions.PreserveMakeCopy); } } } workbook.CsvAlwaysQuoteValues = false; //string strFileName = //"D:\\Live\\ThirdPartyEasyRewardz.Revalweb.com\\Docs\Uploads\Customer\CustomerTest3.csv";//Uploads/SampleExcel.xlsx string strHeader = string.Empty; //if (workbook.Worksheets.ActiveWorksheet.Rows.Count <= 1) //{ // IsCheckFileUploadHeader = false; // IsHeaderCheckingSucceess = false; // strInvalidReason = "There are no records in the imported file!"; // UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.InValidFile), true, false, 0, 0, 0, strInvalidReason); //} string strHeaderName = string.Empty; if (workbook.Worksheets.ActiveWorksheet.Rows != null && workbook.Worksheets.ActiveWorksheet.Rows.Count > 0) { try { General.CreateCodeLog("Step 3.13", "Starting of reading WorkSheet to Workbook And Removing the Spaces in Headers.", "", strMethodName, lstSiteListDTO, null, strServiceName); foreach (ExcelRow row in workbook.Worksheets.ActiveWorksheet.Rows) { #region Old Code //foreach (ExcelColumn Column in workbook.Worksheets.ActiveWorksheet.Columns) //changed here to loop through columns //{ // if (workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Column.Index].Value != null && workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Column.Index].Value.ToString() != string.Empty) // { // //Reading header and remove spaces in the header // if (row.Index == 0) // { // strHeader = workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Column.Index].Value.ToString(); // if (strHeader != string.Empty) // { // strHeader = strHeader.Replace(" ", string.Empty); // strHeader = strHeader.Replace("_", string.Empty); // workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Column.Index].Value = strHeader; // strHeader = string.Empty; // } // } // else // { // workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Column.Index].Value = workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Column.Index].Value.ToString(); // } // } //} #endregion Old Code CellRange range = workbook.Worksheets.ActiveWorksheet.GetUsedCellRange(true); int ColumnCount = 0; ColumnCount = range.LastColumnIndex; for (int Counter = 0; Counter <= ColumnCount; Counter++) { //Reading Header if (row.Index == 0) { if (workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value != null && workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value.ToString() != string.Empty) { try { strHeaderName = workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value.ToString().Trim(); UploadTemplateColumnListDTO objHeaderList = (from objUploadTemplateColumnListDTO in lstUploadTemplateColumnListDTO where objUploadTemplateColumnListDTO.Tag.ToLower() == strHeaderName.ToLower() select objUploadTemplateColumnListDTO).FirstOrDefault(); //CreateCodeLogProgramWise("Step - 26.1", "Tag" + objHeaderList.Tag); //CreateCodeLogProgramWise("Step - 26.2", "strHeaderName" + strHeaderName); //CreateCodeLogProgramWise("Step - 26.3", "ColumnCount " + ColumnCount); if (objHeaderList != null) { General.CreateCodeLog("Step 3.14", "objHeaderList ", "", strMethodName, lstSiteListDTO, null, strServiceName); lstHeaderUploadTemplateColumnList.Add(objHeaderList); } objHeaderList = null; if (strHeaderName != string.Empty) { //CreateCodeLogProgramWise("Step - 26.5", "strHeaderName" + strHeaderName); strHeaderName = strHeaderName.Replace(" ", string.Empty); strHeaderName = strHeaderName.Replace("_", string.Empty); workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value = strHeaderName; strHeaderName = string.Empty; } } catch (Exception ex) { //IsSuccess = false; await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, true, TotalRecords, ProcessedRecords, ErrorRecords, ex.Message, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); General.CreateCodeLog("Step 3.15", "Header validation Exception" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } General.CreateCodeLog("Step 3.16", "After ReadingHEader ", "", strMethodName, lstSiteListDTO, null, strServiceName); if (workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value != null && workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value.ToString() != string.Empty) { workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value = workbook.Worksheets.ActiveWorksheet.Cells[row.Index, Counter].Value.ToString().Replace("\"", "\'"); } } } } catch (Exception ex) { IsSuccess = false; General.CreateCodeLog("Step 3.17", "Exception in reading WorkSheet to Workbook" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } finally { } } if (IsCustomDelimiter) { workbook.SaveCsv(strNewFilePath, Delimiter); } else { workbook.SaveCsv(strNewFilePath, CsvType.CommaDelimited); } workbook.ClosePreservedXlsx(); General.CreateCodeLog("Step 3.18", "End of saving xls or xlsx to csv", "", strMethodName, lstSiteListDTO, null, strServiceName); strFilePath = strNewFilePath; //UploadFileTypeId = Convert.ToInt32(General.UploadFileType.CSV); } } } #endregion Converting to CSV file. } catch (Exception ex) { //IsSuccess = false; await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, true, TotalRecords, ProcessedRecords, ErrorRecords, ex.Message, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); General.CreateCodeLog("Step 3.9.1", " Exception : " + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } //End here Converting .xls and .xlsx to .csv if (IsSuccess) { #region Validate Headers if (IsCheckFileUploadHeader) { General.CreateCodeLog("Step 3.19", "Start of ValidateHeaders Method Calling", "", strMethodName, lstSiteListDTO, null, strServiceName); try { IsHeaderCheckingSucceess = await ValidateHeadersAsync(strFilePath, lstUploadTemplateColumnListDTO, intFileUploadId, intFileUploadTypeId, strUserName, IsCustomDelimiter, Delimiter, strConnectionString, EncryptionKey, SiteLogTypeId, strInvalidHeaderColumns, IntErrorLog, lstSiteListDTO, SiteId, IsMandatoryAllField, IsValidateDuplicateHeader); } catch (Exception ex) { IsSuccess = false; General.CreateCodeLog("Step 3.20", "exception in ValidateHeaders catch block" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } else { IsHeaderCheckingSucceess = true; //Header checking success } #endregion Validate Headers if (IsHeaderCheckingSucceess && IsSuccess) { //Update tblFileUpload Status as Started strInvalidReason = null; await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Processing_Initiated), false, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); //Reading text file convert into datatable General.CreateCodeLog("Step - 36", "Start of Reading text file convert into datatable", "", strMethodName, lstSiteListDTO, null, strServiceName); try { dtResultSet = await ReadTextFileAsync(strClassBuilderName, strFilePath, strHeaderLineText, strTrailerLineText, intFileUploadTypeId, intFileUploadId, lstUploadTemplateColumnListDTO, strUserName, HeaderDaysCount, strHeaderDateFormat, RecordIdColumnName, ErrorDescriptionColumnName, RecordCounterColumnName, IsPasswordGenerationRequired, PasswordGenerationColumnName, UploadFileTypeId, IsStoreErrorRecordInSuccess, strEndTrim, Delimiter, strConnectionString, EncryptionKey, SiteLogTypeId, HeaderInformation, TrailerInformation, IsStoreHeaderInSuccess, intHeaderRecordCountStartPosition, intHeaderRecordCountLength, strInvalidReason, IntErrorLog, lstSiteListDTO, SiteId, intFileUploadTypeId); } catch (Exception ex) { General.CreateCodeLog("Step - 36-a", "exception in ReadTextFile catch block" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } if (dtResultSet != null) { #region Validate the DataTable General.CreateCodeLog("Step - 45", "After reading ReadTextFile - contains result set", "", strMethodName, lstSiteListDTO, null, strServiceName); TotalRecords = dtResultSet.Rows.Count; #region Add MasterId to Result set data table //Aadding Master Id Columns to result set List lstMasterUploadTemplateColumnListDTO = null; UploadTemplateColumnListDTO objMasterUploadTemplateColumnList = null; lstMasterUploadTemplateColumnListDTO = (from objList in lstUploadTemplateColumnListDTO where objList.IsMaster == true select objList).ToList(); if (lstMasterUploadTemplateColumnListDTO != null) { foreach (UploadTemplateColumnListDTO objMasterUploadTemplateColumnListDTO in lstMasterUploadTemplateColumnListDTO) { try { if (!(dtResultSet.Columns.Contains(objMasterUploadTemplateColumnListDTO.MasterColumnIdName))) { strMasterColumnNames = strMasterColumnNames + "," + objMasterUploadTemplateColumnListDTO.MasterColumnIdName; if (objMasterUploadTemplateColumnListDTO.MasterDataType.ToLower() == General.IntegerDataType.ToString().ToLower()) { dtResultSet.Columns.Add(objMasterUploadTemplateColumnListDTO.MasterColumnIdName, typeof(Int32)); } else if (objMasterUploadTemplateColumnListDTO.MasterDataType.ToLower() == General.BigIntDataType.ToString().ToLower()) { dtResultSet.Columns.Add(objMasterUploadTemplateColumnListDTO.MasterColumnIdName, typeof(Int64)); } else if (objMasterUploadTemplateColumnListDTO.MasterDataType.ToLower() == General.StringDataType.ToString().ToLower()) { dtResultSet.Columns.Add(objMasterUploadTemplateColumnListDTO.MasterColumnIdName, typeof(string)); } // dtResultSet.Columns.Add(objMasterUploadTemplateColumnListDTO.MasterColumnIdName, typeof(Int32)); dtResultSet.AcceptChanges(); #region Add Master column names to UploadTemplate Column List to List objMasterUploadTemplateColumnList = new UploadTemplateColumnListDTO(); objMasterUploadTemplateColumnList.SourceColumnName = objMasterUploadTemplateColumnListDTO.MasterColumnIdName; objMasterUploadTemplateColumnList.DestinationColoumnName = objMasterUploadTemplateColumnListDTO.MasterColumnIdName; objMasterUploadTemplateColumnList.IsClientColumn = true; objMasterUploadTemplateColumnList.IsStoreInSuccessTable = true; objMasterUploadTemplateColumnList.IsMasterColumnName = true; lstUploadTemplateColumnListDTO.Add(objMasterUploadTemplateColumnList); objMasterUploadTemplateColumnList = null; #endregion } } catch (Exception ex) { General.CreateCodeLog("Step - 45", "Exception in reading lstMasterUploadTemplateColumnListDTO" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } lstMasterUploadTemplateColumnListDTO = null; } //Aadding DateFormat Columns to result set General.CreateCodeLog("Step - 46", "Before Aadding DateFormat Columns to result set", "", strMethodName, lstSiteListDTO, null, strServiceName); List lstDateUploadTemplateColumnListDTO = null; UploadTemplateColumnListDTO objTempDateUploadTemplateColumnList = null; lstDateUploadTemplateColumnListDTO = (from objList in lstUploadTemplateColumnListDTO where objList.IsDateField == true && objList.DateFormat != string.Empty && objList.DateColumnName != string.Empty select objList).ToList(); //lstDateUploadTemplateColumnListDTO = (from objList in lstUploadTemplateColumnListDTO where objList.IsDateField == true && objList.DateColumnName != string.Empty select objList).ToList(); if (lstDateUploadTemplateColumnListDTO != null) { foreach (UploadTemplateColumnListDTO objDateUploadTemplateColumnList in lstDateUploadTemplateColumnListDTO) { try { General.CreateCodeLog("Step - 47", "While Aadding DateFormat Columns to result set(for each loop)", "", strMethodName, lstSiteListDTO, null, strServiceName); if (!(dtResultSet.Columns.Contains(objDateUploadTemplateColumnList.DateColumnName))) { strDateFormatColumnNames = strDateFormatColumnNames + "," + objDateUploadTemplateColumnList.DateColumnName; dtResultSet.Columns.Add(objDateUploadTemplateColumnList.DateColumnName, typeof(DateTime)); dtResultSet.AcceptChanges(); #region Add DateFormat Columns to UploadTemplate Column LIst objTempDateUploadTemplateColumnList = new UploadTemplateColumnListDTO(); objTempDateUploadTemplateColumnList.SourceColumnName = objDateUploadTemplateColumnList.DateColumnName; objTempDateUploadTemplateColumnList.DestinationColoumnName = objDateUploadTemplateColumnList.DateColumnName; objTempDateUploadTemplateColumnList.IsClientColumn = true; objTempDateUploadTemplateColumnList.IsStoreInSuccessTable = true; objTempDateUploadTemplateColumnList.IsMasterColumnName = true; lstUploadTemplateColumnListDTO.Add(objTempDateUploadTemplateColumnList); objTempDateUploadTemplateColumnList = null; #endregion } } catch (Exception ex) { General.CreateCodeLog("Step - 48", "exception in Aadding DateFormat Columns to result set(for each loop)" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } lstDateUploadTemplateColumnListDTO = null; } //Aadding Numeric Columns to result set General.CreateCodeLog("Step - 48.1", "Before Aadding DateFormat Columns to result set", "", strMethodName, lstSiteListDTO, null, strServiceName); List lstNumericUploadTemplateColumnListDTO = null; UploadTemplateColumnListDTO objTempNumericUploadTemplateColumnList = null; lstNumericUploadTemplateColumnListDTO = (from objList in lstUploadTemplateColumnListDTO where objList.IsNumericField == true && objList.DateColumnName != string.Empty select objList).ToList(); //lstDateUploadTemplateColumnListDTO = (from objList in lstUploadTemplateColumnListDTO where objList.IsDateField == true && objList.DateColumnName != string.Empty select objList).ToList(); if (lstNumericUploadTemplateColumnListDTO != null) { foreach (UploadTemplateColumnListDTO objNumericUploadTemplateColumnList in lstNumericUploadTemplateColumnListDTO) { try { General.CreateCodeLog("Step - 48.2", "While Aadding DateFormat Columns to result set(for each loop)", "", strMethodName, lstSiteListDTO, null, strServiceName); if (!(dtResultSet.Columns.Contains(objNumericUploadTemplateColumnList.DateColumnName))) { strDateFormatColumnNames = strDateFormatColumnNames + "," + objNumericUploadTemplateColumnList.DateColumnName; if (objNumericUploadTemplateColumnList.Comments.ToLower() == "int32") { dtResultSet.Columns.Add(objNumericUploadTemplateColumnList.DateColumnName, typeof(Int32)); } else if (objNumericUploadTemplateColumnList.Comments.ToLower() == "int64") { dtResultSet.Columns.Add(objNumericUploadTemplateColumnList.DateColumnName, typeof(Int64)); } else if (objNumericUploadTemplateColumnList.Comments.ToLower() == "decimal") { dtResultSet.Columns.Add(objNumericUploadTemplateColumnList.DateColumnName, typeof(decimal)); } else { dtResultSet.Columns.Add(objNumericUploadTemplateColumnList.DateColumnName, typeof(float)); } dtResultSet.AcceptChanges(); #region Add DateFormat Columns to UploadTemplate Column LIst objTempNumericUploadTemplateColumnList = new UploadTemplateColumnListDTO(); objTempNumericUploadTemplateColumnList.SourceColumnName = objNumericUploadTemplateColumnList.DateColumnName; objTempNumericUploadTemplateColumnList.DestinationColoumnName = objNumericUploadTemplateColumnList.DateColumnName; objTempNumericUploadTemplateColumnList.IsClientColumn = true; objTempNumericUploadTemplateColumnList.IsStoreInSuccessTable = true; objTempNumericUploadTemplateColumnList.IsMasterColumnName = true; lstUploadTemplateColumnListDTO.Add(objTempNumericUploadTemplateColumnList); objTempNumericUploadTemplateColumnList = null; #endregion } } catch (Exception ex) { General.CreateCodeLog("Step - 48.3", "exception in Aadding DateFormat Columns to result set(for each loop)" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } lstNumericUploadTemplateColumnListDTO = null; } #endregion #region Getting Master Data // Getting Master data to perform data check with the imported data from data table objSearchListDTO = new SearchListDTO(); objSearchListDTO.CompanyId = _ConfigurationSettingsListDTO.CompanyId; objSearchListDTO.SiteId = intSiteId; objSearchListDTO.intRecordId = intFileUploadTypeId; objSearchListDTO.WebSiteLanguageId = intWebSiteLanguageId; objFileUploadBAL = new FileUploadBAL(strConnectionString, EncryptionKey, _ConfigurationSettingsListDTO); General.CreateCodeLog("Step - 49", "Before GetMasterDataByTemplateId Method calling", "", strMethodName, lstSiteListDTO, null, strServiceName); dsMasterData = await objFileUploadBAL.GetMasterDataByTemplateIdAsync(objSearchListDTO); General.CreateCodeLog("Step - 50", "After GetMasterDataByTemplateId Method calling", "", strMethodName, lstSiteListDTO, null, strServiceName); #endregion #region Validate each record With Master data and General validations(Required,regex and MaxLength). DataTable dtTempSuccessDetails = new DataTable(); DataTable dtTempErrorDetails = new DataTable(); //DataTable dtErrorRows = dtTempSuccessDetails.Clone(); // For IsErroneous = true //DataTable dtSuccessRows = dtTempSuccessDetails.Clone(); dtTempSuccessDetails = dtResultSet.Clone(); dtTempErrorDetails = dtResultSet.Clone(); Int64 Counter = 0; Int64 IntLogCounter = 0; foreach (DataRow dr in dtResultSet.Rows) { try { IntLogCounter = IntLogCounter + 1; if (IntLogCounter == _ConfigurationSettingsListDTO.RecordLogCounter) { //CreateErrorLogProgramWise("File read record counter", Counter.ToString() + "File Upload Type id :" + intFileUploadTypeId.ToString(), strMethodName, SiteCode, intSiteId); General.CreateCodeLog("File read record counter", Counter.ToString() + "File Upload Type id :" + intFileUploadTypeId.ToString(), "", strMethodName, lstSiteListDTO, null, strServiceName); //IntLogCounter = 0; } General.CreateCodeLog("Step - 51", "Start of dtResultSet.Rows - for each loop", "", strMethodName, lstSiteListDTO, null, strServiceName); bool IsErroneous = false; StringBuilder strErrorDescription = new StringBuilder(); string strColumnValue = string.Empty; string strMasterIdColumnName = string.Empty; string strRangeMinimumValue = string.Empty; string strRangeMaximumValue = string.Empty; string strRangeErrorDescription = string.Empty; #region Validate every field in the row foreach (UploadTemplateColumnListDTO objUploadTemplateColumnListDTO in lstUploadTemplateColumnListDTO) { try { //CreateCodeLogProgramWise("Step - 52", "Start of lstUploadTemplateColumnListDTO - for each loop"); if (objUploadTemplateColumnListDTO.IsMasterColumnName == false) { strColumnValue = string.Empty; if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.FinanceStatementUpload))//|| intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTColumnMapper)) { if (dtResultSet.Columns[objUploadTemplateColumnListDTO.SourceColumnName] != null) { strColumnValue = dr[objUploadTemplateColumnListDTO.SourceColumnName].ToString().Trim(); } } else if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTDataUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTPeopleCountUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTIFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTPFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTTDSUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTVolumeUpload)) { string sourceColumnName = objUploadTemplateColumnListDTO.SourceColumnName; // Special handling for DateCreated renaming in IOT if (sourceColumnName == "DateCreated" && dtResultSet.Columns.Contains("CDateCreated")) { sourceColumnName = "CDateCreated"; } if (dtResultSet.Columns[sourceColumnName] != null) { strColumnValue = dr[sourceColumnName]?.ToString().Trim(); } } else { if (dtResultSet.Columns[objUploadTemplateColumnListDTO.SourceColumnName] != null) { strColumnValue = dr[objUploadTemplateColumnListDTO.SourceColumnName].ToString().Trim(); } } strMasterIdColumnName = objUploadTemplateColumnListDTO.MasterColumnIdName; strRangeMinimumValue = objUploadTemplateColumnListDTO.RangeMinimumValue; strRangeMaximumValue = objUploadTemplateColumnListDTO.RangeMaximumValue; strRangeErrorDescription = objUploadTemplateColumnListDTO.RangeErrorDescription; #region checkMandatory if (objUploadTemplateColumnListDTO.IsMandatory) { if (string.IsNullOrEmpty(strColumnValue)) { IsErroneous = true; strErrorDescription.Append(objUploadTemplateColumnListDTO.RequiredErrorDescription + ","); } } #endregion checkMandatory #region CheckMaxLength if (objUploadTemplateColumnListDTO.MaxLength > 0) { if (strColumnValue.Length > objUploadTemplateColumnListDTO.MaxLength) { IsErroneous = true; strErrorDescription.Append(objUploadTemplateColumnListDTO.MaxLengthErrorDescription + ","); } } #endregion CheckMaxLength #region RegularExpression if (objUploadTemplateColumnListDTO.RegularExpression != null && objUploadTemplateColumnListDTO.RegularExpression != string.Empty && strColumnValue != string.Empty && objUploadTemplateColumnListDTO.IsCustomRegex) { if (!(Regex.IsMatch(strColumnValue, objUploadTemplateColumnListDTO.RegularExpression, RegexOptions.IgnoreCase))) //for numeric//objUploadTemplateColumnListDTO.RegularExpression) { IsErroneous = true; strErrorDescription.Append(objUploadTemplateColumnListDTO.RegexErrorDescription + ","); } } if (objUploadTemplateColumnListDTO.RegularExpression2 != null && objUploadTemplateColumnListDTO.RegularExpression2 != string.Empty && strColumnValue != string.Empty && objUploadTemplateColumnListDTO.IsCustomRegex) { if (!(Regex.IsMatch(strColumnValue, objUploadTemplateColumnListDTO.RegularExpression2, RegexOptions.IgnoreCase))) //for numeric//objUploadTemplateColumnListDTO.RegularExpression) { IsErroneous = true; strErrorDescription.Append(objUploadTemplateColumnListDTO.Regex2ErrorDescription + ","); } } if (objUploadTemplateColumnListDTO.RegularExpression3 != null && objUploadTemplateColumnListDTO.RegularExpression3 != string.Empty && strColumnValue != string.Empty && objUploadTemplateColumnListDTO.IsCustomRegex) { if (!(Regex.IsMatch(strColumnValue, objUploadTemplateColumnListDTO.RegularExpression3, RegexOptions.IgnoreCase))) //for numeric//objUploadTemplateColumnListDTO.RegularExpression) { IsErroneous = true; strErrorDescription.Append(objUploadTemplateColumnListDTO.Regex3ErrorDescription + ","); } } #endregion RegularExpression #region Master if (objUploadTemplateColumnListDTO.IsMaster) { int intMasterId = 0; Int64 MasterId = 0; bool IsMasterErrorRequired = objUploadTemplateColumnListDTO.IsMasterErrorRequired; string strMasterId = string.Empty; if (!(string.IsNullOrEmpty(strColumnValue))) { if (objUploadTemplateColumnListDTO.MasterDataType.ToLower() == General.IntegerDataType.ToString().ToLower()) { if (dsMasterData.Tables[objUploadTemplateColumnListDTO.MasterTablePosition] != null) { try { object objMasterId = dsMasterData.Tables[objUploadTemplateColumnListDTO.MasterTablePosition].AsEnumerable().Where(x => x.Field(objUploadTemplateColumnListDTO.MasterColumnName) != null && x.Field(objUploadTemplateColumnListDTO.MasterColumnName).ToString().ToLower().Trim() == strColumnValue.ToLower().Trim()).Select(x => x.Field(objUploadTemplateColumnListDTO.MasterColumnIdName)).FirstOrDefault(); if (objMasterId != null && !String.IsNullOrEmpty(objMasterId.ToString().Trim())) { Int32.TryParse(objMasterId.ToString().Trim(), out intMasterId); } } catch (Exception ex) { General.CreateCodeLog("Step - 52", "Exception in objUploadTemplateColumnListDTO" + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } if (intMasterId == 0) { if (IsMasterErrorRequired) { IsErroneous = true; strErrorDescription.Append(objUploadTemplateColumnListDTO.MissingMasterErrorDescription + ","); } } else { dr[strMasterIdColumnName] = intMasterId.ToString(); } } else if (objUploadTemplateColumnListDTO.MasterDataType.ToLower() == General.BigIntDataType.ToString().ToLower()) { if (dsMasterData.Tables[objUploadTemplateColumnListDTO.MasterTablePosition] != null) { try { object objMasterId = dsMasterData.Tables[objUploadTemplateColumnListDTO.MasterTablePosition].AsEnumerable().Where(x => x.Field(objUploadTemplateColumnListDTO.MasterColumnName) != null && x.Field(objUploadTemplateColumnListDTO.MasterColumnName).ToString().ToLower().Trim() == strColumnValue.ToLower().Trim()).Select(x => x.Field(objUploadTemplateColumnListDTO.MasterColumnIdName)).FirstOrDefault(); if (objMasterId != null && !String.IsNullOrEmpty(objMasterId.ToString().Trim())) { Int64.TryParse(objMasterId.ToString().Trim(), out MasterId); } } catch (Exception ex) { General.CreateCodeLog("Step - 52", "Exception in objUploadTemplateColumnListDTO" + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } if (MasterId == 0) { if (IsMasterErrorRequired) { IsErroneous = true; strErrorDescription.Append(objUploadTemplateColumnListDTO.MissingMasterErrorDescription + ","); } } else { dr[strMasterIdColumnName] = MasterId.ToString(); } } else if (objUploadTemplateColumnListDTO.MasterDataType.ToLower() == General.StringDataType.ToString().ToLower()) { if (dsMasterData.Tables[objUploadTemplateColumnListDTO.MasterTablePosition] != null) { // Check if the table is not null if (dsMasterData.Tables[objUploadTemplateColumnListDTO.MasterTablePosition] != null) { // If strColumnValue contains the "|" symbol, split the value and handle each part separately if (strColumnValue.Contains("|")) { var valuesToMatch = strColumnValue.Split('|'); // Split string if multiple values need to be checked bool isMasterIdFound = false; string strFinalMasterId = string.Empty; // Iterate through each value in valuesToMatch foreach (var value in valuesToMatch) { // Find the Master ID for each value strMasterId = (from p in dsMasterData.Tables[objUploadTemplateColumnListDTO.MasterTablePosition].AsEnumerable() where p.Field(objUploadTemplateColumnListDTO.MasterColumnName) != null && p.Field(objUploadTemplateColumnListDTO.MasterColumnName).ToLower() == value.ToLower() select p.Field(objUploadTemplateColumnListDTO.MasterColumnIdName)).FirstOrDefault(); // If strMasterId is not null or empty, append it to strFinalMasterId with a "|" separator if (!string.IsNullOrEmpty(strMasterId)) { if (!string.IsNullOrEmpty(strFinalMasterId)) { strFinalMasterId += "|"; // Add "|" only between Master IDs } strFinalMasterId += strMasterId; // Append the current MasterId } } // If the Master ID is found, use it if (!string.IsNullOrEmpty(strFinalMasterId)) { // Assuming you want the first valid Master ID found (modify as needed for your business logic) dr[strMasterIdColumnName] = strFinalMasterId; // Set the Master ID into the row for this column isMasterIdFound = true; } // If no Master ID was found, handle the error if (!isMasterIdFound && IsMasterErrorRequired) { IsErroneous = true; strErrorDescription.Append(objUploadTemplateColumnListDTO.MissingMasterErrorDescription + ","); } } else { if (dsMasterData.Tables[objUploadTemplateColumnListDTO.MasterTablePosition] != null) { strMasterId = (from p in dsMasterData.Tables[objUploadTemplateColumnListDTO.MasterTablePosition].AsEnumerable() where p.Field(objUploadTemplateColumnListDTO.MasterColumnName) != null && p.Field(objUploadTemplateColumnListDTO.MasterColumnName).ToLower() == strColumnValue.ToLower() select p.Field(objUploadTemplateColumnListDTO.MasterColumnIdName)).FirstOrDefault(); } if (string.IsNullOrEmpty(strMasterId)) { if (IsMasterErrorRequired) { IsErroneous = true; strErrorDescription.Append(objUploadTemplateColumnListDTO.MissingMasterErrorDescription + ","); } } else { dr[strMasterIdColumnName] = strMasterId; } } if (strMasterId == string.Empty) { if (IsMasterErrorRequired) { IsErroneous = true; strErrorDescription.Append(objUploadTemplateColumnListDTO.MissingMasterErrorDescription + ","); } } else { dr[strMasterIdColumnName] = strMasterId; } } } } } } #endregion Master #region NumericField if (objUploadTemplateColumnListDTO.IsNumericField) { bool IsValueNumeric = false; int int32ColumnValue = 0; Int64 int64ColumnValue = 0; decimal dcColumnValue = 0; float floatColumnValue = 0; if (!string.IsNullOrEmpty(strColumnValue)) { System.Object Expression = strColumnValue; IsValueNumeric = IsNumeric(Expression); if (IsValueNumeric && dtResultSet.Columns[objUploadTemplateColumnListDTO.DateColumnName] != null && objUploadTemplateColumnListDTO.DateColumnName != string.Empty) { if (dtResultSet.Columns[objUploadTemplateColumnListDTO.DateColumnName].DataType.Name.ToLower() == "int32") { int.TryParse(strColumnValue, out int32ColumnValue); dr[objUploadTemplateColumnListDTO.DateColumnName] = int32ColumnValue; } else if (dtResultSet.Columns[objUploadTemplateColumnListDTO.DateColumnName].DataType.Name.ToLower() == "int64") { Int64.TryParse(strColumnValue, out int64ColumnValue); dr[objUploadTemplateColumnListDTO.DateColumnName] = int64ColumnValue; } else if (dtResultSet.Columns[objUploadTemplateColumnListDTO.DateColumnName].DataType.Name.ToLower() == "decimal") { decimal.TryParse(strColumnValue, out dcColumnValue); dr[objUploadTemplateColumnListDTO.DateColumnName] = dcColumnValue; } else { float.TryParse(strColumnValue, out floatColumnValue); dr[objUploadTemplateColumnListDTO.DateColumnName] = floatColumnValue; } } } else { IsValueNumeric = true; //if (dtResultSet.Columns[objUploadTemplateColumnListDTO.DateColumnName] != null && objUploadTemplateColumnListDTO.DateColumnName != string.Empty) //{ // dr[objUploadTemplateColumnListDTO.DateColumnName] = strColumnValue; //} if (dtResultSet.Columns[objUploadTemplateColumnListDTO.DateColumnName] != null && objUploadTemplateColumnListDTO.DateColumnName != string.Empty) { if (dtResultSet.Columns[objUploadTemplateColumnListDTO.DateColumnName].DataType.Name.ToLower() == "int32") { int.TryParse(strColumnValue, out int32ColumnValue); dr[objUploadTemplateColumnListDTO.DateColumnName] = int32ColumnValue; } else if (dtResultSet.Columns[objUploadTemplateColumnListDTO.DateColumnName].DataType.Name.ToLower() == "int64") { Int64.TryParse(strColumnValue, out int64ColumnValue); dr[objUploadTemplateColumnListDTO.DateColumnName] = int64ColumnValue; } else if (dtResultSet.Columns[objUploadTemplateColumnListDTO.DateColumnName].DataType.Name.ToLower() == "decimal") { decimal.TryParse(strColumnValue, out dcColumnValue); dr[objUploadTemplateColumnListDTO.DateColumnName] = dcColumnValue; } else { float.TryParse(strColumnValue, out floatColumnValue); dr[objUploadTemplateColumnListDTO.DateColumnName] = floatColumnValue; } } } if (!IsValueNumeric) { IsErroneous = true; strErrorDescription.Append(objUploadTemplateColumnListDTO.NonNumericErrorDescription + ","); } if (strRangeMaximumValue != string.Empty && strRangeMinimumValue != string.Empty && strRangeErrorDescription != string.Empty && IsValueNumeric) { double MinVlaue = 0; double maxvalue = 0; double originalValue = 0; double.TryParse(strColumnValue, out originalValue); double.TryParse(strRangeMinimumValue, out MinVlaue); double.TryParse(strRangeMaximumValue, out maxvalue); if (!(MinVlaue <= originalValue && originalValue <= maxvalue)) { IsErroneous = true; strErrorDescription.Append(strRangeErrorDescription + ","); } } } #endregion NumericField #region DateField if (objUploadTemplateColumnListDTO.IsDateField &&!string.IsNullOrWhiteSpace(strColumnValue)) { bool IsValidDate = false; string value = strColumnValue.Trim(); if (value != objUploadTemplateColumnListDTO.DateDefaultValue) { DateTime dtValue = DateTime.MinValue; // If no format defined → fallback safe parse if (string.IsNullOrWhiteSpace(objUploadTemplateColumnListDTO.DateFormat) || objUploadTemplateColumnListDTO.DateFormat.ToLower() == "null") { if (DateTime.TryParse(value, new CultureInfo("en-IN"), DateTimeStyles.AllowWhiteSpaces, out dtValue)) { IsValidDate = true; } else { IsErroneous = true; } } else { // Strict format parsing string[] formats = objUploadTemplateColumnListDTO.DateFormat .Split('|', StringSplitOptions.RemoveEmptyEntries) .Select(f => f.Trim()) .ToArray(); if (formats.Length > 0 && DateTime.TryParseExact(value, formats, new CultureInfo("en-IN"), DateTimeStyles.AllowWhiteSpaces, out dtValue)) { IsValidDate = true; } else { IsErroneous = true; } } // Assign if valid if (IsValidDate && !string.IsNullOrWhiteSpace(objUploadTemplateColumnListDTO.DateColumnName) && dtResultSet.Columns.Contains(objUploadTemplateColumnListDTO.DateColumnName)) { dr[objUploadTemplateColumnListDTO.DateColumnName] = dtValue; } else if (IsErroneous) { if (!string.IsNullOrWhiteSpace(objUploadTemplateColumnListDTO.DateFormatErrorDescripton)) { strErrorDescription.Append(objUploadTemplateColumnListDTO.DateFormatErrorDescripton + ","); } else { strErrorDescription.Append(objUploadTemplateColumnListDTO.RegexErrorDescription + ","); } } } } #endregion DateField #region MaxandMin if (strRangeMaximumValue != string.Empty && strRangeMinimumValue != string.Empty && strRangeErrorDescription != string.Empty) { DateTime MinVlaue = DateTime.MinValue; DateTime maxvalue = DateTime.MinValue; DateTime originalValue = DateTime.MinValue; DateTime.TryParseExact(strColumnValue, objUploadTemplateColumnListDTO.DateFormat, null, DateTimeStyles.None, out originalValue); DateTime.TryParseExact(strRangeMinimumValue, objUploadTemplateColumnListDTO.DateFormat, null, DateTimeStyles.None, out MinVlaue); DateTime.TryParseExact(strRangeMaximumValue, objUploadTemplateColumnListDTO.DateFormat, null, DateTimeStyles.None, out maxvalue); if (!(MinVlaue <= originalValue && originalValue <= maxvalue)) { IsErroneous = true; strErrorDescription.Append(strRangeErrorDescription + ","); if (dtResultSet.Columns[objUploadTemplateColumnListDTO.DateColumnName] != null && objUploadTemplateColumnListDTO.DateColumnName != string.Empty) { dr[objUploadTemplateColumnListDTO.DateColumnName] = DBNull.Value; } } } #endregion MaxandMin } } catch (Exception ex) { General.CreateCodeLog("Step - 53", "Exception in compare lstUploadTemplateColumnListDTO - for each loop" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } #endregion if (dr["FileUploadId"] != null) { dr["FileUploadId"] = intFileUploadId; } if (dr["CreatedBy"] != null) { dr["CreatedBy"] = strUserName; } if (dr["DateCreated"] != null) { dr["DateCreated"] =dtCurrentDate; } if (dr["IPAddress"] != null) { dr["IPAddress"] = _ConfigurationSettingsListDTO.IPAddress; } if (dr["CompanyId"] != null) { dr["CompanyId"] = _ConfigurationSettingsListDTO.CompanyId; } if (dr["SiteId"] != null) { dr["SiteId"] = objFileUploadDTO.SiteId > 0 ? objFileUploadDTO.SiteId : lstSiteListDTO[0].SiteId; } if (IsStoreHeaderInSuccess) { IsStoreHeader = true; if (dr["HeaderInformation"] != null) { dr["HeaderInformation"] = HeaderInformation; } } if (!IsErroneous || IsStoreErrorRecordInSuccess) { Counter = Counter + 1; //Inserting Error Reocrds to Error Data table. if (dtResultSet.Columns[RecordCounterColumnName] != null) { dr[RecordCounterColumnName] = Counter; } if (IsPasswordGenerationRequired) { string EncryptedUserPassword = string.Empty; if (PasswordTypeGeneration == Convert.ToInt32(General.PasswordType.Numeric)) //for numeric { EncryptedUserPassword = General.GenerateRandomNumber(PasswordLength); } else ////for alphanumeric { EncryptedUserPassword = General.GenerateRandomNumber(1) + await General.GenerateRandomNumberAsync(PasswordLength - 1, PasswordTypeGeneration.ToString()); } EncryptedUserPassword = clsCryptography.Encrypt(EncryptedUserPassword); if (dtResultSet.Columns[PasswordGenerationColumnName] != null) { dr[PasswordGenerationColumnName] = EncryptedUserPassword.ToString().TrimEnd(','); } } if (dtResultSet.Columns["IsErroneous"] != null) { dr["IsErroneous"] = IsErroneous; } if (dtResultSet.Columns[ErrorDescriptionColumnName] != null) { dr[ErrorDescriptionColumnName] = strErrorDescription.ToString().TrimEnd(','); } if (IsStoreErrorRecordInSuccess) { strIsErroneousColumnName = "IsErroneous"; } //Inserting Success Reocrds to Success Data table. //Inserting Success Reocrds to Success Data table. if (dtTempSuccessDetails == null) { dtTempSuccessDetails = new DataTable(); dtTempSuccessDetails = dtResultSet.Clone(); } dtTempSuccessDetails.ImportRow(dr); dtTempSuccessDetails.AcceptChanges(); General.CreateCodeLog("Step - 54", "Record is valid and added to success temp table", "", strMethodName, lstSiteListDTO, null, strServiceName); } else { //Inserting Error Reocrds to Error Data table. if (dtResultSet.Columns[ErrorDescriptionColumnName] != null) { dr[ErrorDescriptionColumnName] = strErrorDescription.ToString().TrimEnd(','); } dtTempErrorDetails.ImportRow(dr); dtTempErrorDetails.AcceptChanges(); General.CreateCodeLog("Step - 54", "Record is IN valid and added to Error temp table", "", strMethodName, lstSiteListDTO, null, strServiceName); } strErrorDescription = null; if (IntLogCounter == _ConfigurationSettingsListDTO.RecordLogCounter) { #region Save Success Records Into Success table defined in tblFileUploadType if (dtTempSuccessDetails != null && dtTempSuccessDetails.Rows.Count > 0) { General.CreateCodeLog("Step - 55", "Start of ImportDataToSuccessTable Method call", "", strMethodName, lstSiteListDTO, null, strServiceName); List lstSuccessTableList = (from objUploadTemplateColumnListDTO in lstUploadTemplateColumnListDTO where objUploadTemplateColumnListDTO.IsStoreInSuccessTable == true select objUploadTemplateColumnListDTO).ToList(); string strErrorMessage = string.Empty; bool IsErrorRecords = false; if (lstSuccessTableList != null) { string[] strResult = await ImportDataToSuccessTableAsync(dtTempSuccessDetails, SuccessTableName, lstSuccessTableList, RecordCounterColumnName, PasswordGenerationColumnName, IsStoreHeader, ErrorDescriptionColumnName, strIsErroneousColumnName, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, intFileUploadTypeId, intSplitCount); General.CreateCodeLog("Step - 56", "After ImportDataToSuccessTable Method call", "", strMethodName, lstSiteListDTO, null, strServiceName); //0 for failure //1 for success if (strResult.Length > 1) { if (strResult[0] == "0") { IsErrorRecords = true; strErrorMessage = strResult[1]; if (strErrorMessage == string.Empty) { strErrorMessage = "Error while inserting data into temp success table"; } } else { if (IsServiceStartOrStop) { // SecondServiceStart(strSecondServiceName, SiteLogTypeId, strConnectionString, EncryptionKey,lstSiteListDTO); } if (_ConfigurationSettingsListDTO.IsAPIService) { /// /// This method is responsible for stopping the first background service related to file uploads. /// It follows these steps: /// 1. Retrieves the `intBackGroundServiceId` for the `RevalERPFileUploadProcessorService`. /// 2. Logs the process start before calling `GetAPIUrlByBackGroundServiceIdDB`. /// 3. Fetches the API URL and service details using `GetAPIUrlByBackGroundServiceId`. /// 4. Logs after fetching the service details. /// 5. If service details exist: /// - Extracts `MainBackgroundServiceId` and `APIEndPointURL`. /// - Constructs the API endpoint URL to stop the service. /// - Logs before calling the REST API. /// - Calls `CallRestAPIForService` to stop the service. /// - Logs after calling the API. /// Int64 intBackGroundServiceId = Convert.ToInt32(General.BackGroundService.RevalERPFileUploadProcessorSecondService); General.CreateCodeLog("Step 3.9", "Before Calling GetAPIUrlByBackGroundServiceIdDB method -Checking For Start or Stop the FileUpload Service", string.Empty); lstBackgroundServiceListDTO = await objFileUploadBAL.GetAPIUrlByBackGroundServiceId(intBackGroundServiceId, _ConfigurationSettingsListDTO.ConnectionString); General.CreateCodeLog("Step 3.9", "After Calling GetAPIUrlByBackGroundServiceIdDB method -Checking For Start or Stop the FileUpload Service", string.Empty); if (lstBackgroundServiceListDTO != null) { Int64 MainBackgroundServiceId = lstBackgroundServiceListDTO[0].MainBackGroundServiceId; string strAPIEndPointURL = lstBackgroundServiceListDTO[0].APIEndPointURL; string strApiUrl = $"{strAPIEndPointURL}Services/start/{MainBackgroundServiceId}/{MainBackgroundServiceId}"; string strAPIRequest = string.Empty; General.CreateCodeLog("Step 3.9", "Before Calling CallRestAPI method -Checking For Start or Stop the FileUpload Service", string.Empty); string strResponse = CallRestAPIForService(strToken, strApiUrl); // Calling Respected API Start General.CreateCodeLog("Step 3.9", "After Calling CallRestAPI method -Checking For Start or Stop the FileUpload Service", string.Empty); } } } } } if (IsErrorRecords) { if (dtTempSuccessDetails != null) { foreach (DataRow drSuccess in dtTempSuccessDetails.Rows) { try { General.CreateCodeLog("Step - 57", "Adding ImportDataToSuccessTable return error.and adding records to dtTempErrorDetails", "", strMethodName, lstSiteListDTO, null, strServiceName); if (dtTempSuccessDetails.Columns[ErrorDescriptionColumnName] != null) { drSuccess[ErrorDescriptionColumnName] = strErrorMessage.ToString().TrimEnd(','); } if (dtTempErrorDetails == null) { dtTempErrorDetails = dtTempSuccessDetails.Clone(); } dtTempErrorDetails.ImportRow(drSuccess); dtTempErrorDetails.AcceptChanges(); } catch (Exception ex) { General.CreateCodeLog("Step - 57", "Adding ImportDataToSuccessTable return error.and adding records to dtTempErrorDetails" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } } dtTempSuccessDetails = null; } #endregion } } catch (Exception ex) { General.CreateCodeLog("Step - 54", "Exception in dtResultSet.Rows for each loop" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } if (IntLogCounter == _ConfigurationSettingsListDTO.RecordLogCounter) { IntLogCounter = 0; } } #endregion #region Save Success Records Into Success table defined in tblFileUploadType if (dtTempSuccessDetails != null && dtTempSuccessDetails.Rows.Count > 0) { General.CreateCodeLog("Step - 55", "Start of ImportDataToSuccessTable Method call", "", strMethodName, lstSiteListDTO, null, strServiceName); List lstSuccessTableList = (from objUploadTemplateColumnListDTO in lstUploadTemplateColumnListDTO where objUploadTemplateColumnListDTO.IsStoreInSuccessTable == true select objUploadTemplateColumnListDTO).ToList(); string strErrorMessage = string.Empty; bool IsErrorRecords = false; if (lstSuccessTableList != null) { string[] strResult = await ImportDataToSuccessTableAsync(dtTempSuccessDetails, SuccessTableName, lstSuccessTableList, RecordCounterColumnName, PasswordGenerationColumnName, IsStoreHeader, ErrorDescriptionColumnName, strIsErroneousColumnName, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, intFileUploadTypeId, intSplitCount); General.CreateCodeLog("Step - 56", "After ImportDataToSuccessTable Method call", "", strMethodName, lstSiteListDTO, null, strServiceName); //0 for failure //1 for success if (strResult.Length > 1) { if (strResult[0] == "0") { IsErrorRecords = true; strErrorMessage = strResult[1]; if (strErrorMessage == string.Empty) { strErrorMessage = "Error while inserting data into temp success table"; } } else { if (IsServiceStartOrStop) { // SecondServiceStart(strSecondServiceName, SiteLogTypeId, strConnectionString, EncryptionKey,lstSiteListDTO); } if (_ConfigurationSettingsListDTO.IsAPIService) { /// /// This method is responsible for stopping the first background service related to file uploads. /// It follows these steps: /// 1. Retrieves the `intBackGroundServiceId` for the `RevalERPFileUploadProcessorService`. /// 2. Logs the process start before calling `GetAPIUrlByBackGroundServiceIdDB`. /// 3. Fetches the API URL and service details using `GetAPIUrlByBackGroundServiceId`. /// 4. Logs after fetching the service details. /// 5. If service details exist: /// - Extracts `MainBackgroundServiceId` and `APIEndPointURL`. /// - Constructs the API endpoint URL to stop the service. /// - Logs before calling the REST API. /// - Calls `CallRestAPIForService` to stop the service. /// - Logs after calling the API. /// Int64 intBackGroundServiceId = Convert.ToInt32(General.BackGroundService.RevalERPFileUploadProcessorSecondService); General.CreateCodeLog("Step 3.9", "Before Calling GetAPIUrlByBackGroundServiceIdDB method -Checking For Start or Stop the FileUpload Service", string.Empty); lstBackgroundServiceListDTO = await objFileUploadBAL.GetAPIUrlByBackGroundServiceId(intBackGroundServiceId, _ConfigurationSettingsListDTO.ConnectionString); General.CreateCodeLog("Step 3.9", "After Calling GetAPIUrlByBackGroundServiceIdDB method -Checking For Start or Stop the FileUpload Service", string.Empty); if (lstBackgroundServiceListDTO != null) { Int64 MainBackgroundServiceId = lstBackgroundServiceListDTO[0].MainBackGroundServiceId; string strAPIEndPointURL = lstBackgroundServiceListDTO[0].APIEndPointURL; string strApiUrl = $"{strAPIEndPointURL}Services/start/{MainBackgroundServiceId}/{MainBackgroundServiceId}"; string strAPIRequest = string.Empty; General.CreateCodeLog("Step 3.9", "Before Calling CallRestAPI method -Checking For Start or Stop the FileUpload Service", string.Empty); string strResponse = CallRestAPIForService(strToken, strApiUrl); // Calling Respected API Start General.CreateCodeLog("Step 3.9", "After Calling CallRestAPI method -Checking For Start or Stop the FileUpload Service", string.Empty); } } } } } if (IsErrorRecords) { if (dtTempSuccessDetails != null) { foreach (DataRow dr in dtTempSuccessDetails.Rows) { try { General.CreateCodeLog("Step - 57", "Adding ImportDataToSuccessTable return error.and adding records to dtTempErrorDetails", "", strMethodName, lstSiteListDTO, null, strServiceName); if (dtTempSuccessDetails.Columns[ErrorDescriptionColumnName] != null) { dr[ErrorDescriptionColumnName] = strErrorMessage.ToString().TrimEnd(','); } if (dtTempErrorDetails == null) { dtTempErrorDetails = dtTempSuccessDetails.Clone(); } dtTempErrorDetails.ImportRow(dr); dtTempErrorDetails.AcceptChanges(); } catch (Exception ex) { General.CreateCodeLog("Step - 57", "Step - 57 Adding ImportDataToSuccessTable return error.and adding records to dtTempErrorDetails", "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } } } #endregion #region Validate success records from DB and Return Error data DataTable dtUploadDBErrorDetails = null; SearchListDTO objUploadSearchListDTO = null; General.CreateCodeLog("Step - 58", "Start of calling ValidateSuccessRecordsByFileUploadId method", "", strMethodName, lstSiteListDTO, null, strServiceName); try { objUploadSearchListDTO = new SearchListDTO(); objUploadSearchListDTO.CompanyId = _ConfigurationSettingsListDTO.CompanyId; objUploadSearchListDTO.SiteId = objFileUploadDTO.SiteId > 0 ? objFileUploadDTO.SiteId : lstSiteListDTO[0].SiteId; objUploadSearchListDTO.DepartmentId = _ConfigurationSettingsListDTO.DepartmentId; objUploadSearchListDTO.intRecordId = intFileUploadTypeId; objUploadSearchListDTO.RecordId = intFileUploadId; dtUploadDBErrorDetails = await objFileUploadBAL.ValidateSuccessRecordsByFileUploadIdAsync(objUploadSearchListDTO); General.CreateCodeLog("Step - 58", "After calling ValidateSuccessRecordsByFileUploadId method", "", strMethodName, lstSiteListDTO, null, strServiceName); } catch (Exception ex) { General.CreateCodeLog("Step - 58", "Exception in ValidateSuccessRecordsByFileUploadId method" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 strInvalidReason = "Error in Validate Success Records"; await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); General.CreateCodeLog("Step - 17.2", "End of ValidateSuccessRecords method", "", strMethodName, lstSiteListDTO, null, strServiceName); //CreateErrorLog("step 29", "before calling deletestorecustomerflataccrualdetailsfromupload method", strMethodName, SiteCode, intSiteId); await DeleteErrorDetailsByFileuploadTypeIdAsync(intFileUploadId, intFileUploadTypeId, IsDeleteSuccessRecords, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO); //CreateErrorLog("step 30", "after calling deletestorecustomerflataccrualdetailsfromupload method", strMethodName, SiteCode, intSiteId); dtUploadDBErrorDetails = null; dtTempErrorDetails = null; InvalidFileError = -1; } objUploadSearchListDTO = null; #endregion if (InvalidFileError == 0) { if (dtUploadDBErrorDetails != null && dtUploadDBErrorDetails.Rows.Count > 0) { Int64 CounterRecordId = 0; //Adding error records to dtTempErrorDatatTable General.CreateCodeLog("Step - 59", "Start of Adding DB Error records to Error temp table", "", strMethodName, lstSiteListDTO, null, strServiceName); Int64 IntTempLogCounter = 0; foreach (DataRow drError in dtUploadDBErrorDetails.Rows) { try { CounterRecordId = 0; IntTempLogCounter = IntTempLogCounter + 1; if (IntTempLogCounter == _ConfigurationSettingsListDTO.RecordLogCounter) { //CreateErrorLog("After validate procedure adding data to error record data table", (Counter).ToString() + " File upload type id: " + intFileUploadTypeId.ToString(), strMethodName, SiteCode, intSiteId); IntTempLogCounter = 0; } General.CreateCodeLog("Step - 52", "Start of dtResultSet.Rows - for each loop", "", strMethodName, lstSiteListDTO); if (drError[RecordCounterColumnName] != null) { Int64.TryParse(drError[RecordCounterColumnName].ToString(), out CounterRecordId); if (CounterRecordId > 0) { DataRow dataRow; if (dtTempSuccessDetails != null) { dataRow = dtTempSuccessDetails.AsEnumerable().FirstOrDefault(r => Convert.ToInt64(r[RecordCounterColumnName]) == CounterRecordId); } else if (dtResultSet != null) { dataRow = dtResultSet.AsEnumerable().FirstOrDefault(r => Convert.ToInt64(r[RecordCounterColumnName]) == CounterRecordId); } else { dataRow = null; } if (dataRow != null) { General.CreateCodeLog("Step - 60", "For each loop for dtUploadDBErrorDetails. Adding DB Error records to Error temp table", "", strMethodName, lstSiteListDTO); if (dtTempErrorDetails.Columns[ErrorDescriptionColumnName] != null) { dataRow[ErrorDescriptionColumnName] = drError[ErrorDescriptionColumnName].ToString(); } dtTempErrorDetails.ImportRow(dataRow); dtTempErrorDetails.AcceptChanges(); dataRow = null; } } } } catch (Exception ex) { General.CreateCodeLog("Step - 60-i", "Exception in adding error data table to source error data table for each loop" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } General.CreateCodeLog("Step - 61", "After Adding DB Error records to Error temp table", "", strMethodName, lstSiteListDTO, null, strServiceName); } } } #region Save Error records into physical file string strTempUploadErrorPath = string.Empty; if (!IsStoreErrorRecordInSuccess) { if (dtTempErrorDetails != null && dtTempErrorDetails.Rows.Count > 0) { if (IsAzureBlobStorage) //Addde By Akhila R on april 2025 { General.CreateCodeLog("Step - 62", "Save Error records into physical file", "", strMethodName, lstSiteListDTO, null, strServiceName); strErrorFileName = UploadErrorPrefix + dtCurrentDate.Day + dtCurrentDate.Month + dtCurrentDate.Year + dtCurrentDate.Hour + dtCurrentDate.Minute + strFileExtension; strTempUploadErrorPath = UploadErrorPath; ErrorFileFolderName = ErrorFileFolderName.Replace("\\\\", "/"); UploadErrorPath = UploadErrorPath + ErrorFileFolderName; BlobUploadErrorPath = BlobUploadErrorPath + FileUniqueID + "/" + ErrorFileFolderName; strErrorfilePath = UploadErrorPath + strErrorFileName; strBlobErrorfilePath = BlobUploadErrorPath + strErrorFileName; ErrorRecords = dtTempErrorDetails.Rows.Count; if (!Directory.Exists(UploadErrorPath)) { Directory.CreateDirectory(UploadErrorPath); } string strRemoveMasterColumn = string.Empty; strRemoveMasterColumn = strMasterColumnNames + "," + RecordIdColumnName + "," + RecordCounterColumnName + "," + strDateFormatColumnNames + ",CompanyId,SiteId,FileUploadId,CreatedBy,DateCreated,IPAddress," + RemoveErrorColumnNames; strRemoveMasterColumn = strRemoveMasterColumn.TrimEnd(','); if (IsPasswordGenerationRequired) { strRemoveMasterColumn = strRemoveMasterColumn + "," + PasswordGenerationColumnName; } string[] strArray = strRemoveMasterColumn.Split(','); foreach (string col in strArray) { try { if (col != string.Empty) { if (dtTempErrorDetails.Columns.Contains(col)) { dtTempErrorDetails.Columns.Remove(col); dtTempErrorDetails.AcceptChanges(); } } } catch (Exception ex) { General.CreateCodeLog("Step - 66", "exception in GetUnProcessedFileUploadsByTypeId catch block" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } // await CreateWorkbookAsync(strErrorfilePath, dtTempErrorDetails, SiteCode, SiteLogTypeId, IntErrorLog, strConnectionString, EncryptionKey, intSiteId); await CreateWorkbookAsync(strErrorfilePath, dtTempErrorDetails, SiteLogTypeId, IntErrorLog, strConnectionString, EncryptionKey, lstSiteListDTO); if (IsAzureBlobStorage) // Saving the Error Excel file in Blob and delting from local { if (File.Exists(strErrorfilePath)) { byte[] fileBytes = File.ReadAllBytes(strErrorfilePath); string strExcelBas64 = Convert.ToBase64String(fileBytes); strModuleCode = strModuleCode + "/"; FileUniqueID = FileUniqueID + "/" + ErrorFileFolderName; UploadStorageBAL objUploadStorageBAL = new UploadStorageBAL(_ConfigurationSettingsListDTO); strBlobErrorfilePath = objUploadStorageBAL.SaveFile(strExcelBas64, strErrorFileName, FileUniqueID, false, 0, 0, 0, intModuleStorageId, SiteCode, 0, 0, 0, strModuleCode, "", lstUploadStorageListDTO); if (!string.IsNullOrEmpty(strBlobErrorfilePath)) { File.Delete(strErrorfilePath); strErrorfilePath = strBlobErrorfilePath; } } } General.CreateCodeLog("Step - 62", "Save Error records into physical file CreateWorkbook method calling completed", "", strMethodName, lstSiteListDTO, null, strServiceName); } else { General.CreateCodeLog("Step - 62", "Save Error records into physical file", "", strMethodName, lstSiteListDTO, null, strServiceName); strErrorFileName = UploadErrorPrefix + dtCurrentDate.Day + dtCurrentDate.Month + dtCurrentDate.Year + dtCurrentDate.Hour + dtCurrentDate.Minute + strFileExtension; UploadErrorPath = UploadErrorPath + FileUniqueID + "\\" + ErrorFileFolderName; strErrorfilePath = UploadErrorPath + strErrorFileName; ErrorRecords = dtTempErrorDetails.Rows.Count; if (!Directory.Exists(UploadErrorPath)) { Directory.CreateDirectory(UploadErrorPath); } string strRemoveMasterColumn = string.Empty; strRemoveMasterColumn = strMasterColumnNames + "," + RecordIdColumnName + "," + RecordCounterColumnName + "," + strDateFormatColumnNames + ",CompanyId,SiteId,FileUploadId,CreatedBy,DateCreated,IPAddress," + RemoveErrorColumnNames; strRemoveMasterColumn = strRemoveMasterColumn.TrimEnd(','); if (IsPasswordGenerationRequired) { strRemoveMasterColumn = strRemoveMasterColumn + "," + PasswordGenerationColumnName; } string[] strArray = strRemoveMasterColumn.Split(','); foreach (string col in strArray) { try { if (col != string.Empty) { if (dtTempErrorDetails.Columns.Contains(col)) { dtTempErrorDetails.Columns.Remove(col); dtTempErrorDetails.AcceptChanges(); } } } catch (Exception ex) { General.CreateCodeLog("Step - 66", "exception in GetUnProcessedFileUploadsByTypeId catch block" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } await CreateWorkbookAsync(strErrorfilePath, dtTempErrorDetails, SiteLogTypeId, IntErrorLog, strConnectionString, EncryptionKey, lstSiteListDTO); General.CreateCodeLog("Step - 62", "Save Error records into physical file CreateWorkbook method calling completed", "", strMethodName, lstSiteListDTO, null, strServiceName); } } } #endregion #region Update Error File Paths // Saving the success and error file paths in tblFileUpload if (IsAzureBlobStorage) { General.CreateCodeLog("Step - 63", "Start of SaveFileUploadPath,UpdateFileUploadStatus Method calling", "", strMethodName, lstSiteListDTO, null, strServiceName); await SaveFileUploadPathAsync(intFileUploadId, strUserName, BlobUploadSuccessPath, strSuccessFileName, BlobUploadErrorPath, strErrorFileName, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO); string tempfilepath = strTempUploadErrorPath + strFileName; string tempcsvpath = strFilePath; string tempExcelFilePath = Path.Combine(Path.GetDirectoryName(strFilePath), strFileName); if (File.Exists(tempfilepath)) { File.Delete(tempfilepath); } if (File.Exists(tempcsvpath)) { File.Delete(tempcsvpath); } if (File.Exists(tempExcelFilePath)) { File.Delete(tempExcelFilePath); } } else { General.CreateCodeLog("Step - 63", "Start of SaveFileUploadPath,UpdateFileUploadStatus Method calling", "", strMethodName, lstSiteListDTO, null, strServiceName); await SaveFileUploadPathAsync(intFileUploadId, strUserName, UploadSuccessPath, strSuccessFileName, UploadErrorPath, strErrorFileName, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO); } strInvalidReason = null; dtTempErrorDetails = null; dtTempSuccessDetails = null; //if(intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.FinanceStatementUpload)) //{ // await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Completed), true, true, TotalRecords, ProcessedRecords, ErrorRecords, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); // General.CreateCodeLog("Step - 64", "After SaveFileUploadPath,UpdateFileUploadStatus Method calling", "", strMethodName, lstSiteListDTO, null, strServiceName); //} //else //{ await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.In_Progress), true, true, TotalRecords, ProcessedRecords, ErrorRecords, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); General.CreateCodeLog("Step - 64", "After SaveFileUploadPath,UpdateFileUploadStatus Method calling", "", strMethodName, lstSiteListDTO, null, strServiceName); //} #endregion #endregion } else { //Update tblFileUpload Status as invalid file General.CreateCodeLog("Step - 63", "Start of Method - UpdateFileUploadStatus- Update tblFileUpload Status as invalid file", "", strMethodName, lstSiteListDTO, null, strServiceName); strInvalidReason = "No Records in File"; await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); General.CreateCodeLog("Step - 63", "After Method - UpdateFileUploadStatus- Update tblFileUpload Status as invalid file", "", strMethodName, lstSiteListDTO, null, strServiceName); } } else { General.CreateCodeLog("Step - 63-a-1", "Start of Method - UpdateFileUploadStatus- Update tblFileUpload Status as invalid file", "", strMethodName, lstSiteListDTO, null, strServiceName); strInvalidReason = strInvalidHeaderColumns;// "Invalid headers in file (or) Invalid file."; await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); General.CreateCodeLog("Step - 63-a-2", "After Method - UpdateFileUploadStatus- Update tblFileUpload Status as invalid file", "", strMethodName, lstSiteListDTO, null, strServiceName); } } else { General.CreateCodeLog("Step - 63-a", "Start of Method - UpdateFileUploadStatus- Update tblFileUpload Status as invalid file", "", strMethodName, lstSiteListDTO, null, strServiceName); strInvalidReason = "Invalid headers in file (or) Invalid file."; await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); General.CreateCodeLog("Step - 63-b", "After Method - UpdateFileUploadStatus- Update tblFileUpload Status as invalid file", "", strMethodName, lstSiteListDTO, null, strServiceName); } } else { //Update tblFileUpload Status as invalid file General.CreateCodeLog("Step - 64", "Start of Method - UpdateFileUploadStatus- Update tblFileUpload Status as invalid file", "", strMethodName, lstSiteListDTO, null, strServiceName); strInvalidReason = "Invalid Headers"; await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); General.CreateCodeLog("Step - 65", "After Method - UpdateFileUploadStatus- Update tblFileUpload Status as invalid file", "", strMethodName, lstSiteListDTO, null, strServiceName); } #endregion } else { General.CreateCodeLog("Step 3.7", "After calling IsvalidFileExtension: " + IsvalidFileExtension, "", strMethodName, lstSiteListDTO, null, strServiceName); strInvalidReason = "Invalid file extension "; await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); } } catch (Exception ex) { General.CreateCodeLog("Step - 66", "exception in GetUnProcessedFileUploadsByTypeId catch block" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } else { if (IsServiceStartOrStop) { IsInProcess = false; //FirstServiceStartStop(SiteLogTypeId, strConnectionString, EncryptionKey,lstSiteListDTO); } if (_ConfigurationSettingsListDTO.IsAPIService) { /// /// This method is responsible for stopping the first background service related to file uploads. /// It follows these steps: /// 1. Retrieves the `intBackGroundServiceId` for the `RevalERPFileUploadProcessorService`. /// 2. Logs the process start before calling `GetAPIUrlByBackGroundServiceIdDB`. /// 3. Fetches the API URL and service details using `GetAPIUrlByBackGroundServiceId`. /// 4. Logs after fetching the service details. /// 5. If service details exist: /// - Extracts `MainBackgroundServiceId` and `APIEndPointURL`. /// - Constructs the API endpoint URL to stop the service. /// - Logs before calling the REST API. /// - Calls `CallRestAPIForService` to stop the service. /// - Logs after calling the API. /// Int64 intBackGroundServiceId = Convert.ToInt32(General.BackGroundService.RevalERPFileUploadProcessorServicve); General.CreateCodeLog("Step 3.9", "Before Calling GetAPIUrlByBackGroundServiceIdDB method -Checking For Start or Stop the FileUpload Service", string.Empty); lstBackgroundServiceListDTO = await objFileUploadBAL.GetAPIUrlByBackGroundServiceId(intBackGroundServiceId, _ConfigurationSettingsListDTO.ConnectionString); General.CreateCodeLog("Step 3.9", "After Calling GetAPIUrlByBackGroundServiceIdDB method -Checking For Start or Stop the FileUpload Service", string.Empty); if (lstBackgroundServiceListDTO != null) { Int64 MainBackgroundServiceId = lstBackgroundServiceListDTO[0].MainBackGroundServiceId; string strAPIEndPointURL = lstBackgroundServiceListDTO[0].APIEndPointURL; string strApiUrl = $"{strAPIEndPointURL}Services/stop/{MainBackgroundServiceId}/{MainBackgroundServiceId}"; string strAPIRequest = string.Empty; General.CreateCodeLog("Step 3.9", "Before Calling CallRestAPI method -Checking For Start or Stop the FileUpload Service", string.Empty); string strResponse = CallRestAPIForService(strToken, strApiUrl); // Calling Respected API For Stop General.CreateCodeLog("Step 3.9", "After Calling CallRestAPI method -Checking For Start or Stop the FileUpload Service", string.Empty); } } } #endregion } } catch (Exception ex) { General.CreateCodeLog("Step - 67", "Exception in ProcessUploadedFiles Main try block" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } finally { //IsInProcess = false; lstFileUploadListDTO = null; objFileUploadListDTO = null; objFileUploadBAL = null; } } #endregion ProcessUploadedFiles #region InsertFileUploadFromDownLoadPath public async Task InsertFileUploadFromDownLoadPathAsync(string strConnectionString, string EncryptionKey, int LogTypeId, int IntErrorLog, List lstSiteList) { string strMethodName = MethodBase.GetCurrentMethod().Name; General.CreateCodeLog("Step 4.1", "Start of InsertFileUploadFromDownLoadPath Method", "", strMethodName, lstSiteList, null, strServiceName); FileUploadServiceListDTO objFileUploadListDTO = null; FileUploadBAL objFileUploadBAL = null; DataTable dtFileUploadType = null; DateTime dtCurrentDate = RevalTimeZone.GetDateByTimezone(DateTime.Now, TimeZone, NoOfHours, NoOfMinutes); try { bool IsDownloadFile = false; bool IsDecryptFile = false; string DecryptFilePath = string.Empty; string DownloadFilePath = string.Empty; int intUploadTemplateId = 0, intModuleId = 0; string UploadSuccessPath = string.Empty; objFileUploadListDTO = new FileUploadServiceListDTO(); objFileUploadBAL = new FileUploadBAL(strConnectionString, EncryptionKey, _ConfigurationSettingsListDTO); objFileUploadListDTO.CompanyId = _ConfigurationSettingsListDTO.CompanyId; objFileUploadListDTO.SiteId = lstSiteList[0].SiteId; objFileUploadListDTO.DepartmentId = _ConfigurationSettingsListDTO.DepartmentId; objFileUploadListDTO.DisplayOnWeb = true; General.CreateCodeLog("Step 4.2", "Before calling GetFileUploadTypeByFileUploadTypeId Method", "", strMethodName, lstSiteList, null, strServiceName); dtFileUploadType = await objFileUploadBAL.GetFileUploadTypeByFileUploadTypeIdAsync(objFileUploadListDTO, _ConfigurationSettingsListDTO); if (dtFileUploadType != null && dtFileUploadType.Rows.Count > 0) { foreach (DataRow dr in dtFileUploadType.Rows) { try { General.CreateCodeLog("Step 4.3", "After calling GetFileUploadTypeByFileUploadTypeId Method - in foreach loop", "", strMethodName, lstSiteList, null, strServiceName); IsDownloadFile = false; IsDecryptFile = false; DecryptFilePath = string.Empty; DownloadFilePath = string.Empty; intUploadTemplateId = 0; UploadSuccessPath = string.Empty; intModuleId = 0; //DataRow dr = dtFileUploadType.Rows[0]; // Access the first row directly if (dr["UploadSuccessPath"] != null && dr["UploadSuccessPath"].ToString() != string.Empty) { UploadSuccessPath = dr["UploadSuccessPath"].ToString(); } if (dr["FileUploadTypeId"] != null && dr["FileUploadTypeId"].ToString() != string.Empty) { Int32.TryParse(dr["FileUploadTypeId"].ToString(), out intUploadTemplateId); } if (dr["DownloadFilePath"] != null && dr["DownloadFilePath"].ToString() != string.Empty) { DownloadFilePath = dr["DownloadFilePath"].ToString(); } if (dr["IsDownloadFile"] != null && dr["IsDownloadFile"].ToString() != string.Empty) { bool.TryParse(dr["IsDownloadFile"].ToString(), out IsDownloadFile); } if (dr["DecryptFilePath"] != null && dr["DecryptFilePath"].ToString() != string.Empty) { DecryptFilePath = dr["DecryptFilePath"].ToString(); } if (dr["IsDecrypt"] != null && dr["IsDecrypt"].ToString() != string.Empty) { bool.TryParse(dr["IsDecrypt"].ToString(), out IsDecryptFile); } if (dr["ModuleId"] != null && dr["ModuleId"].ToString() != string.Empty) { Int32.TryParse(dr["ModuleId"].ToString(), out intModuleId); } if (IsDownloadFile && DownloadFilePath != string.Empty) { General.CreateCodeLog("Step 4.4", "Before calling ImportDownloadFiles Method - in foreach loop", "", strMethodName, lstSiteList, null, strServiceName); await ImportDownloadFiles(DownloadFilePath, UploadSuccessPath, intUploadTemplateId, IsDecryptFile, DecryptFilePath, strConnectionString, EncryptionKey, LogTypeId, IntErrorLog, lstSiteList, intModuleId); General.CreateCodeLog("Step 4.5", "After calling ImportDownloadFiles Method - in foreach loop", "", strMethodName, lstSiteList, null, strServiceName); } } catch (Exception ex) { General.CreateCodeLog("Step 4.3.1", "Exception in GetFileUploadTypeByFileUploadTypeId for each loop" + ex.Message, "", strMethodName, lstSiteList, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteList, null, null); // Added by Subbarao B on Feb 05 2026 } } } } catch (Exception ex) { General.CreateCodeLog("Step 4.1.1", "Exception in InsertFileUploadFromDownLoadPath Method" + ex.Message, "", strMethodName, lstSiteList, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteList, null, null); // Added by Subbarao B on Feb 05 2026 } finally { General.CreateCodeLog("Step - 4.6", "End of InsertFileUploadFromDownLoadPath Method", "", strMethodName, lstSiteList, null, strServiceName); objFileUploadListDTO = null; objFileUploadBAL = null; dtFileUploadType = null; } } #endregion InsertFileUploadFromDownLoadPath #region Read text file and convert into data table Int64 intFileRecordCount = 0; private async Task BeforeReadRecordAsync(EngineBase engine, BeforeReadRecordEventArgs e) { string TrailerInformation = string.Empty; string HeaderInformation = string.Empty; int LogTypeId = 1; try { if (e.LineNumber == 1) { e.SkipThisRecord = true; HeaderInformation = e.RecordLine; } else { TrailerInformation = e.RecordLine; } } catch (Exception ex) { //General.CreateCodeLog("Step 4.1.1", "Exception in InsertFileUploadFromDownLoadPath Method: " + ex.Message, "",MethodBase.GetCurrentMethod(), lstSiteDetails); //General.CreateErrorLog(ex, "step 1 Exception in BeforeReadRecord ",lstSiteDetails,null); } } private void BeforeReadRecordWrapper(EngineBase engine, BeforeReadRecordEventArgs e) // Added By Mahesh Gupta 01-03-2024 { // Call the asynchronous method and wait for its completion synchronously BeforeReadRecordAsync(engine, e).GetAwaiter().GetResult(); } //************************************************************************************************************* // Purpose : To read text file from physical path and convert into data table // Layer : Service // Method Name : ReadTextFile // Input Parameters : // Return Values : Nothing // Return Values : Nothing // ------------------------------------------------------------------------------------------------------------ // Version Author Date Remarks // ----------------------------------------------------------------------------------------------------------- // Sanjay kalwa 1.0.0 20 Aug 2014 Creation // Rajani J 2.0 26 MAY 2015 For fixed length change the code and pass trim parameter //************************************************************************************************************* private async Task ReadTextFileAsync(string strClassBuilderName, string strFilePath, string HeaderLineText, string TrailerLineText, int FileUploadTypeId, Int64 intFileUploadId, List lstUploadTemplateColumnListDTO, string strUserName, int HeaderDaysCount, string strHeaderDateFormat, string strRecordIdColumnName, string ErrorDescriptionColumnName, string RecordCounterColumnName, bool IsPasswordGenerationRequired, string PasswordGenerationColumnName, int UploadFileTypeId, bool IsStoreErrorRecordInSuccess, string strEndTrim, char Delimiter, string strConnectionString, string EncryptionKey, int SiteLogTypeId, string HeaderInformation, string TrailerInformation, bool IsStoreHeaderInSuccess, int intHeaderRecordCountStartPosition, int intHeaderRecordCountLength, string strInvalidReason, int IntErrorLog, List lstSiteListDTO, int SiteId, int intFileUploadTypeId) { Int64 InvalidErrorRecords = 0; DataTable dtReturnTable = null; Int64 TableRecordCount = 0; Int64 TrailerRecordCount = 0; int intTrailerRecord = 0; FileHelperEngine engine = null; string strMethodName = MethodBase.GetCurrentMethod().Name; try { General.CreateCodeLog("Step - 37", "start of Reading text file convert into datatable", "", strMethodName, lstSiteListDTO); if (lstUploadTemplateColumnListDTO != null && lstUploadTemplateColumnListDTO.Count > 0 && strFilePath != string.Empty) { if (UploadFileTypeId == Convert.ToInt32(General.UploadFileType.FixedLength)) { General.CreateCodeLog("Step - 37", "ReadTextFile - if file type is FixedLength", "", strMethodName, lstSiteListDTO); FixedLengthClassBuilder cb = new FixedLengthClassBuilder(strClassBuilderName, FixedMode.ExactLength); foreach (UploadTemplateColumnListDTO objUploadTemplateColumnListDTO in lstUploadTemplateColumnListDTO) { General.CreateCodeLog("Step - 38", "ReadTextFile - if file type is FixedLength for each loop", "", strMethodName, lstSiteListDTO); if (objUploadTemplateColumnListDTO.ClassDataType.ToLower() == General.IntegerDataType) { cb.AddField(objUploadTemplateColumnListDTO.ClientColumnName, objUploadTemplateColumnListDTO.MaxLength, typeof(Int32)); cb.LastField.FieldNullValue = 0; if (objUploadTemplateColumnListDTO.IsTrimColumnValue) { cb.LastField.TrimMode = TrimMode.Both; if (strEndTrim != string.Empty) { cb.LastField.TrimChars = strEndTrim; } cb.LastField.TrimMode = TrimMode.Both; } } if (objUploadTemplateColumnListDTO.ClassDataType.ToLower() == General.StringDataType) { cb.AddField(objUploadTemplateColumnListDTO.ClientColumnName, objUploadTemplateColumnListDTO.MaxLength, typeof(string)); cb.LastField.FieldNullValue = string.Empty; if (objUploadTemplateColumnListDTO.IsTrimColumnValue) { cb.LastField.TrimMode = TrimMode.Both; if (strEndTrim != string.Empty) { cb.LastField.TrimChars = strEndTrim; } cb.LastField.TrimMode = TrimMode.Both; } } else if (objUploadTemplateColumnListDTO.ClassDataType.ToLower() == General.DeimalDataType) { cb.AddField(objUploadTemplateColumnListDTO.ClientColumnName, objUploadTemplateColumnListDTO.MaxLength, typeof(Decimal)); cb.LastField.FieldNullValue = 0.0m; if (objUploadTemplateColumnListDTO.IsTrimColumnValue) { cb.LastField.TrimMode = TrimMode.Both; if (strEndTrim != string.Empty) { cb.LastField.TrimChars = strEndTrim; } cb.LastField.TrimMode = TrimMode.Both; } } else if (objUploadTemplateColumnListDTO.ClassDataType.ToLower() == General.BigIntDataType) { cb.AddField(objUploadTemplateColumnListDTO.ClientColumnName, objUploadTemplateColumnListDTO.MaxLength, typeof(Int64)); cb.LastField.FieldNullValue = 0L; if (objUploadTemplateColumnListDTO.IsTrimColumnValue) { cb.LastField.TrimMode = TrimMode.Both; if (strEndTrim != string.Empty) { cb.LastField.TrimChars = strEndTrim; } cb.LastField.TrimMode = TrimMode.Both; } } else if (objUploadTemplateColumnListDTO.ClassDataType.ToLower() == General.DateTimeDataType) { cb.AddField(objUploadTemplateColumnListDTO.ClientColumnName, objUploadTemplateColumnListDTO.MaxLength, typeof(DateTime)); cb.LastField.Converter.Kind = ConverterKind.Date; cb.LastField.Converter.Arg1 = objUploadTemplateColumnListDTO.DateFormat; if (objUploadTemplateColumnListDTO.IsTrimColumnValue) { cb.LastField.TrimMode = TrimMode.Both; if (strEndTrim != string.Empty) { cb.LastField.TrimChars = strEndTrim; } cb.LastField.TrimMode = TrimMode.Both; } } } engine = new FileHelperEngine(cb.CreateRecordClass()); intFileRecordCount = engine.TotalRecords; engine.BeforeReadRecord += new BeforeReadRecordHandler(BeforeReadRecordWrapper); engine.ErrorManager.ErrorMode = ErrorMode.SaveAndContinue; dtReturnTable = engine.ReadFileAsDT(strFilePath); #region Bind Error Message if (engine.ErrorManager.ErrorCount > 0) { if (engine.ErrorManager.ErrorCount == 1 && HeaderLineText != string.Empty && TrailerLineText != string.Empty) { if (engine.ErrorManager.Errors[0].RecordString.ToLower().Contains(TrailerLineText.ToLower()) && engine.ErrorManager.Errors[0].RecordString.Length == 12) { InvalidErrorRecords = 0; intTrailerRecord = 1; } else { InvalidErrorRecords = engine.ErrorManager.ErrorCount; if (engine.ErrorManager.HasErrors) { foreach (ErrorInfo err in engine.ErrorManager.Errors) { //Console.WriteLine(err.LineNumber); //Console.WriteLine(err.RecordString); //Console.WriteLine(err.ExceptionInfo.ToString()); if (err.ExceptionInfo != null) { General.CreateCodeLog("Step - 42", "ReadTextFile - ErroInfor " + err.ExceptionInfo.ToString(), "", strMethodName, lstSiteListDTO); } } } } } else { InvalidErrorRecords = engine.ErrorManager.ErrorCount; if (engine.ErrorManager.HasErrors) { foreach (ErrorInfo err in engine.ErrorManager.Errors) { //Console.WriteLine(err.LineNumber); //Console.WriteLine(err.RecordString); //Console.WriteLine(err.ExceptionInfo.ToString()); if (err.ExceptionInfo != null) { General.CreateCodeLog("Step - 41", "ReadTextFile - ErroInfor " + strMethodName, "", strMethodName, lstSiteListDTO); } } } } //Not able to read the file. General.CreateCodeLog("Step - 42", "ReadTextFile - Assign columns to data table and be fore checking engine.ErrorManager.ErrorCount ", "", strMethodName, lstSiteListDTO); } #endregion } else if (UploadFileTypeId == Convert.ToInt32(General.UploadFileType.CSV) || UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xls_xlsx) || UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xls) || UploadFileTypeId == Convert.ToInt32(General.UploadFileType.xlsx)) { General.CreateCodeLog("Step - 37", "ReadTextFile - if file type is xls,xlsx,xls/xlsx or csv", "", strMethodName, lstSiteListDTO); try { if (strFilePath != string.Empty) { char quotingCharacter = '"'; // no quoting-character; char escapeCharacter = '\0'; // no escape-character; char commentCharacter = '\0'; // no comment-character(If we keep any character in this if same appear in the first caracter of a row in the file the the entire row will be igonored and considered it as a comment line) if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.FinanceStatementUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTDataUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTPeopleCountUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTIFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTPFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTTDSUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTVolumeUpload)) { using (var reader = new StreamReader(strFilePath)) { string rawText = await reader.ReadToEndAsync(); char quoteChar = rawText.Contains("\"") ? '"' : '\0'; // Handle quoted fields if needed string[] actualHeaders = null; int headerRowIndex = -1; List expectedHeaders = lstUploadTemplateColumnListDTO .Select(x => x.DestinationColoumnName.ToLower().Replace(" ", "").Replace("_", "").Trim()) .ToList(); // Pass 1: Locate the header row //using (var headerReader = new StringReader(rawText)) using (var headerReader = new StringReader(rawText)) { bool looksLikeQuotedCSV = rawText.Split('\n').Any(line => line.Trim().StartsWith("\"")); LumenWorks.Framework.IO.Csv.CsvReader csv; if (looksLikeQuotedCSV) { //using (var manualReader = new StringReader(rawText)) //{ // int currentRowIndex = 0; // string line; // while ((line = manualReader.ReadLine()) != null) // { // try // { // if (string.IsNullOrWhiteSpace(line)) // { // currentRowIndex++; // continue; // } // // Remove all quotes from the entire line // line = line.Replace("\"", "").Trim(); // // Split using delimiter and normalize each field // string[] currentRow = line // .Split(Delimiter) // .Select(x => x.Trim()) // Optional: .ToLower().Replace(" ", "").Replace("_", "") // .ToArray(); // // Assign to actualHeaders // actualHeaders = currentRow; // headerRowIndex = currentRowIndex; // // If you're only interested in the first non-empty row: // break; // } // catch (Exception ex) // { // General.CreateCodeLog("Step - 38.0", "Error processing line (quoted): " + ex.Message, "", strMethodName, lstSiteListDTO); // } // currentRowIndex++; // } //} csv = new CsvReader(new StringReader(rawText), false, ',', '"'); csv.MissingFieldAction = MissingFieldAction.ParseError; //string rawText = await reader.ReadToEndAsync(); // Get CSV data // Optional: Log to ensure data is actually loaded Console.WriteLine("Raw CSV Preview:\n" + rawText.Substring(0, Math.Min(200, rawText.Length))); // Re-create reader from raw text using (var headerReader1 = new StringReader(rawText)) { using (csv = new LumenWorks.Framework.IO.Csv.CsvReader(headerReader1, false, ',', '"')) { int currentRowIndex = 0; // Step 1: Flatten expected headers (normalize all aliases) List flattenedExpectedHeaders = expectedHeaders .SelectMany(h => h.Split(',', StringSplitOptions.RemoveEmptyEntries)) .Select(h => h.ToLower().Replace("\"", "").Replace(" ", "").Replace("_", "").Trim()) .Where(h => !string.IsNullOrEmpty(h)) .Distinct() .ToList(); while (csv.ReadNextRecord()) { try { string[] currentRow = new string[csv.FieldCount]; HashSet seenHeaders = new HashSet(StringComparer.OrdinalIgnoreCase); for (int j = 0; j < csv.FieldCount; j++) { string value = csv[j]; string cleanedValue = string.IsNullOrWhiteSpace(value) ? "" : value.ToLower().Replace("\"", "").Replace(" ", "").Replace("_", "").Trim(); // ✅ Take only first occurrence if (!string.IsNullOrEmpty(cleanedValue) && !seenHeaders.Contains(cleanedValue)) { currentRow[j] = cleanedValue; seenHeaders.Add(cleanedValue); } else { currentRow[j] = ""; // Ignore duplicates } } // ✅ Check if any cleaned header matches expected bool hasAtLeastOneHeaderMatch = currentRow.Any(c => flattenedExpectedHeaders.Contains(c)); if (hasAtLeastOneHeaderMatch) { actualHeaders = currentRow; headerRowIndex = currentRowIndex; break; // ✅ Use this row as header } } catch (Exception ex) { General.CreateCodeLog("Step - 38.0", "Error reading row during header scan: " + ex.Message, "", strMethodName, lstSiteListDTO); } currentRowIndex++; } } } // Build DataTable columns dtReturnTable = new DataTable(); Dictionary matchingColumnIndexes = new Dictionary(); var cleanedExpectedHeaderList = expectedHeaders .SelectMany(h => h.Split(',')) .Select(h => h.ToLower().Replace(" ", "").Replace("_", "").Trim()) .Where(h => !string.IsNullOrEmpty(h)) .Distinct() .ToList(); //for (int colIndex = 0; colIndex < actualHeaders.Length; colIndex++) //{ // string cleanedHeader = actualHeaders[colIndex].ToLower().Replace(" ", "").Replace("_", "").Trim(); // if (cleanedExpectedHeaderList.Contains(cleanedHeader)) // { // try // { // // Clean the matched header in DestinationColoumnName and use it directly // string matchedDestColName = lstUploadTemplateColumnListDTO // .FirstOrDefault(x => // (x.DestinationColoumnName ?? "") // .Split(',', StringSplitOptions.RemoveEmptyEntries) // .Select(d => d.ToLower().Replace(" ", "").Replace("_", "").Trim()) // .Contains(cleanedHeader.ToLower().Replace(" ", "").Replace("_", "").Trim()) // )?.SourceColumnName; // if (!string.IsNullOrEmpty(matchedDestColName)) // { // // ✅ Use the first clean, usable name from the comma-separated destination column // string finalColName = matchedDestColName // .Split(',', StringSplitOptions.RemoveEmptyEntries) // .Select(d => d.Trim().Replace("\"", "")) // .FirstOrDefault(d => !string.IsNullOrWhiteSpace(d)); // if (!string.IsNullOrEmpty(finalColName) && // !dtReturnTable.Columns.Contains(finalColName) && // !matchingColumnIndexes.ContainsValue(finalColName)) // { // dtReturnTable.Columns.Add(finalColName); // matchingColumnIndexes[colIndex] = finalColName; // } // } // } // catch (Exception ex) // { // General.CreateCodeLog("Step - 39.1", "Exception adding column: " + ex.Message, "", strMethodName, lstSiteListDTO); // General.CreateErrorLog(ex, "Step - 39.1 Adding column exception", lstSiteListDTO, null); // } // } //} for (int colIndex = 0; colIndex < actualHeaders.Length; colIndex++) { string cleanedHeader = actualHeaders[colIndex] .ToLower() .Replace(" ", "") .Replace("_", "") .Trim(); // ✅ Skip if this header is already matched once if (matchingColumnIndexes.Values.Contains(cleanedHeader)) continue; if (cleanedExpectedHeaderList.Contains(cleanedHeader)) { try { string matchedDestColName = lstUploadTemplateColumnListDTO .FirstOrDefault(x => (x.DestinationColoumnName ?? "") .Split(',', StringSplitOptions.RemoveEmptyEntries) .Select(d => d.ToLower().Replace(" ", "").Replace("_", "").Trim()) .Contains(cleanedHeader) )?.SourceColumnName; if (!string.IsNullOrEmpty(matchedDestColName) && !dtReturnTable.Columns.Contains(matchedDestColName)) { dtReturnTable.Columns.Add(matchedDestColName); matchingColumnIndexes[colIndex] = matchedDestColName; } } catch (Exception ex) { General.CreateCodeLog("Step - 39.1", "Exception adding column: " + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } string rawText1 = await reader.ReadToEndAsync(); // This you've already done // Use StringReader as the dataReader // Assume rawText contains full CSV text (including headers) // Step - 38.0: Read and preprocess CSV text General.CreateCodeLog("Step - 38.0", "Reading raw CSV content for header deduplication", "", strMethodName, lstSiteListDTO); dtReturnTable = ParseCsvToDataTable(rawText, matchingColumnIndexes, strMethodName, lstSiteListDTO); } else { // Simpler constructor – quotes will not be expected if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.FinanceStatementUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTDataUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTPeopleCountUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTIFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTPFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTTDSUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTVolumeUpload)) { using (var readerDel = new StreamReader(strFilePath)) using (csv = new LumenWorks.Framework.IO.Csv.CsvReader(readerDel, false, Delimiter)) { General.CreateCodeLog("Step - 38", "ReadTextFile - File is Reading and Converted to Data Table", "", strMethodName, lstSiteListDTO); dtReturnTable = new DataTable(); if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.FinanceStatementUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTDataUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTPeopleCountUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTIFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTPFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTTDSUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTVolumeUpload)) { // Step 1: Flatten and clean all expected headers from DTO expectedHeaders = lstUploadTemplateColumnListDTO .SelectMany(x => x.DestinationColoumnName .Split(',', StringSplitOptions.RemoveEmptyEntries)) .Select(h => h .ToLower() .Replace(" ", "") .Replace("_", "") .Replace("\"", "") .Replace("'", "") .Replace("|", "") .Trim()) .ToList(); int maxRowsToScanForHeader = 30; string firstLine = File.ReadLines(strFilePath).FirstOrDefault() ?? ""; char detectedDelimiter = firstLine.Contains('|') ? '|' : firstLine.Contains(';') ? ';' : firstLine.Contains('\t') ? '\t' : ','; // default fallback for (int i = 0; i < maxRowsToScanForHeader; i++) { if (!csv.ReadNextRecord()) break; // Join row values using detected delimiter string rawLine = string.Join(detectedDelimiter.ToString(), Enumerable.Range(0, csv.FieldCount).Select(j => csv[j])); // ✅ Replace any non-comma delimiters with comma string normalizedLine = rawLine.Replace('|', ',') .Replace(';', ',') .Replace('\t', ','); // Split and clean string[] rawCells = normalizedLine.Split(new[] { ',' }, StringSplitOptions.RemoveEmptyEntries); string[] currentRow = rawCells .Select(h => h .ToLower() .Replace(" ", "") .Replace("_", "") .Replace("\"", "") .Replace("'", "") .Trim()) .ToArray(); // Match headers int matchedCount = currentRow.Count(actualCol => expectedHeaders.Contains(actualCol)); if (matchedCount >= expectedHeaders.Count / 2) { actualHeaders = currentRow; headerRowIndex = i; Console.WriteLine("✅ Matched Header Row: " + string.Join(",", actualHeaders)); break; } } if (actualHeaders == null) { string strInvalidHeaderColumns = "Unable to detect header row in uploaded file."; General.CreateCodeLog("Step - 32.4", "Header row not found or empty", "", strMethodName, null); await UpdateFileUploadStatus( intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidHeaderColumns, strConnectionString, EncryptionKey, 0, IntErrorLog, null, SiteId); } // Add only matching columns to DataTable dtReturnTable = new DataTable(); Dictionary matchingColumnIndexes = new Dictionary(); var cleanedExpectedHeaderList = expectedHeaders .SelectMany(h => h.Split(',')) .Select(h => h.ToLower().Replace(" ", "").Replace("_", "").Trim()) .Where(h => !string.IsNullOrEmpty(h)) .Distinct() .ToList(); HashSet addedColumnNames = new HashSet(StringComparer.OrdinalIgnoreCase); for (int colIndex = 0; colIndex < actualHeaders.Length; colIndex++) { string cleanedHeader = actualHeaders[colIndex] .ToLower().Replace(" ", "").Replace("_", "").Trim(); if (cleanedExpectedHeaderList.Contains(cleanedHeader)) { try { string matchedDestColName = lstUploadTemplateColumnListDTO .FirstOrDefault(x => (x.DestinationColoumnName ?? "") .Split(',', StringSplitOptions.RemoveEmptyEntries) .Select(d => d.ToLower().Replace(" ", "").Replace("_", "").Trim()) .Contains(cleanedHeader) )?.SourceColumnName; if (!string.IsNullOrEmpty(matchedDestColName)) { // Take only the first valid occurrence (prevent duplicate column names) if (!addedColumnNames.Contains(matchedDestColName)) { dtReturnTable.Columns.Add(matchedDestColName); matchingColumnIndexes[colIndex] = matchedDestColName; addedColumnNames.Add(matchedDestColName); // ✅ track added columns } } } catch (Exception ex) { General.CreateCodeLog("Step - 39.1", "Exception adding column: " + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } // Since we already read rows during scan, csv currently at headerRowIndex row // So no action needed here — next ReadNextRecord() will read first data row General.CreateCodeLog("Step - 40", "ReadTextFile - Start reading data rows after header", "", strMethodName, lstSiteListDTO); // Step 1: Detect delimiter //string firstLine3 = File.ReadLines(strFilePath).FirstOrDefault() ?? ""; //char detectedDelimiter1 = firstLine3.Contains('|') ? '|' : // firstLine3.Contains(';') ? ';' : // firstLine3.Contains('\t') ? '\t' : // ','; // fallback to comma if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.FinanceStatementUpload)) { List headerColumns = new List(); // int headerRowIndex = -1; // 🔹 First pass: detect header row using (var readerDel1 = new StreamReader(strFilePath)) using (csv = new LumenWorks.Framework.IO.Csv.CsvReader(readerDel1, false, detectedDelimiter)) { int rowIndex = 0; while (csv.ReadNextRecord() && rowIndex < 50) // check first 50 rows { List rowValues = new List(); for (int i = 0; i < csv.FieldCount; i++) { rowValues.Add(csv[i]?.Trim()); } // normalize row values to lowercase var normalizedRow = rowValues.Select(v => v.ToLowerInvariant()).ToList(); int matchCount = normalizedRow.Count(v => lstUploadTemplateColumnListDTO.Any(col => string.Equals(col.DestinationColoumnName.ToLowerInvariant(), v))); if (matchCount >= 2) // ✅ threshold: at least 2 matches { headerRowIndex = rowIndex; headerColumns = rowValues; Console.WriteLine($"Header row found at index {rowIndex}: {string.Join(", ", headerColumns)}"); break; } rowIndex++; } } // 🔹 Second pass: map and read data if (headerRowIndex == -1) throw new Exception("No valid header row found in first 50 rows."); using (var readerDel2 = new StreamReader(strFilePath)) using (csv = new LumenWorks.Framework.IO.Csv.CsvReader(readerDel2, false, detectedDelimiter)) { int rowIndex = 0; // Skip until header row while (rowIndex <= headerRowIndex && csv.ReadNextRecord()) rowIndex++; // ✅ Build mapping from CSV index → SourceColumnName (using Destination for match) Dictionary columnIndexToSource = new Dictionary(); for (int i = 0; i < headerColumns.Count; i++) { string header = headerColumns[i].Trim().ToLowerInvariant(); var match = lstUploadTemplateColumnListDTO .FirstOrDefault(col => string.Equals(col.DestinationColoumnName.Trim().ToLowerInvariant(), header)); if (match != null) { columnIndexToSource[i] = match.SourceColumnName; // ✅ Save in SourceColumnName Console.WriteLine($"Mapping CSV column '{headerColumns[i]}' -> Source '{match.SourceColumnName}'"); } } // ✅ Read actual data while (csv.ReadNextRecord()) { DataRow row = dtReturnTable.NewRow(); foreach (var kvp in columnIndexToSource) { int colIndex = kvp.Key; string sourceCol = kvp.Value; string fieldValue = csv[colIndex]; row[sourceCol] = string.IsNullOrWhiteSpace(fieldValue) ? DBNull.Value : fieldValue; } dtReturnTable.Rows.Add(row); } } } else { using (var readerDel1 = new StreamReader(strFilePath)) using (csv = new LumenWorks.Framework.IO.Csv.CsvReader(readerDel1, false, detectedDelimiter)) { // Now csv.FieldCount should be the number of columns in the row int rowIndex = 0; while (csv.ReadNextRecord()) { rowIndex++; // ✅ Skip the first record (header) if (rowIndex == 1) continue; int count = csv.FieldCount; Console.WriteLine($"FieldCount: {count}"); try { DataRow Row = dtReturnTable.NewRow(); Console.WriteLine($"Row {dtReturnTable.Rows.Count}: FieldCount={csv.FieldCount}"); foreach (var kvp in matchingColumnIndexes) { int fieldIndex = kvp.Key; string colName = kvp.Value; if (fieldIndex >= 0 && fieldIndex < count) { string fieldValue = csv[fieldIndex]; Console.WriteLine($"Reading field {fieldIndex}='{fieldValue}' for column '{colName}'"); Row[colName] = fieldValue; } else { Console.WriteLine($"Field index {fieldIndex} out of range (FieldCount={csv.FieldCount}), setting DBNull for column '{colName}'"); Row[colName] = DBNull.Value; } } dtReturnTable.Rows.Add(Row); } catch (Exception ex) { General.CreateCodeLog("Step - 40.2", "ReadTextFile - Exception adding row: " + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } } } else { // Your existing logic for other upload types (unchanged) General.CreateCodeLog("Step - 38", "Processing non-FinanceStatementUpload files with existing logic", "", strMethodName, lstSiteListDTO); int fieldCount = csv.FieldCount; DataRow Row; string[] headers = csv.GetFieldHeaders(); if (headers != null && headers.Length > 0) { foreach (string header in headers) { try { dtReturnTable.Columns.Add(header); } catch (Exception ex) { General.CreateCodeLog("Step - 39.1", "Exception adding column: " + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } General.CreateCodeLog("Step - 40", "Reading data rows", "", strMethodName, lstSiteListDTO); while (csv.ReadNextRecord()) { try { Row = dtReturnTable.NewRow(); for (int Field = 0; Field < fieldCount; Field++) { try { Row[Field] = Convert.ToString(csv[Field]); } catch (Exception ex) { General.CreateCodeLog("Step - 40.1", "Exception reading field: " + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } dtReturnTable.Rows.Add(Row); } catch (Exception ex) { General.CreateCodeLog("Step - 40.2", "Exception adding row: " + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } } } } } } } else { using (LumenWorks.Framework.IO.Csv.CsvReader csv = new LumenWorks.Framework.IO.Csv.CsvReader(new StreamReader(strFilePath), true, Delimiter, quotingCharacter, escapeCharacter, commentCharacter, ValueTrimmingOptions.None)) { General.CreateCodeLog("Step - 38", "ReadTextFile - File is Reading and Converted to Data Table", "", strMethodName, lstSiteListDTO); int fieldCount = csv.FieldCount; DataRow Row; dtReturnTable = new DataTable(); string[] headers = csv.GetFieldHeaders(); General.CreateCodeLog("Step - 39", "ReadTextFile - File is Reading Headers", "", strMethodName, lstSiteListDTO); if (headers != null && headers.Length > 0) { foreach (string header in headers) { try { dtReturnTable.Columns.Add(header); } catch (Exception ex) { General.CreateCodeLog("Step - 39.1", "ReadTextFile - exception" + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } General.CreateCodeLog("Step - 40", "ReadTextFile - File is Reading Columns", "", strMethodName, lstSiteListDTO); while (csv.ReadNextRecord()) { try { Row = dtReturnTable.NewRow(); for (int Field = 0; Field < fieldCount; Field++) { try { Row[Field] = Convert.ToString(csv[Field]); } catch (Exception ex) { General.CreateCodeLog("Step - 40.1", "ReadTextFile - exception" + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } dtReturnTable.Rows.Add(Row); } catch (Exception ex) { General.CreateCodeLog("Step - 40.1", "ReadTextFile - exception" + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } } } } catch (Exception ex) { General.CreateCodeLog("Step - 38.3", "ReadTextFile - exception" + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } finally { } } dtReturnTable.Columns.Add(ErrorDescriptionColumnName, typeof(string)); if (strRecordIdColumnName != string.Empty) { dtReturnTable.Columns.Add(strRecordIdColumnName, typeof(Int64)); } dtReturnTable.Columns.Add(RecordCounterColumnName, typeof(Int64)); if (IsPasswordGenerationRequired) { dtReturnTable.Columns.Add(PasswordGenerationColumnName, typeof(string)); } dtReturnTable.Columns.Add("FileUploadId", typeof(Int64)); dtReturnTable.Columns.Add("CompanyId", typeof(int)); dtReturnTable.Columns.Add("SiteId", typeof(int)); dtReturnTable.Columns.Add("CreatedBy", typeof(string)); dtReturnTable.Columns.Add("IPAddress", typeof(string)); if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTDataUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTPeopleCountUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTIFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTPFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTTDSUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTVolumeUpload)) { if (dtReturnTable.Columns.Contains("Time")) { foreach (DataRow row in dtReturnTable.Rows) { if (row["Time"] != DBNull.Value && !string.IsNullOrWhiteSpace(row["Time"].ToString())) { string timeRaw = row["Time"].ToString(); // Try to parse as DateTime if (DateTime.TryParse(timeRaw, out DateTime parsedTime)) { // Replace with only time part in HH:mm:ss format row["Time"] = parsedTime.ToString("HH:mm:ss"); } else { // Handle fallback: If it's just a time (like from CSV), keep it as-is // OR optionally normalize to HH:mm:ss if it's like "11:00" if (TimeSpan.TryParse(timeRaw, out TimeSpan parsedSpan)) { row["Time"] = new DateTime(parsedSpan.Ticks).ToString("HH:mm:ss"); } } } } } } // Add DateCreated only if it's not an IOTColumnMapper upload dtReturnTable.Columns.Add("DateCreated", typeof(DateTime)); if (IsStoreHeaderInSuccess) { dtReturnTable.Columns.Add("HeaderInformation", typeof(string)); } if (IsStoreErrorRecordInSuccess) { dtReturnTable.Columns.Add("IsErroneous", typeof(bool)); } dtReturnTable.AcceptChanges(); General.CreateCodeLog("Step - 41", "ReadTextFile - Assign columns to data table and be fore checking engine.ErrorManager.ErrorCount ", "", strMethodName, lstSiteListDTO); if ((HeaderLineText != string.Empty && TrailerLineText != string.Empty && (HeaderInformation.ToLower().Contains(HeaderLineText.ToLower()) && TrailerInformation.ToLower().Contains(TrailerLineText.ToLower())) && InvalidErrorRecords == 0) || (intHeaderRecordCountStartPosition > 0 && intHeaderRecordCountLength > 0 && InvalidErrorRecords == 0)) { if (dtReturnTable != null) { if (intTrailerRecord == 1) { TableRecordCount = dtReturnTable.Rows.Count; } else { TableRecordCount = dtReturnTable.Rows.Count - 1;//Except trailer row } General.CreateCodeLog("Step - 43", "ReadTextFile - Before if condition for intHeaderRecordCountStartPosition,intHeaderRecordCountLength", "", strMethodName, lstSiteListDTO); if (intHeaderRecordCountStartPosition == 0 && intHeaderRecordCountLength == 0) { TrailerInformation = TrailerInformation.Trim(); TrailerInformation = TrailerInformation.Replace(TrailerLineText, string.Empty); //Removing "TLR" Text from Trailer TrailerRecordCount = Convert.ToInt64(TrailerInformation); if (TrailerRecordCount == TableRecordCount) { HeaderInformation = HeaderInformation.Trim(); HeaderInformation = HeaderInformation.Replace(HeaderLineText, string.Empty);//Removing "HDR" Text from Header if (HeaderInformation != string.Empty)// { DateTime dtCurrentDate = RevalTimeZone.GetDateByTimezone(DateTime.Now, TimeZone, NoOfHours, NoOfMinutes); dtCurrentDate = dtCurrentDate.AddDays(-5); //DateTime dtHeaderDate = Convert.ToDateTime(strHeaderDate); DateTime dt2 = DateTime.ParseExact(HeaderInformation, strHeaderDateFormat, CultureInfo.InvariantCulture); if (dt2.Date >= dtCurrentDate.Date && dt2.Date <= dtCurrentDate.Date) { if (intTrailerRecord == 0) { dtReturnTable.Rows[dtReturnTable.Rows.Count - 1].Delete(); dtReturnTable.AcceptChanges(); } } else { //Update tblFileUpload Status as invalid file General.CreateCodeLog("Step - 44", "ReadTextFile - header date not match - invalid file", "", strMethodName, lstSiteListDTO); dtReturnTable = null; strInvalidReason = "Header date not match "; ; await UpdateFileUploadStatus(intFileUploadId, FileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); } } else { //Update tblFileUpload Status as invalid file General.CreateCodeLog("Step - 44", "ReadTextFile - header contains string.empty - invalid file", "", strMethodName, lstSiteListDTO); dtReturnTable = null; strInvalidReason = "header contains string.empty."; ; await UpdateFileUploadStatus(intFileUploadId, FileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); } } else { //Update tblFileUpload Status as invalid file General.CreateCodeLog("Step - 44", "ReadTextFile - Header and Trailer record count not matched - invalid file", "", strMethodName, lstSiteListDTO); dtReturnTable = null; strInvalidReason = "Header and Trailer record count not matched"; await UpdateFileUploadStatus(intFileUploadId, FileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); } } else { //Match header record count in uploaded files General.CreateCodeLog("Step - 44", "ReadTextFile -start of Match header record count in uploaded files", "", strMethodName, lstSiteListDTO); Int64 intRecordCount = 0; string strSubString = string.Empty; if (HeaderInformation != string.Empty && HeaderInformation.Length > intHeaderRecordCountStartPosition + intHeaderRecordCountLength) { strSubString = HeaderInformation.Substring(intHeaderRecordCountStartPosition - 1, intHeaderRecordCountLength); Int64.TryParse(strSubString, out intRecordCount); TableRecordCount = dtReturnTable.Rows.Count; if (intRecordCount != TableRecordCount) { //Update tblFileUpload Status as invalid file General.CreateCodeLog("Step - 44", "ReadTextFile -header record count not matched - record length - Invalid file", "", strMethodName, lstSiteListDTO); dtReturnTable = null; strInvalidReason = "Header record count not matched - record length"; await UpdateFileUploadStatus(intFileUploadId, FileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); } } else { General.CreateCodeLog("Step - 44", "ReadTextFile -header is empty or HeaderInformation Length not contains the-intHeaderRecordCountStartPosition + intHeaderRecordCountLength- record length - Invalid file", "", strMethodName, lstSiteListDTO); //Update tblFileUpload Status as invalid file dtReturnTable = null; strInvalidReason = "Header is empty or HeaderInformation Length not contains the-intHeaderRecordCountStartPosition + intHeaderRecordCountLength- record length"; await UpdateFileUploadStatus(intFileUploadId, FileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); } } } } else if (HeaderLineText == string.Empty && TrailerLineText == string.Empty) { return dtReturnTable; } else { //Invalid File return Null dtReturnTable = null; //Update tblFileUpload Status as invalid file General.CreateCodeLog("Step - 44", "ReadTextFile -last else - Invalid file", "", strMethodName, lstSiteListDTO); strInvalidReason = "Header record count not matched"; await UpdateFileUploadStatus(intFileUploadId, FileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidReason, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, SiteId); } } } catch (Exception ex) { General.CreateCodeLog("Step - 44", "ReadTextFile - exception" + ex.Message, "", strMethodName, lstSiteListDTO); dtReturnTable = null; General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } return dtReturnTable; } #endregion #region Update File Upload Status //************************************************************************************************************* // Purpose : To update the file upload status by file uploadid // Layer : Service // Method Name : UpdateFileUploadStatus // Input Parameters : // Return Values : Nothing // ------------------------------------------------------------------------------------------------------------ // Version Author Date Remarks // ----------------------------------------------------------------------------------------------------------- // Sanjay kalwa 1.0.0 20 Aug 2014 Creation //************************************************************************************************************* private async Task UpdateFileUploadStatus(Int64 FileUploadId, int FileUploadTypeId, string CreatedBy, int FileUploadStatusId, bool IsProcessed, bool IsUpdateRecords, Int64 TotalRecords, Int64 ProcessedRecords, Int64 ErrorRecords, string InvalidReason, string strConnectionString, string EncryptionKey, int LogTypeId, int IntErrorLog, List lstSiteListDTO, int SiteId) { string strMethodName = MethodBase.GetCurrentMethod()?.Name ?? string.Empty; FileUploadServiceListDTO objFileUploadListDTO = null; FileUploadBAL objFileUploadBAL = null; DateTime dtCurrentDate = RevalTimeZone.GetDateByTimezone(DateTime.Now, TimeZone, NoOfHours, NoOfMinutes); try { General.CreateCodeLog("Step - i", "Start of UpdateFileUploadStatus Method", "", strMethodName, lstSiteListDTO); objFileUploadBAL = new FileUploadBAL(strConnectionString, EncryptionKey, _ConfigurationSettingsListDTO); //objFileUploadBAL = new FileUploadBAL(_configurationSettingsListDto); objFileUploadListDTO = new FileUploadServiceListDTO(); objFileUploadListDTO.CompanyId = _ConfigurationSettingsListDTO.CompanyId; objFileUploadListDTO.SiteId = SiteId; objFileUploadListDTO.FileUploadId = FileUploadId; objFileUploadListDTO.FileUploadTypeId = FileUploadTypeId; objFileUploadListDTO.CreatedBy = CreatedBy; objFileUploadListDTO.DateCreated = dtCurrentDate; objFileUploadListDTO.IPAddress = _ConfigurationSettingsListDTO.IPAddress; objFileUploadListDTO.FileUploadStatusId = FileUploadStatusId; objFileUploadListDTO.IsProcessed = IsProcessed; objFileUploadListDTO.IsUpdateRecords = IsUpdateRecords; objFileUploadListDTO.TotalRecords = TotalRecords; objFileUploadListDTO.ProcessedRecords = ProcessedRecords; objFileUploadListDTO.ErrorRecords = ErrorRecords; objFileUploadListDTO.IsFromSuccessService = false; objFileUploadListDTO.InvalidReason = InvalidReason; await objFileUploadBAL.UpdateFileUploadIsProcessedByIdAsync(objFileUploadListDTO); General.CreateCodeLog("Step - ii", "End of UpdateFileUploadStatus Method", "", strMethodName, lstSiteListDTO); } catch (Exception ex) { General.CreateCodeLog("Step - iii", "Exception in UpdateFileUploadStatus Method" + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } #endregion #region Create Work Book public async Task CreateWorkbookAsync(string filePath, DataTable dtResultSet, int SiteLogTypeId, int IntErrorLog, string SiteConnectionString, string EncryptionKey, List lstSiteListDTO) { if (dtResultSet != null) { string strMethodName = MethodBase.GetCurrentMethod()?.Name ?? string.Empty; try { int RowCount = 0; int TempRowCount = 0; General.CreateCodeLog("Step - iv", "start of CreateWorkbook method", "", strMethodName, lstSiteListDTO); var workbook = new ExcelFile(); var workbook1 = new ExcelFile(); string strFilePath = string.Empty; string strFileName = string.Empty; // Add new worksheet to Excel file. //var worksheet = workbook.Worksheets.Add("Sheet1"); RowCount = dtResultSet.Rows.Count; TempRowCount = RowCount; int pageNum = 1; int pageSize = WorkBookPageSize; if (RowCount > pageSize) { DataTable dtPage = dtResultSet.Copy(); var worksheet = workbook.Worksheets.Add("Sheet1"); worksheet.InsertDataTable(dtPage, 0, 0, true); string Extension = Path.GetExtension(filePath); if (Extension.ToLower() == General.xlsxFileExtension.ToLower()) { workbook.SaveXlsx(filePath); } else if (Extension.ToLower() == General.xlsFileExtension.ToLower()) { workbook.SaveXls(filePath); } else if (Extension.ToLower() == General.csvFileExtnsion.ToLower()) { workbook.SaveCsv(filePath, ','); } workbook.ClosePreservedXlsx(); while (dtPage != null) { try { workbook1 = new ExcelFile(); //worksheet.Delete(); var worksheet1 = workbook1.Worksheets.Add("Sheet1"); if (dtPage != null) { pageNum = pageNum + 1; TempRowCount = TempRowCount - pageSize; if (TempRowCount <= pageSize) { dtPage = dtResultSet.Rows.Cast().Skip((pageNum - 1) * pageSize).Take(TempRowCount).CopyToDataTable(); //worksheet1.InsertDataTable(dtPage, pageSize * (pageNum - 1), 0, true); worksheet1.InsertDataTable(dtPage, 0, 0, true); TempRowCount = 0; dtPage = null; } else { dtPage = dtResultSet.Rows.Cast().Skip((pageNum - 1) * pageSize).Take(pageSize).CopyToDataTable(); //worksheet1.InsertDataTable(dtPage, pageSize * (pageNum - 1), 0, true); worksheet1.InsertDataTable(dtPage, 0, 0, true); } } // workbook.Worksheets.AddCopy("Sheet" + (pageNum).ToString(), worksheet1); Extension = string.Empty; Extension = Path.GetExtension(filePath); strFileName = Path.GetFileNameWithoutExtension(filePath); strFilePath = filePath.Replace(strFileName, strFileName + pageNum.ToString()); //strFilePath = filePath.Replace(strFileName+Extension, string.Empty); if (Extension.ToLower() == General.xlsxFileExtension.ToLower()) { workbook1.SaveXlsx(strFilePath); } else if (Extension.ToLower() == General.xlsFileExtension.ToLower()) { workbook1.SaveXls(strFilePath); } else if (Extension.ToLower() == General.csvFileExtnsion.ToLower()) { workbook1.SaveCsv(strFilePath, ','); } workbook1.ClosePreservedXlsx(); //workbook.Worksheets.Union(IEnumerable worksheet1); worksheet1.Delete(); worksheet1 = null; } catch (Exception ex) { General.CreateCodeLog("Step - iv", "Exception in CreateWorkbook method " + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } else { var worksheet = workbook.Worksheets.Add("Sheet1"); worksheet.InsertDataTable(dtResultSet, 0, 0, true); string Extension = Path.GetExtension(filePath); if (Extension.ToLower() == General.xlsxFileExtension.ToLower()) { workbook.SaveXlsx(filePath); } else if (Extension.ToLower() == General.xlsFileExtension.ToLower()) { workbook.SaveXls(filePath); } else if (Extension.ToLower() == General.csvFileExtnsion.ToLower()) { workbook.SaveCsv(filePath, ','); } workbook.ClosePreservedXlsx(); } General.CreateCodeLog("Step - v", "end of CreateWorkbook method", "", strMethodName, lstSiteListDTO); } catch (Exception ex) { General.CreateCodeLog("Step - vi", "exception in CreateWorkbook method" + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } #endregion #region Update File Paths in tblFileUpload protected async Task SaveFileUploadPathAsync(Int64 FileUploadId, string strUserName, string SuccessFilePath, string SuccessFileName, string ErrorFilePath, string ErrorFileName, string strConnectionString, string EncryptionKey, int LogTypeId, int IntErrorLog, List lstSiteListDTO) { FileUploadBAL objFileUploadBAL = null; FileUploadServiceListDTO objFileUploadListDTO = null; string strMethodName = MethodBase.GetCurrentMethod()?.Name ?? string.Empty; DateTime dtCurrentDate = RevalTimeZone.GetDateByTimezone(DateTime.Now, TimeZone, NoOfHours, NoOfMinutes); try { General.CreateCodeLog("Step - vii", "Start of SaveFileUploadPath Method", "", strMethodName, lstSiteListDTO); objFileUploadListDTO = new FileUploadServiceListDTO(); objFileUploadListDTO.FileUploadId = FileUploadId; objFileUploadListDTO.LastUpdated = dtCurrentDate; objFileUploadListDTO.IPAddress = _ConfigurationSettingsListDTO.IPAddress; objFileUploadListDTO.SuccessFilePath = SuccessFilePath; objFileUploadListDTO.ErrorFilePath = ErrorFilePath; objFileUploadListDTO.UpdatedBy = strUserName; objFileUploadListDTO.SuccessFileName = SuccessFileName; objFileUploadListDTO.ErrorFileName = ErrorFileName; objFileUploadListDTO.IsFromSuccessService = false; objFileUploadBAL = new FileUploadBAL(strConnectionString, EncryptionKey, _ConfigurationSettingsListDTO); await objFileUploadBAL.UpdateFileUploadPathAsync(objFileUploadListDTO); General.CreateCodeLog("Step - viii", "End of SaveFileUploadPath Method", "", strMethodName, lstSiteListDTO); } catch (Exception ex) { General.CreateCodeLog("Step - ix", "Exception in SaveFileUploadPath Method" + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } finally { objFileUploadListDTO = null; objFileUploadBAL = null; } } #endregion #region Map and Import To Success Table private async Task ImportDataToSuccessTableAsync(DataTable dtSuccessDetails, string strSuccessTableName, List lstUploadTemplateColumnListDTO, string RecordCounterColumnName, string PasswordGenerationColumnName, bool IsStoreHeader, string strErrorDescriptionColumnName, string strIsErroneousColumnName, string strConnectionString, string EncryptionKey, int LogTypeId, int IntErrorLog, List lstSiteListDTO, int intFileUploadTypeId, int intSplitCount) { string strMethodName = MethodBase.GetCurrentMethod()?.Name ?? string.Empty; General.CreateCodeLog("Step - x", "start of ImportDataToSuccessTable", "", strMethodName, lstSiteListDTO); string strIsSuccess = "1"; string strMessage = string.Empty; String[] ReturnArray = new String[2]; FileUploadBAL objFileUploadBAL = null; try { objFileUploadBAL = new FileUploadBAL(strConnectionString, EncryptionKey, _ConfigurationSettingsListDTO); await objFileUploadBAL.ImportToSuccessTableAsync(dtSuccessDetails, strSuccessTableName, lstUploadTemplateColumnListDTO, RecordCounterColumnName, PasswordGenerationColumnName, IsStoreHeader, strErrorDescriptionColumnName, strIsErroneousColumnName, intFileUploadTypeId, lstSiteListDTO, intSplitCount); } catch (Exception ex) { strIsSuccess = "0"; strMessage = ex.Message; General.CreateCodeLog("Step - xii", "Exception in ImportDataToSuccessTable" + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } finally { General.CreateCodeLog("Step - xi", "End of ImportDataToSuccessTable", "", strMethodName, lstSiteListDTO); objFileUploadBAL = null; } ReturnArray[0] = strIsSuccess; ReturnArray[1] = string.Empty; return ReturnArray; } #endregion #region ValidateHeaders protected async Task ValidateHeadersAsync(string strFileName, List lstUploadTemplateColumnListDTO, Int64 intFileUploadId, int intFileUploadTypeId, string strUserName, bool IsCustomDelimiter, char Delimiter, string strConnectionString, string EncryptionKey, int LogTypeId, string strInvalidHeaderColumns, int IntErrorLog, List lstSiteDetails, int SiteId, bool IsMandatoryAllField, bool IsValidateDuplicateHeader) { List strHeaders = null; bool IsHeaderCheckingSuccess = false; string strMethodName = MethodBase.GetCurrentMethod()?.Name ?? string.Empty; bool IsCheckMandatoryAllField = IsMandatoryAllField; // 🔁 Set this to true if all fields (mandatory + non-mandatory) should be validated try { General.CreateCodeLog("Step - 32", "Start of ValidateHeaders Method", "", strMethodName, lstSiteDetails); if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.FinanceStatementUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTDataUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTPeopleCountUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTIFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTPFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTTDSUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTVolumeUpload)) { List allLines = File.ReadAllLines(strFileName).ToList(); if (!allLines.Any()) return false; (char delimiter, bool isQuotedCsv) = DetectDelimiter(allLines); int headerRowIndex = -1; List fileHeaders = new List(); if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.FinanceStatementUpload)) { // STEP 1: Get all expected header aliases var expectedHeaderAliases = lstUploadTemplateColumnListDTO .SelectMany(column => (column.DestinationColoumnName ?? "") .Split(',', StringSplitOptions.RemoveEmptyEntries) .Select(alias => alias.ToLower().Replace(" ", "").Replace("_", "").Trim())) .Distinct() .ToHashSet(); // For fast lookup int matchThreshold = 3; // You can adjust this number based on how strict you want matching to be // STEP 2: Loop over the first 50 lines to detect header for (int i = 0; i < Math.Min(50, allLines.Count); i++) { var fields = SplitCsvLine(allLines[i], delimiter, isQuotedCsv); var cleanedFields = fields .Select(h => h.ToLower().Replace(" ", "").Replace("_", "").Trim()) .Where(h => !string.IsNullOrWhiteSpace(h)) .ToList(); if (cleanedFields.Count >= 2) { // Count how many cleaned fields match expected headers int matchCount = cleanedFields.Count(h => expectedHeaderAliases.Contains(h)); if (matchCount >= matchThreshold) { headerRowIndex = i; General.CreateCodeLog("Step - 32.1", "Header row identified based on expected matches", $"RowIndex={i}", strMethodName, lstSiteDetails); if (IsValidateDuplicateHeader) { fileHeaders = cleanedFields; var duplicateHeaders = fileHeaders .GroupBy(h => h) .Where(g => g.Count() > 1) .Select(g => g.Key) .ToList(); if (duplicateHeaders.Any()) { General.CreateCodeLog("Step - 32.2", "Duplicate headers found", string.Join(", ", duplicateHeaders), strMethodName, lstSiteDetails); strInvalidHeaderColumns = "Duplicate column header(s) found: " + string.Join(", ", duplicateHeaders); await UpdateFileUploadStatus( intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidHeaderColumns, strConnectionString, EncryptionKey, LogTypeId, IntErrorLog, lstSiteDetails, SiteId); return false; } } else { var seenHeaders = new HashSet(StringComparer.OrdinalIgnoreCase); fileHeaders = new List(); foreach (var header in cleanedFields) { if (!seenHeaders.Contains(header)) { fileHeaders.Add(header); seenHeaders.Add(header); } } General.CreateCodeLog("Step - 32.3", "Duplicate headers ignored, using first occurrences", string.Join(", ", fileHeaders), strMethodName, lstSiteDetails); } break; // We found our header, exit loop } } } } else { for (int i = 0; i < Math.Min(50, allLines.Count); i++) { var fields = SplitCsvLine(allLines[i], delimiter, isQuotedCsv); var cleanedFields = fields .Select(h => h.ToLower().Replace(" ", "").Replace("_", "").Trim()) .Where(h => !string.IsNullOrWhiteSpace(h)) .ToList(); if (cleanedFields.Count >= 2) { headerRowIndex = i; General.CreateCodeLog("Step - 32.1", "Header row identified", $"RowIndex={i}", strMethodName, lstSiteDetails); if (IsValidateDuplicateHeader) { fileHeaders = cleanedFields; var duplicateHeaders = fileHeaders .GroupBy(h => h) .Where(g => g.Count() > 1) .Select(g => g.Key) .ToList(); if (duplicateHeaders.Any()) { General.CreateCodeLog("Step - 32.2", "Duplicate headers found", string.Join(", ", duplicateHeaders), strMethodName, lstSiteDetails); strInvalidHeaderColumns = "Duplicate column header(s) found: " + string.Join(", ", duplicateHeaders); await UpdateFileUploadStatus( intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidHeaderColumns, strConnectionString, EncryptionKey, LogTypeId, IntErrorLog, lstSiteDetails, SiteId); return false; } } else { var seenHeaders = new HashSet(StringComparer.OrdinalIgnoreCase); fileHeaders = new List(); foreach (var header in cleanedFields) { if (!seenHeaders.Contains(header)) { fileHeaders.Add(header); seenHeaders.Add(header); } } General.CreateCodeLog("Step - 32.3", "Duplicate headers ignored, using first occurrences", string.Join(", ", fileHeaders), strMethodName, lstSiteDetails); } break; } } } if (headerRowIndex == -1 || fileHeaders.Count == 0) { strInvalidHeaderColumns = "Unable to detect header row in uploaded file."; General.CreateCodeLog("Step - 32.4", "Header row not found or empty", "", strMethodName, lstSiteDetails); await UpdateFileUploadStatus( intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidHeaderColumns, strConnectionString, EncryptionKey, LogTypeId, IntErrorLog, lstSiteDetails, SiteId); return false; } var requiredColumns = IsCheckMandatoryAllField ? lstUploadTemplateColumnListDTO : lstUploadTemplateColumnListDTO.Where(x => x.IsMandatory).ToList(); List missingFields = new List(); foreach (var column in requiredColumns) { var destinationAliases = (column.DestinationColoumnName ?? "") .Split(',', StringSplitOptions.RemoveEmptyEntries) .Select(alias => alias.ToLower().Replace(" ", "").Replace("_", "").Trim()) .Distinct() .ToList(); bool matched = destinationAliases.Any(alias => fileHeaders.Contains(alias)); if (!matched) { missingFields.Add(column.SourceColumnName); } } if (missingFields.Any()) { strInvalidHeaderColumns = (IsCheckMandatoryAllField ? "Missing expected column(s): " : "Missing mandatory header(s): ") + string.Join(", ", missingFields); General.CreateCodeLog("Step - 32.5", "Missing required headers", strInvalidHeaderColumns, strMethodName, lstSiteDetails); await UpdateFileUploadStatus( intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidHeaderColumns, strConnectionString, EncryptionKey, LogTypeId, IntErrorLog, lstSiteDetails, SiteId); return false; } IsHeaderCheckingSuccess = true; General.CreateCodeLog("Step - 33", "Completed header validation successfully", "", strMethodName, lstSiteDetails); } else { using (LumenWorks.Framework.IO.Csv.CsvReader csv = new LumenWorks.Framework.IO.Csv.CsvReader(new StreamReader(strFileName), true, Delimiter)) { int fieldCount = csv.FieldCount; string[] headers = csv.GetFieldHeaders(); strHeaders = new List(); if (headers != null && headers.Length > 0) { foreach (string header in headers) { string strHeaderName = header; strHeaderName = strHeaderName.Replace(" ", string.Empty); strHeaderName = strHeaderName.Replace("_", string.Empty); strHeaders.Add(strHeaderName.ToLower()); } strHeaders = strHeaders.Select(d => d.Trim()).ToList(); General.CreateCodeLog("Step - 34", "Headers fetched using LumenWorks", string.Join(", ", strHeaders), strMethodName, lstSiteDetails); if (lstUploadTemplateColumnListDTO != null && lstUploadTemplateColumnListDTO.Count > 0) { List lstImportedColumns = new List(); if (intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.FinanceStatementUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTDataUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IOTPeopleCountUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTIFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTPFRUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTTDSUpload) || intFileUploadTypeId == Convert.ToInt32(General.FileUploadType.IoTVolumeUpload)) { lstImportedColumns = (from lstUploadTemplateColumn in lstUploadTemplateColumnListDTO where lstUploadTemplateColumn.IsMandatory == true select lstUploadTemplateColumn.DestinationColoumnName.ToLower()).ToList(); } else { lstImportedColumns = (from lstUploadTemplateColumn in lstUploadTemplateColumnListDTO where lstUploadTemplateColumn.IsMandatory == true select lstUploadTemplateColumn.ClientColumnName.ToLower()).ToList(); } var strExtraColumns = string.Join(", ", lstImportedColumns.Except(strHeaders).ToArray()); if (strExtraColumns != string.Empty) { IsHeaderCheckingSuccess = false; strInvalidHeaderColumns = "Invalid header column name(s): " + strExtraColumns.TrimEnd(',').Replace(",", ", "); General.CreateCodeLog("Step - 35", "Header validation failed", strInvalidHeaderColumns, strMethodName, lstSiteDetails); await UpdateFileUploadStatus(intFileUploadId, intFileUploadTypeId, strUserName, Convert.ToInt32(General.FileUploadStatus.Invalid_File), true, false, 0, 0, 0, strInvalidHeaderColumns, strConnectionString, EncryptionKey, LogTypeId, IntErrorLog, lstSiteDetails, SiteId); } else { IsHeaderCheckingSuccess = true; General.CreateCodeLog("Step - 36", "LumenWorks header validation passed", "", strMethodName, lstSiteDetails); } } } else { IsHeaderCheckingSuccess = false; General.CreateCodeLog("Step - 37", "LumenWorks: No headers found", "", strMethodName, lstSiteDetails); } } } General.CreateCodeLog("Step - 33", "End of ValidateHeaders Method", "", strMethodName, lstSiteDetails); } catch (Exception ex) { General.CreateCodeLog("Step - 34", "Exception in ValidateHeaders Method" + ex.Message, "", strMethodName, lstSiteDetails); General.CreateErrorLog(ex, strMethodName, lstSiteDetails, null, null); // Added by Subbarao B on Feb 05 2026 } finally { lstUploadTemplateColumnListDTO = null; strHeaders = null; } return IsHeaderCheckingSuccess; } #endregion ValidateHeaders #region Check IsNumeric public static System.Boolean IsNumeric(System.Object Expression) { if (Expression == null || Expression is DateTime) return false; if (Expression is Int16 || Expression is Int32 || Expression is Int64 || Expression is Decimal || Expression is Single || Expression is Double || Expression is Boolean) return true; try { if (Expression is string) Double.Parse(Expression as string); else Double.Parse(Expression.ToString()); return true; } catch { } // just dismiss errors but return false return false; } #endregion Check Isnumeric #region Import Download Files //------------------------------------------------------------------------------------------------------------------- // Method Name : ImportDownloadFiles // Method Description : This method will import valid Download files to tblFileUpload. // Author Name : Neeta Deshpande // Create Date : 21 Jul 2014 // Modified Date : // Modified Name : // Modified Reason : //-------------------------------------------------------------------------------------------------------------------- /// /// ImportData: This method will import valid FTP files to tblFileUpload. /// protected async Task ImportDownloadFiles(string DownloadFilesPath, string UploadSuccessPath, int intUploadTemplateId, bool IsDecryptFile, string DecryptFilePath, string strConnectionString, string EncryptionKey, int SiteLogTypeId, int IntErrorLog, List lstSiteListDTO, int ModuleId) { string strMethodName = MethodBase.GetCurrentMethod()?.Name ?? string.Empty; General.CreateCodeLog("Step 5.1", "Start of ImportDownloadFiles Method", "", strMethodName, lstSiteListDTO, null, strServiceName); int ErrorCode = 0, intCountryId = 0, intLanguageId = 0, intCurrencyId = 0, intRequestTimeOut = 0; CommonBAL objCommonBAL = null; RESTAPIListDTO objRESTAPIListDTO = null; DefaultUserTokenListDTO objDefaultUserTokenListDTO = null; bool IsDefaultUserToken = true; string strToken = string.Empty, JWTToken = string.Empty, Token = string.Empty; DateTime dtCurrentDate = RevalTimeZone.GetDateByTimezone(DateTime.Now, TimeZone, NoOfHours, NoOfMinutes); try { if (!Directory.Exists(DownloadFilesPath)) { Directory.CreateDirectory(DownloadFilesPath); } if (IsDecryptFile && !Directory.Exists(DecryptFilePath)) { Directory.CreateDirectory(DecryptFilePath); } string[] filters = new[] { "*.xlsx", "*.csv", "*.txt", "*.xls" }; string[] excelFiles = filters.SelectMany(f => Directory.GetFiles(DownloadFilesPath, f)).ToArray(); foreach (string strFile in excelFiles) { #region JwtToken if (ErrorCode == 0) { objCommonBAL = new CommonBAL(_ConfigurationSettingsListDTO, strConnectionString); // ✅ 1. Check if default user token feature is enabled var objDefaultUserToken = objCommonBAL.GetSiteConfiguration( Convert.ToInt32(General.SiteConfiguration.IsDefaultUserToken), intCountryId, intLanguageId, intCurrencyId ); bool.TryParse(objDefaultUserToken?.ToString(), out IsDefaultUserToken); if (IsDefaultUserToken) { try { // ✅ 2. Prepare AES keys string strAESEncryptkey = !string.IsNullOrEmpty(_ConfigurationSettingsListDTO.AESEncryptkey) ? clsCryptography.Decrypt(_ConfigurationSettingsListDTO.AESEncryptkey, _ConfigurationSettingsListDTO.EncryptionKey) : string.Empty; string strAESEncryptiv = !string.IsNullOrEmpty(_ConfigurationSettingsListDTO.AESEncryptiv) ? clsCryptography.Decrypt(_ConfigurationSettingsListDTO.AESEncryptiv, _ConfigurationSettingsListDTO.EncryptionKey) : string.Empty; // ✅ 3. Get token details FileUploadBAL objFileUploadBAL = new FileUploadBAL(strConnectionString, EncryptionKey, _ConfigurationSettingsListDTO); DataSet dsDefaultUser = objFileUploadBAL.GetDefaultUserDetails(strConnectionString); string strUserToken = string.Empty, strUserName = string.Empty, strUserPassword = string.Empty, strUserTokenGuid = string.Empty; DateTime? TokenExpiryDate = null; if (dsDefaultUser?.Tables.Count > 1) { DataTable dtDefaultUserToken = dsDefaultUser.Tables[0]; DataTable dtDefaultUser = General.DecryptDataTable(dsDefaultUser.Tables[1], strAESEncryptkey, strAESEncryptiv); // ✅ Extract UserToken info if (dtDefaultUserToken.Rows.Count > 0) { var tokenRow = dtDefaultUserToken.Rows[0]; strUserToken = tokenRow["UserToken"]?.ToString() ?? string.Empty; strUserTokenGuid = tokenRow["Id"]?.ToString() ?? string.Empty; //DateTime.TryParse(tokenRow["ExpiryDate"]?.ToString(), out DateTime parsedExpiry1); //TokenExpiryDate = parsedExpiry1; } // ✅ Extract Default User credentials if (dtDefaultUser.Rows.Count > 0) { var userRow = dtDefaultUser.Rows[0]; strUserName = userRow["UserName"]?.ToString() ?? string.Empty; strUserPassword = userRow["UserPassword"]?.ToString() ?? string.Empty; strUserPassword = clsCryptography.Decrypt(strUserPassword, EncryptionKey); strUserPassword = General.Encrypt(strUserPassword, EncryptionKey); DateTime.TryParse(userRow["ExpiryDate"]?.ToString(), out DateTime parsedExpiry); TokenExpiryDate = parsedExpiry; } } // ✅ 4. Check if token is still valid if (!string.IsNullOrWhiteSpace(strUserToken) && TokenExpiryDate > dtCurrentDate) { strToken = strUserToken; } else { // ✅ 5. Get API URLs in one go string APIURL = objCommonBAL.GetSiteConfiguration((int)General.SiteConfiguration.APIURL, intCountryId, intLanguageId, intCurrencyId)?.ToString() ?? string.Empty; string LoginAPIURL = objCommonBAL.GetSiteConfiguration((int)General.SiteConfiguration.LoginAPIURL, intCountryId, intLanguageId, intCurrencyId)?.ToString() ?? string.Empty; string JWTTokenURL = objCommonBAL.GetSiteConfiguration((int)General.SiteConfiguration.JWTTokenURL, intCountryId, intLanguageId, intCurrencyId)?.ToString() ?? string.Empty; string APIRequestTimeOut = objCommonBAL.GetSiteConfiguration((int)General.SiteConfiguration.APIRequestTimeOut, intCountryId, intLanguageId, intCurrencyId)?.ToString() ?? string.Empty; intRequestTimeOut = Convert.ToInt32(APIRequestTimeOut); string strContentType = "application/json"; string strMethodType = "POST"; General.CreateCodeLog("Step 3.16a", "Before calling of CallRestAPI method.", "", strMethodName, lstSiteListDTO, null, ""); // ✅ JWT Token API Call string strRequestURL = $"{APIURL}/{JWTTokenURL}"; var requestData = new { SessionID = Guid.NewGuid().ToString(), SiteURL = lstSiteListDTO[0].SiteURL }; string postData = JsonConvert.SerializeObject(requestData, Formatting.Indented); objRESTAPIListDTO = new RESTAPIListDTO { APIRequest = postData, APIURL = strRequestURL, ContentType = strContentType, HttpVerb = strMethodType, RequestTimeOut = intRequestTimeOut }; JWTToken = General.CallJWTRestAPI(objRESTAPIListDTO, lstSiteListDTO[0].SiteCode, lstSiteListDTO[0].SiteId, lstSiteListDTO); if (!string.IsNullOrEmpty(JWTToken)) { var jsonObject = JObject.Parse(JWTToken); Token = jsonObject["SecurityToken"]?.ToString(); if (!string.IsNullOrEmpty(Token)) { //getting upk from token //var handler = new JwtSecurityTokenHandler(); //var jwtToken = handler.ReadJwtToken(Token); //var upkClaim = jwtToken.Claims.FirstOrDefault(c => c.Type == "upk")?.Value; //string UserPrivateKey = upkClaim; //DateTime? expiryDate = jwtToken.ValidTo; } } General.CreateCodeLog("Step 3.16b Token ", Token, "", strMethodName, lstSiteListDTO, null, ""); // ✅ Login API Call if (!string.IsNullOrWhiteSpace(Token)) { var loginRequestData = new { UserName = strUserName, UserPassword = strUserPassword, CountryCode = "ind", CurrencyCode = "inr", LanguageCode = "eng" }; string strLoginRequestURL = $"{APIURL}/{LoginAPIURL}"; string loginPostData = JsonConvert.SerializeObject(loginRequestData, Formatting.Indented); objRESTAPIListDTO = new Revalsys.Properties.RESTAPIListDTO { APIRequest = loginPostData, APIURL = strLoginRequestURL, ContentType = strContentType, HttpVerb = strMethodType, RequestTimeOut = intRequestTimeOut, BearerToken = Token }; string UserTokenResponse = General.CallJWTRestAPI(objRESTAPIListDTO, lstSiteListDTO[0].SiteCode, lstSiteListDTO[0].SiteId, lstSiteListDTO); if (!string.IsNullOrEmpty(UserTokenResponse)) { var jsonObject = JObject.Parse(UserTokenResponse); strToken = jsonObject["Data"]?[0]?["JwtToken"]?.ToString(); } } // ✅ TODO: Insert or Update Token and Expiry date in tblDefaultUserToken if (!string.IsNullOrWhiteSpace(strToken)) { objDefaultUserTokenListDTO = new DefaultUserTokenListDTO() { Id = strUserTokenGuid, UserToken = strToken, IsPublished = true, PublishedBy = "FileUploadProcessorService", DatePublished = dtCurrentDate, DisplayOnWeb = true, IPAddress = General.GetIP4Address(), CreatedBy = "FileUploadProcessorService", DateCreated = dtCurrentDate, SiteId = lstSiteListDTO[0].SiteId, //CreatedByUserId = intUserId, }; FileUploadDAL objFileUploadDAL = new FileUploadDAL(strConnectionString, EncryptionKey); DataSet dsResponse = objFileUploadDAL.SaveDefaultUserTokenDB(objDefaultUserTokenListDTO); } } } catch (Exception Ex) { General.CreateErrorLog(Ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } #endregion JwtToken try { if (!File.Exists(strFile)) continue; if (!await CanReadFile(strFile, SiteLogTypeId, strConnectionString, EncryptionKey, lstSiteListDTO)) continue; string strFNamewithExt = System.IO.Path.GetFileName(strFile); // fudTransactions.PostedFile.FileName; string strExt = Path.GetExtension(strFNamewithExt); string strFileName = Path.GetFileName(strFNamewithExt); string[] splitFName = strFileName.Split('.'); string strNewFileName = string.Empty; if (splitFName.Length > 0 && splitFName[0].Length > 0) { strFileName = splitFName[0]; } string strGUID = string.Empty; strGUID = Guid.NewGuid().ToString(); string strDestPath = Path.Combine(UploadSuccessPath, strGUID); StringBuilder objFilepath = new StringBuilder(); objFilepath.Append(strFileName); objFilepath.Append("_"); objFilepath.Append(dtCurrentDate.ToString("dd_MMM_yyyy_hh_mm")); objFilepath.Append(strExt); strNewFileName = objFilepath.ToString(); string strfilepath = Path.Combine(strDestPath, strNewFileName); int intUploadFileTypeId = await GetUploadFileTypeId(strExt); if (!Directory.Exists(strDestPath)) { Directory.CreateDirectory(strDestPath); } string sourceFile = Path.Combine(DownloadFilesPath, strFNamewithExt); string destFile = Path.Combine(strDestPath, strNewFileName); await Task.Run(() => File.Copy(sourceFile, destFile)); if (IsDecryptFile) { await DecryptFileIfNeededAsync(destFile, DecryptFilePath); strfilepath = DecryptFilePath; } int FileuploadId = await InsertFileUpload(strfilepath, strFileName, strFNamewithExt, intUploadTemplateId, strGUID, intUploadFileTypeId, strConnectionString, EncryptionKey, SiteLogTypeId, IntErrorLog, lstSiteListDTO, strToken, ModuleId); if (FileuploadId > 0 && File.Exists(sourceFile)) { File.Delete(sourceFile); } } catch (Exception ex) { // HandleException(ex, SiteCode, intSiteId, SiteLogTypeId, ref IntErrorLog); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } } catch (Exception ex) { // HandleException(ex, SiteCode, intSiteId, SiteLogTypeId, ref IntErrorLog); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } #region DecryptFileIfNeeded //------------------------------------------------------------------------------------------------------------------- //Method Name : DecryptFileIfNeeded //Method Description : This method is used to Decrypt File If Needed. //Author : Mahesh Gupta //Creation Date : 15 Feb 2024 //Modified Date : //-------------------------------------------------------------------------------------------------------------------- // --------------------------------------------------------------------------------------------------------------------------- // Version Author Date Remarks // --------------------------------------------------------------------------------------------------------------------------- // 1.0.0 Mahesh Gupta 15 Feb 2024 Creation //******************************************************************************************************************************** private async Task DecryptFileIfNeededAsync(string sourceFile, string DecryptFilePath) { try { AESDecryption objAESDecryption = new AESDecryption(); await Task.Run(() => objAESDecryption.DecryptFile(sourceFile, DecryptFilePath)); if (File.Exists(sourceFile)) { File.Delete(sourceFile); } } catch (Exception ex) { throw new Exception($"Error decrypting file: {ex.Message}"); } } #endregion #region Checking File Extension //------------------------------------------------------------------------------------------------------------------- //Method Name : GetUploadFileTypeId //Method Description : This method is used to Check File Extension. //Author : Mahesh Gupta //Creation Date : 15 Feb 2024 //Modified Date : //-------------------------------------------------------------------------------------------------------------------- // --------------------------------------------------------------------------------------------------------------------------- // Version Author Date Remarks // --------------------------------------------------------------------------------------------------------------------------- // 1.0.0 Mahesh Gupta 15 Feb 2024 Creation //******************************************************************************************************************************** private Task GetUploadFileTypeId(string fileExtension) { string lowerCaseExtension = fileExtension.ToLower().Replace(".", ""); if (lowerCaseExtension == "xls") { return Task.FromResult(Convert.ToInt32(General.UploadFileType.xls)); } else if (lowerCaseExtension == "xlsx") { return Task.FromResult(Convert.ToInt32(General.UploadFileType.xlsx)); } else if (lowerCaseExtension == "csv") { return Task.FromResult(Convert.ToInt32(General.UploadFileType.CSV)); } else if (lowerCaseExtension == "txt") { return Task.FromResult(Convert.ToInt32(General.UploadFileType.FixedLength)); } else { // Default to FixedLength if the extension is not recognized return Task.FromResult(Convert.ToInt32(General.UploadFileType.FixedLength)); } } #endregion private async Task InsertFileUpload(string strfilepath, string strFileName, string strFNamewithExt, int intUploadTemplateId, string strGUID, int intUploadFileTypeId, string strConnectionString, string EncryptionKey, int SiteLogTypeId, int IntErrorLog, List lstSiteListDTO, string strToken, int intModuleId) { FileUploadServiceListDTO objFileUploadListDTO = null; FileUploadBAL objFileUploadBAL = null; int FileUplaodId = 0; string strMethodName = MethodBase.GetCurrentMethod()?.Name ?? string.Empty; DateTime dtCurrentDate = RevalTimeZone.GetDateByTimezone(DateTime.Now, TimeZone, NoOfHours, NoOfMinutes); try { General.CreateCodeLog("Step 6.1", "Start of InsertFileUpload", "", strMethodName, lstSiteListDTO, null, strServiceName); objFileUploadBAL = new FileUploadBAL(strConnectionString, EncryptionKey, _ConfigurationSettingsListDTO); //objFileUploadBAL = new FileUploadBAL(_configurationSettingsListDto); objFileUploadListDTO = new FileUploadServiceListDTO(); objFileUploadListDTO.CompanyId = _ConfigurationSettingsListDTO.CompanyId; objFileUploadListDTO.SiteId = lstSiteListDTO[0].SiteId; objFileUploadListDTO.SiteCode = lstSiteListDTO[0].SiteCode; objFileUploadListDTO.DepartmentId = _ConfigurationSettingsListDTO.DepartmentId; objFileUploadListDTO.FilePath = strfilepath; objFileUploadListDTO.FileUploadName = strFileName; objFileUploadListDTO.FileNameWithExtension = strFNamewithExt; objFileUploadListDTO.UploadTemplateId = intUploadTemplateId; objFileUploadListDTO.CreatedBy = "FileUploadProcessorService"; objFileUploadListDTO.IPAddress = _ConfigurationSettingsListDTO.IPAddress; objFileUploadListDTO.FileUploadTypeId = intUploadTemplateId; objFileUploadListDTO.FileUniqueID = strGUID; objFileUploadListDTO.IsScheduleRequired = true; objFileUploadListDTO.IsScheduled = true; objFileUploadListDTO.IsProcessed = false; objFileUploadListDTO.DateScheduled = dtCurrentDate; objFileUploadListDTO.DatePublished = dtCurrentDate; objFileUploadListDTO.UploadFileTypeId = intUploadFileTypeId; objFileUploadListDTO.OriginalFileName = strFNamewithExt; objFileUploadListDTO.IsPublished = true; objFileUploadListDTO.DisplayOnWeb = true; objFileUploadListDTO.Token = strToken; objFileUploadListDTO.ModuleId = intModuleId; objFileUploadListDTO.FileUploadStatusId = Convert.ToInt32(General.FileUploadStatus.New); objFileUploadBAL = new FileUploadBAL(strConnectionString, EncryptionKey, _ConfigurationSettingsListDTO); // FileUplaodId = Convert.ToInt32(await objFileUploadBAL.InsertFileUpload(objFileUploadListDTO)); var (primaryResult, mainResult) = await objFileUploadBAL.InsertFileUpload(objFileUploadListDTO); // Convert the primary result if needed // Ensure primaryResult is not null and can be converted to int if (primaryResult != null && int.TryParse(primaryResult.ToString(), out int tempId)) { FileUplaodId = tempId; } General.CreateCodeLog("Step 6.2", "End of InsertFileUpload", "", strMethodName, lstSiteListDTO, null, strServiceName); } catch (Exception ex) { General.CreateCodeLog("Step 6.1.1", "Exception in InsertFileUpload Metod" + ex.Message, "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 return 0; } finally { objFileUploadListDTO = null; objFileUploadBAL = null; } return FileUplaodId; } #endregion private async Task CanReadFile(string filePath, int SiteLogTypeId, string strConnectionString, string EncryptionKey, List lstSiteListDTO) { string strMethodName = MethodBase.GetCurrentMethod()?.Name ?? string.Empty; try { // The "using" is important because FileStream implements IDisposable and // "using" will avoid a heap exhaustion situation when too many handles // are left undisposed. using (FileStream fileStream = File.Open(filePath, FileMode.Open, FileAccess.ReadWrite, FileShare.None)) { if (fileStream != null) fileStream.Close(); } } catch (IOException ex) { General.CreateCodeLog("Step - I", "Can read file method" + ex.Message.ToString(), "", strMethodName, lstSiteListDTO, null, strServiceName); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 return false; } return true; } #region DeleteErrorDetailsByFileuploadTypeIdAsync //------------------------------------------------------------------------------------------------------------------- // Method Name : DeleteErrorDetailsByFileuploadTypeId // Method Description : This method will Delete the data from Success table for respected fileuploadtype. // Author Name : Mahesh M // Create Date : 05 July 2016 // Modified Date : // Modified Name : // Modified Reason : //-------------------------------------------------------------------------------------------------------------------- /// /// ImportData: This method will Delete the data from Success table for respected fileuploadtype. /// private async Task DeleteErrorDetailsByFileuploadTypeIdAsync(Int64 FileUploadId, int FileUploadTypeId, bool IsDeleteSuccessRecords, string strConnectionString, string EncryptionKey, int LogTypeId, int IntErrorLog, List lstSiteListDTO) { FileUploadServiceListDTO objFileUploadListDTO = null; FileUploadBAL objFileUploadBAL = null; string strMethodName = MethodBase.GetCurrentMethod()?.Name ?? string.Empty; try { General.CreateCodeLog("Step - 1", "Exception in DeleteErrorDetailsByFileuploadTypeId method", "", strMethodName, lstSiteListDTO, null, strServiceName); objFileUploadListDTO = new FileUploadServiceListDTO(); objFileUploadBAL = new FileUploadBAL(strConnectionString, EncryptionKey, _ConfigurationSettingsListDTO); objFileUploadListDTO.CompanyId = _ConfigurationSettingsListDTO.CompanyId; //objFileUploadListDTO.SiteId = lstSiteListDTO[0].SiteId; objFileUploadListDTO.FileUploadId = FileUploadId; objFileUploadListDTO.IsDeleteSuccessRecords = IsDeleteSuccessRecords; objFileUploadListDTO.FileUploadTypeId = FileUploadTypeId; objFileUploadListDTO.IsDeleteSuccessRecords = IsDeleteSuccessRecords; await objFileUploadBAL.DeleteErrorDetailsByFileuploadTypeId(objFileUploadListDTO); // for deleting data from success table General.CreateCodeLog("Step - 2", "Exception in DeleteErrorDetailsByFileuploadTypeId method", "", strMethodName, lstSiteListDTO, null, strServiceName); } catch (Exception ex) { General.CreateCodeLog("Step - 3", "Exception DeleteErrorDetailsByFileuploadTypeId method", "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } finally { objFileUploadListDTO = null; objFileUploadBAL = null; } } #endregion //#region ServiceStartStop ////*************************************************************************************************************************** //// Purpose : Running the Second Service then stop. //// Method Name : ServiceStartStop //// Date : 08 Aug 2024 //// Return Values : ////======================================================================================= ////Version Author Date Remarks ////---------------------------------------------------------------------------------------- ////1.0 Mahesh Gupta 08 Aug 2024 Creation ////======================================================================================= //private void ServiceStartStop(int LogTypeId, string SiteCode, string ConnectionString, string EncryptionKey, int intSiteId) //{ // ServiceController service = null; // try // { // service = new ServiceController(_configurationSettingsListDto.SecondServiceFromConfig); // if (service != null) // { // if (service.Status == ServiceControllerStatus.Running) // { // service.Stop(); // } // } // } // catch (Exception ex) // { // if (LogTypeId > Convert.ToInt32(General.ErrorLog.ErrorLog_NotRequired)) // { // //CreateErrorLogProgramWise(LogTypeId, ex, "", SiteCode, ConnectionString, EncryptionKey); // CreateErrorLogProgramWiseAsync(ex, "Exception In ServiceStartStop method", SiteCode, intSiteId); // } // } // finally // { // service = null; // } //} //#endregion ServiceStartStop #region FirstServiceStartStop //*************************************************************************************************************************** // Purpose : Running the FirstService then Stop. // Method Name : FirstServiceStartStop // Date : 02 May 2024 // Return Values : //======================================================================================= //Version Author Date Remarks //---------------------------------------------------------------------------------------- //1.0 Mahesh Gupta 24 Aug 2024 Creation //======================================================================================= //private void FirstServiceStartStop(int LogTypeId, string ConnectionString, string EncryptionKey,List lstSiteListDTO) //{ // string strMethodName = MethodBase.GetCurrentMethod().Name; // System.ServiceProcess.ServiceController service = null; // try // { // if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows)) // { // service = new System.ServiceProcess.ServiceController(_serviceConfig.ServiceName); // if (service != null) // { // if (service.Status == ServiceControllerStatus.Running) // { // service.Stop(); // } // } // } // else if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux)) // { // // CreateCodeLogProgramWise("Step 1.3", "Start of GetSuccessFileUploads method", strMethodName, objSiteDTO.SiteCode, objSiteDTO.SiteId); // // ManageLinuxService(_ConfigurationSettingsListDTO.ServiceName, "stop", LogTypeId, ConnectionString, EncryptionKey,lstSiteListDTO).Wait(); // // CreateCodeLogProgramWise("Step - I", "Getting Exception in FirstServiceStartStop" + ex.Message.ToString(), strMethodName, SiteCode, intSiteId); // } // } // catch (Exception ex) // { // General.CreateCodeLog("Step - I", "Getting Exception in FirstServiceStartStop" + ex.Message.ToString(),"", strMethodName, lstSiteListDTO, null,strServiceName); // if (LogTypeId > Convert.ToInt32(General.ErrorLog.ErrorLog_NotRequired)) // { // General.CreateErrorLog(ex, MethodBase.GetCurrentMethod().Name, lstSiteListDTO, null,strServiceName); // } // } // finally // { // service = null; // } //} #endregion FirstServiceStartStop public string CallRestAPIForService(string Token, string apiUrl) { string strResponse = string.Empty; Guid newGuid = Guid.NewGuid(); string guidString = newGuid.ToString(); try { ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12; var webRequest = (HttpWebRequest)WebRequest.Create(apiUrl); webRequest.Method = "POST"; webRequest.Timeout = 15000; // 15 seconds webRequest.ContentType = "application/json"; webRequest.UserAgent = "PostmanRuntime/7.40.0"; webRequest.Headers.Add("Cache-Control", "no-cache"); webRequest.Headers.Add("Postman-Token", guidString); webRequest.Headers.Add("Accept-Encoding", "gzip, deflate"); // Accept compressed responses webRequest.Headers.Add("Connection", "keep-alive"); if (!string.IsNullOrEmpty(Token)) { webRequest.Headers.Add(HttpRequestHeader.Authorization, "Bearer " + Token); } // Send an empty body (like in curl --data '') using (var requestStream = webRequest.GetRequestStream()) { byte[] emptyData = Encoding.UTF8.GetBytes(""); // Empty body requestStream.Write(emptyData, 0, emptyData.Length); } using (var resp = (HttpWebResponse)webRequest.GetResponse()) { var responseStream = resp.GetResponseStream(); // Handle compression if (resp.ContentEncoding.ToLower().Contains("gzip")) { using (var gzipStream = new GZipStream(responseStream, CompressionMode.Decompress)) using (var reader = new StreamReader(gzipStream, Encoding.UTF8)) { strResponse = reader.ReadToEnd(); } } else if (resp.ContentEncoding.ToLower().Contains("deflate")) { using (var deflateStream = new DeflateStream(responseStream, CompressionMode.Decompress)) using (var reader = new StreamReader(deflateStream, Encoding.UTF8)) { strResponse = reader.ReadToEnd(); } } else { using (var reader = new StreamReader(responseStream, Encoding.UTF8)) { strResponse = reader.ReadToEnd(); } } } } catch (WebException ex) { if (ex.Response != null) { using (var errorResponse = (HttpWebResponse)ex.Response) using (var resStream = errorResponse.GetResponseStream()) using (var reader = new StreamReader(resStream, Encoding.UTF8)) { strResponse = $"Error: {reader.ReadToEnd()}"; } } else { strResponse = $"Error: {ex.Message}"; } } catch (Exception ex) { strResponse = $"Exception: {ex.Message}"; } return strResponse; } #region SecondServiceStart //*************************************************************************************************************************** // Purpose : Stop the SecondService then Start. // Method Name : SecondServiceStart // Date : 02 May 2024 // Return Values : strSecondServiceName //======================================================================================= //Version Author Date Remarks //---------------------------------------------------------------------------------------- //1.0 Mahesh Gupta 24 Aug 2024 Creation //======================================================================================= //private void SecondServiceStart(string strSecondServiceName, int LogTypeId, string ConnectionString, string EncryptionKey,List lstSiteListDTO) //{ // string strMethodName = MethodBase.GetCurrentMethod().Name; // System.ServiceProcess.ServiceController service = null; // try // { // if (RuntimeInformation.IsOSPlatform(OSPlatform.Windows)) // { // service = new System.ServiceProcess.ServiceController(strSecondServiceName); // if (service != null) // { // if (service.Status == ServiceControllerStatus.Stopped) // { // service.Start(); // } // } // } // else if (RuntimeInformation.IsOSPlatform(OSPlatform.Linux)) // { // // CreateCodeLogProgramWise("Step 1.3", "Start of GetSuccessFileUploads method", strMethodName, objSiteDTO.SiteCode, objSiteDTO.SiteId); // // ManageLinuxService(strSecondServiceName, "start", LogTypeId, ConnectionString, EncryptionKey, lstSiteListDTO).Wait(); // // CreateCodeLogProgramWise("Step - I", "Getting Exception in FirstServiceStartStop" + ex.Message.ToString(), strMethodName, SiteCode, intSiteId); // } // } // catch (Exception ex) // { // General.CreateCodeLog("Step - I", "Getting Exception In SecondServiceStart " + ex.Message.ToString(), "", strMethodName, lstSiteListDTO, null, strServiceName); // General.CreateErrorLog(ex, "Exception in SecondServiceStart Method", lstSiteListDTO, null, strServiceName); // } // finally // { // service = null; // } //} #endregion SecondServiceStart #region Read File //----------------------------------------------------------------------------------------------------------------------- //Method Name : ReadFile //Method Description : This method will download the file from local path // Author Name : Akhila R // Create Date : 10 April 2025 //------------------------------------------------------------------------------------------------------------------------ //-- Version Author Date Remarks //-- --------------------------------------------------------------------------------------------------------------------- //-- 1.0 Akhila R 10 April 2025 Creation //-- ===================================================================================================================== public async Task ReadFile(string strFilepath, string strFileName, string strSiteCode, List lstUploadStorageListDTO) { bool IsAzureBlobStorage = false; string AzureBlobUrl = string.Empty, ServerPath = string.Empty, BlobImageContainer = string.Empty, StorageAccount = string.Empty; string strLocalStoragePath = string.Empty, SubDominePath = string.Empty, SubDomineUrl = string.Empty, strNewFilePath = string.Empty; try { #region ModuleStorage Details if (lstUploadStorageListDTO != null && lstUploadStorageListDTO.Count > 0) { foreach (UploadStorageListDTO SiteUploadStorage in lstUploadStorageListDTO) { if (SiteUploadStorage.UploadTypeId == Convert.ToInt32(General.UploadTypeId.LocalStorage)) { IsAzureBlobStorage = false; SubDominePath = SiteUploadStorage.SubDominePath; SubDomineUrl = SiteUploadStorage.SubDomineUrl; strLocalStoragePath = SiteUploadStorage.LocalStoragePath.Replace("[#sitecode#]", strSiteCode); } else if (SiteUploadStorage.UploadTypeId == Convert.ToInt32(General.UploadTypeId.Blob)) { AzureBlobUrl = SiteUploadStorage.UploadStorageUrl; BlobImageContainer = SiteUploadStorage.ServerName; StorageAccount = SiteUploadStorage.UploadStorageAccount; IsAzureBlobStorage = true; ServerPath = SiteUploadStorage.ServerPath.Replace("[#sitecode#]", strSiteCode); SubDominePath = SiteUploadStorage.SubDominePath; strBlobLocalStoragePath = SiteUploadStorage.BlobLocalStoragePath.Replace("[#sitecode#]", strSiteCode); } } } #endregion // Blob Storage: Read file from Azure Blob Storage if (IsAzureBlobStorage) { string blobFilePath = strFilepath; string OriginalImagefullPathBlobDirectoryPath = blobFilePath.Replace(AzureBlobUrl, ""); string directoryPathWithoutFileName = Path.GetDirectoryName(OriginalImagefullPathBlobDirectoryPath); directoryPathWithoutFileName = directoryPathWithoutFileName.Replace("\\", "/"); if (directoryPathWithoutFileName.StartsWith("uploads/")) { directoryPathWithoutFileName = directoryPathWithoutFileName.Substring("uploads/".Length); } string LocalStoragePath = SubDominePath + strBlobLocalStoragePath.Replace("~/", ""); if (!string.IsNullOrEmpty(LocalStoragePath)) { BlobHelper blobHelper = new BlobHelper(); strNewFilePath = await blobHelper.DownloadFileFromBlobStorage(BlobImageContainer, directoryPathWithoutFileName, strFileName, StorageAccount, LocalStoragePath); } } return strNewFilePath; } catch (Exception ex) { throw; } } #endregion #region GetUploadStorageByModuleStorageId //----------------------------------------------------------------------------------------------------------------------- //Method Name : GetUploadStorageByModuleStorageId //Method Description : This method will get data from Site Uploadstoreage // Author Name : Akhila R // Create Date : 28 Dec 2023 //------------------------------------------------------------------------------------------------------------------------ //-- Version Author Date Remarks //-- --------------------------------------------------------------------------------------------------------------------- //-- 1.0 Akhila R 28 Dec 2023 Creation //-- 1.1 Jafar 26 Dec 2024 Added Return Type for the Method //-- ===================================================================================================================== /// /// This method will get data from Uploadstoreage /// /// /// /// /// /// public List GetUploadStorageByModuleStorageId(int intCountryId, int intCurrencyId, int intLanguageId, int intModuleStorageId = 0, string SiteCode = "", ConfigurationSettingsListDTO _configurationSettingsListDto = null) { bool IsStoreFileInDatabase = false; List lstUploadStorageListDTO = new List(); try { AzureBlobUrl = string.Empty; StorageAccount = string.Empty; IsAzureBlobStorage = false; // RootFolderImagePath = string.Empty; UploadStorageDAL objUploadStorageDAL = new UploadStorageDAL(_configurationSettingsListDto, string.Empty); UploadStorageListDTO objUploadStorageListDTO = new UploadStorageListDTO { IsPublished = true, CountryId = intCountryId, CurrencyId = intCurrencyId, LanguageId = intLanguageId, ModuleStorageId = intModuleStorageId, }; UploadStorageBAL objUploadStorageBAL = new UploadStorageBAL(_configurationSettingsListDto); lstUploadStorageListDTO = objUploadStorageBAL.GetUploadStorageByModuleStorageId(intCountryId, intCurrencyId, intLanguageId, intModuleStorageId, 0); if (lstUploadStorageListDTO?.Count > 0) { foreach (UploadStorageListDTO SiteUploadStorage in lstUploadStorageListDTO) { if (SiteUploadStorage.UploadTypeId == Convert.ToInt32(General.UploadTypeId.LocalStorage)) { IsAzureBlobStorage = false; SubDominePath = SiteUploadStorage.SubDominePath; SubDomineUrl = SiteUploadStorage.SubDomineUrl; strLocalStoragePath = SiteUploadStorage.LocalStoragePath.Replace("[#sitecode#]", SiteCode); } else if (SiteUploadStorage.UploadTypeId == Convert.ToInt32(General.UploadTypeId.Blob)) { AzureBlobUrl = SiteUploadStorage.UploadStorageUrl; BlobImageContainer = SiteUploadStorage.ServerName; StorageAccount = SiteUploadStorage.UploadStorageAccount; IsAzureBlobStorage = true; ServerPath = SiteUploadStorage.ServerPath.Replace("[#sitecode#]", SiteCode); SubDominePath = SiteUploadStorage.SubDominePath; strBlobLocalStoragePath = SiteUploadStorage.BlobLocalStoragePath.Replace("[#sitecode#]", SiteCode); // Added by Srinivas M on 19 Feb 2024 } else if (SiteUploadStorage.UploadTypeId == Convert.ToInt32(General.UploadTypeId.Database)) { IsStoreFileInDatabase = true; } } } } catch (Exception ex) { // General.CreateErrorLog(ex, MethodBase.GetCurrentMethod().Name); } return lstUploadStorageListDTO; } #endregion // Helper method to detect delimiter and quoted status private (char delimiter, bool isQuoted) DetectDelimiter(List sampleLines) { foreach (var line in sampleLines.Take(5)) { if (line.Contains("|")) return ('|', false); if (line.Contains(",") && line.Contains("\"")) return (',', true); if (line.Contains(",")) return (',', false); } return (',', false); // default } // Helper method to safely split CSV line private List SplitCsvLine(string line, char delimiter, bool isQuoted) { if (isQuoted) { return line.Split(new[] { "\"" + delimiter + "\"" }, StringSplitOptions.None) .Select(f => f.Replace("\"", "").Trim()).ToList(); } return line.Split(delimiter).Select(f => f.Trim()).ToList(); } public DataTable ParseCsvToDataTable(string rawText, Dictionary matchingColumnIndexes, string strMethodName, List lstSiteListDTO) { General.CreateCodeLog("Step - 40", "Start parsing CSV to DataTable", "", strMethodName, lstSiteListDTO); DataTable dtReturnTable = new DataTable(); try { // ✅ Add columns to DataTable, ensuring no duplicate column names HashSet addedColumnNames = new HashSet(StringComparer.OrdinalIgnoreCase); foreach (var kvp in matchingColumnIndexes) { if (!addedColumnNames.Contains(kvp.Value)) { dtReturnTable.Columns.Add(kvp.Value); addedColumnNames.Add(kvp.Value); } else { General.CreateCodeLog("Step - 40.0", $"Duplicate column '{kvp.Value}' skipped from DataTable column creation.", "", strMethodName, lstSiteListDTO); } } // ✅ Split rawText into lines var allLines = rawText.Split(new[] { "\r\n", "\n" }, StringSplitOptions.None).ToList(); if (allLines.Count < 2) { General.CreateCodeLog("Step - 40.E1", "CSV file has less than 2 lines", "", strMethodName, lstSiteListDTO); return dtReturnTable; } // ✅ Read actual rows from second line onwards for (int i = 1; i < allLines.Count; i++) // Skip header { try { var line = allLines[i]; if (string.IsNullOrWhiteSpace(line)) continue; string[] fields = line.Split(','); DataRow row = dtReturnTable.NewRow(); foreach (var kvp in matchingColumnIndexes) { try { if (!dtReturnTable.Columns.Contains(kvp.Value)) continue; if (kvp.Key >= fields.Length) continue; string cellValue = fields[kvp.Key]?.Trim() .Replace("\"", "") // Remove double quotes .Replace("'", ""); // Remove single quotes row[kvp.Value] = string.IsNullOrEmpty(cellValue) ? DBNull.Value : (object)cellValue; } catch (Exception ex) { General.CreateCodeLog("Step - 40.1", "Exception reading field: " + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } dtReturnTable.Rows.Add(row); } catch (Exception ex) { General.CreateCodeLog("Step - 40.2", "Exception adding row: " + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } } General.CreateCodeLog("Step - 41", "CSV parsing to DataTable completed successfully", "", strMethodName, lstSiteListDTO); } catch (Exception ex) { General.CreateCodeLog("Step - 40.E", "Fatal error during CSV parsing: " + ex.Message, "", strMethodName, lstSiteListDTO); General.CreateErrorLog(ex, strMethodName, lstSiteListDTO, null, null); // Added by Subbarao B on Feb 05 2026 } return dtReturnTable; } } }