[{"_1":2,"_176":-5,"_177":-5},"loaderData",{"_3":4,"_92":93},"root",{"_5":6,"_35":36,"_65":66},"ctx",{"_7":8,"_23":-5,"_24":25,"_26":27,"_28":20,"_29":20,"_30":20,"_31":32,"_33":20,"_34":20},"auth",{"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20,"_21":22},"sessionClaims","sessionId","sessionStatus","userId","actor","orgId","orgRole","orgSlug","orgPermissions","factorVerificationAge","isAuthenticated",false,"tokenType","session_token","user","preferencesKeybindings","default","preferencesTheme","light","preferencesFixers","preferencesCopilot","preferencesSelectionAi","notificationCount",0,"flagDenols","isTeamOrgMember","ENV",{"_37":38,"_39":40,"_41":42,"_43":44,"_45":46,"_47":48,"_49":50,"_51":52,"_53":54,"_55":56,"_57":58,"_59":60,"_61":62,"_63":64},"REACT_APP_SENTRY_DSN","https://586020d86f05489db63a554690acf1e9@o1398359.ingest.sentry.io/6724499","VAL_RUNTIME_NAME","prod","RENDER_GIT_COMMIT","d5f757b780a022bc6624fd27fe562bc7d587159d","RENDER_GIT_BRANCH","main","CLERK_PUBLISHABLE_KEY","pk_live_Y2xlcmsudmFsLnRvd24k","POSTHOG_API_TOKEN","phc_nmoyuAkStEZuUp69rYlg1DZgrIt19Gx0Y4Hwf8tGgJP","POSTHOG_API_HOST","https://wart.val.town","WEB_API_TEMPLATE","https://{val}.web.val.run","APP_URL","https://www.val.town","MODULE_URL","https://esm.town","VANITY_WEB_API_TEMPLATE","https://{label}.val.run/","DENOLS_URL","https://lsp.val.town","NODE_ENV","production","API_URL","https://api.val.town","clerkState",{"_67":68},"__internal_clerk_state",{"_69":70,"_71":46,"_72":73,"_74":73,"_75":20,"_76":73,"_77":73,"_78":73,"_79":73,"_80":81,"_90":20,"_91":20},"__clerk_ssr_state",{"_21":22,"_9":-5,"_10":-5,"_11":-5,"_12":-5,"_13":-5,"_14":-5,"_15":-5,"_16":-5,"_17":-5,"_18":-5,"_19":20},"__publishableKey","__proxyUrl","","__domain","__isSatellite","__signInUrl","__signUpUrl","__afterSignInUrl","__afterSignUpUrl","__clerk_debug",{"_82":20,"_19":20,"_83":73,"_84":85,"_86":73,"_87":46,"_88":20,"_89":73},"isSignedIn","proxyUrl","reason","session-token-and-uat-missing","message","publishableKey","isSatellite","domain","__telemetryDisabled","__telemetryDebug","routes/embed.x.$handle.$name.$",{"_94":95,"_96":97,"_98":99,"_100":101,"_151":152,"_173":174,"_175":-5},"handle","MRPRRSIDENT","name","CDMBenchmarking","path","backend/services/file-processor.ts","project",{"_102":103,"_96":97,"_104":105,"_106":-5,"_107":20,"_108":109,"_110":111,"_112":-5,"_12":113,"_114":115,"_116":117,"_118":117,"_119":-5,"_120":32,"_14":121,"_122":123,"_124":123,"_125":126,"_127":126,"_128":126,"_129":123,"_130":123,"_23":131,"_134":135,"_137":138,"_150":20},"id","181b65cc-5ba7-11f0-9a49-f69ea79377d9","createdAt",["D",1751943367054],"deletedAt","legacyEnvVars","privacy","unlisted","description","Model benchmarking tool for IMM, CDM ISDA plus mapping ","imageUrl","682f125d-0e14-4a34-baf0-cd336faaaea9","pinnedFileId","bc561f68-cfa3-11f0-8a00-42dde27851f2","pinPreview",true,"pinMigrated","screenshotVersion","likeCount","68e9dfdf-cb88-426a-b619-6e75fb5e7dfa","userPerm","r","valPerm","blobPerm","rw","emailPerm","sqlitePerm","projectPerm","telemetryPerm",{"_94":95,"_132":133},"profileImageUrl","https://img.clerk.com/eyJ0eXBlIjoiZGVmYXVsdCIsImlpZCI6Imluc18yTm10cGhQUklwU0tqZHNiOHNnNGd2ZjJURFUiLCJyaWQiOiJ1c2VyXzJwcTVjemR4RFVBdnRsajFGMTFlRFp3UFZ6OSJ9","org",{"_94":95,"_136":133,"_12":113},"avatar","branch",{"_139":140,"_96":44,"_102":141,"_142":103,"_143":-5,"_144":-5,"_145":-5,"_146":-5,"_147":-5,"_148":97,"_149":95},"version",52,"181bcb52-5ba7-11f0-9a49-f69ea79377d9","projectId","forkedBranchId","forkedBranchVersion","forkedProjectPrivacy","forkedProjectId","forkedProjectOrgId","projectName","userHandle","isOwned","file",{"_102":153,"_96":154,"_155":156,"_157":158,"_159":141,"_160":161,"_162":117,"_104":163,"_139":32,"_164":165,"_166":167,"_168":20,"_169":-5,"_98":170},"34f39a3c-cfa3-11f0-b294-42dde27851f2","file-processor.ts","fileId","34f39b0e-cfa3-11f0-b294-42dde27851f2","parentId","41e85e3b-ef05-43d6-9ffe-f13d0a806c3f","branchId","branchVersion",7,"latest",["D",1764696032330],"fileType","script","content","// File processing utilities for Excel, SQL, and Oracle data formats\n\nimport type { FieldMapping, MappingResult, CDMTrade } from '../../shared/types.ts';\nimport { ExternalDataService } from './external-data.ts';\n\nexport class FileProcessor {\n private externalDataService: ExternalDataService;\n\n constructor() {\n this.externalDataService = ExternalDataService.getInstance();\n }\n\n async processFile(file: File, fileType: string): Promise {\n try {\n let rawData: Record[];\n \n switch (fileType.toLowerCase()) {\n case 'excel':\n rawData = await this.processExcelFile(file);\n break;\n case 'sql':\n rawData = await this.processSQLFile(file);\n break;\n case 'oracle':\n rawData = await this.processOracleFile(file);\n break;\n default:\n throw new Error(`Unsupported file type: ${fileType}`);\n }\n\n return await this.mapToCDM(rawData);\n } catch (error) {\n throw new Error(`File processing failed: ${error.message}`);\n }\n }\n\n private async processExcelFile(file: File): Promise[]> {\n // For demonstration, we'll parse CSV-like content\n // In a real implementation, you'd use a library like xlsx\n const text = await file.text();\n const lines = text.split('\\n').filter(line => line.trim());\n \n if (lines.length < 2) {\n throw new Error('Excel file must contain at least a header row and one data row');\n }\n\n const headers = lines[0].split(',').map(h => h.trim().replace(/\"/g, ''));\n const data: Record[] = [];\n\n for (let i = 1; i < lines.length; i++) {\n const values = lines[i].split(',').map(v => v.trim().replace(/\"/g, ''));\n const row: Record = {};\n \n headers.forEach((header, index) => {\n row[header] = values[index] || '';\n });\n \n data.push(row);\n }\n\n return data;\n }\n\n private async processSQLFile(file: File): Promise[]> {\n // Parse SQL INSERT statements or result sets\n const text = await file.text();\n \n // Look for INSERT statements\n const insertRegex = /INSERT\\s+INTO\\s+\\w+\\s*\\(([^)]+)\\)\\s+VALUES\\s*\\(([^)]+)\\)/gi;\n const matches = [...text.matchAll(insertRegex)];\n \n if (matches.length === 0) {\n // Try to parse as tab-separated values (SQL query result)\n return this.parseTabSeparatedValues(text);\n }\n\n const data: Record[] = [];\n \n for (const match of matches) {\n const columns = match[1].split(',').map(col => col.trim().replace(/[`\"']/g, ''));\n const values = match[2].split(',').map(val => val.trim().replace(/['\"]/g, ''));\n \n const row: Record = {};\n columns.forEach((col, index) => {\n row[col] = values[index] || '';\n });\n \n data.push(row);\n }\n\n return data;\n }\n\n private async processOracleFile(file: File): Promise[]> {\n // Process Oracle export format (similar to SQL but with Oracle-specific syntax)\n const text = await file.text();\n \n // Look for Oracle INSERT statements\n const insertRegex = /INSERT\\s+INTO\\s+\"?\\w+\"?\\s*\\(([^)]+)\\)\\s+VALUES\\s*\\(([^)]+)\\)/gi;\n const matches = [...text.matchAll(insertRegex)];\n \n if (matches.length === 0) {\n // Try to parse as pipe-separated values (Oracle export format)\n return this.parsePipeSeparatedValues(text);\n }\n\n const data: Record[] = [];\n \n for (const match of matches) {\n const columns = match[1].split(',').map(col => col.trim().replace(/[`\"']/g, ''));\n const values = match[2].split(',').map(val => val.trim().replace(/['\"]/g, ''));\n \n const row: Record = {};\n columns.forEach((col, index) => {\n row[col] = values[index] || '';\n });\n \n data.push(row);\n }\n\n return data;\n }\n\n private parseTabSeparatedValues(text: string): Record[] {\n const lines = text.split('\\n').filter(line => line.trim());\n if (lines.length < 2) return [];\n\n const headers = lines[0].split('\\t').map(h => h.trim());\n const data: Record[] = [];\n\n for (let i = 1; i < lines.length; i++) {\n const values = lines[i].split('\\t').map(v => v.trim());\n const row: Record = {};\n \n headers.forEach((header, index) => {\n row[header] = values[index] || '';\n });\n \n data.push(row);\n }\n\n return data;\n }\n\n private parsePipeSeparatedValues(text: string): Record[] {\n const lines = text.split('\\n').filter(line => line.trim());\n if (lines.length < 2) return [];\n\n const headers = lines[0].split('|').map(h => h.trim());\n const data: Record[] = [];\n\n for (let i = 1; i < lines.length; i++) {\n const values = lines[i].split('|').map(v => v.trim());\n const row: Record = {};\n \n headers.forEach((header, index) => {\n row[header] = values[index] || '';\n });\n \n data.push(row);\n }\n\n return data;\n }\n\n private async mapToCDM(rawData: Record[]): Promise {\n if (rawData.length === 0) {\n throw new Error('No data found in file');\n }\n\n const sourceFields = Object.keys(rawData[0]);\n const mappings: FieldMapping[] = [];\n const unmappedFields: string[] = [];\n const validationErrors: any[] = [];\n const warnings: any[] = [];\n\n // Define CDM field mappings with confidence scores\n const cdmFieldMappings = this.getCDMFieldMappings();\n\n for (const sourceField of sourceFields) {\n const mapping = this.findBestMapping(sourceField, cdmFieldMappings);\n \n if (mapping) {\n // Validate the field value using external data sources\n const sampleValue = rawData[0][sourceField];\n const externalValidation = await this.validateFieldValue(mapping.targetField, sampleValue);\n \n mappings.push({\n sourceField,\n targetField: mapping.targetField,\n confidence: mapping.confidence,\n validationStatus: externalValidation.isValid ? 'valid' : 'warning',\n suggestions: mapping.suggestions,\n externalValidation,\n });\n } else {\n unmappedFields.push(sourceField);\n }\n }\n\n // Generate partial CDM data from the first row\n const cdmData = this.generateCDMData(rawData[0], mappings);\n\n return {\n mappings,\n unmappedFields,\n cdmData,\n validationErrors,\n warnings,\n };\n }\n\n private getCDMFieldMappings(): Array<{targetField: string, patterns: string[], confidence: number, suggestions?: string[]}> {\n return [\n // Trade Header mappings\n { targetField: 'tradeHeader.tradeDate', patterns: ['trade_date', 'tradedate', 'date', 'execution_date'], confidence: 0.9 },\n { targetField: 'tradeHeader.partyTradeIdentifier[0].tradeId', patterns: ['trade_id', 'tradeid', 'deal_id', 'transaction_id'], confidence: 0.95 },\n \n // Product mappings\n { targetField: 'product.productType', patterns: ['product_type', 'producttype', 'instrument_type', 'asset_class'], confidence: 0.9 },\n \n // Party mappings\n { targetField: 'party[0].partyId[0].value', patterns: ['counterparty_id', 'party_id', 'client_id', 'entity_id'], confidence: 0.85 },\n { targetField: 'party[0].partyName', patterns: ['counterparty_name', 'party_name', 'client_name', 'entity_name'], confidence: 0.9 },\n \n // Interest Rate Derivative mappings\n { targetField: 'product.interestRateDerivative.swapStream[0].calculationPeriodAmount.calculation.notionalSchedule.notionalStepSchedule.initialValue', \n patterns: ['notional', 'notional_amount', 'principal', 'face_value'], confidence: 0.9 },\n { targetField: 'product.interestRateDerivative.swapStream[0].calculationPeriodAmount.calculation.notionalSchedule.notionalStepSchedule.currency', \n patterns: ['currency', 'ccy', 'notional_currency'], confidence: 0.95 },\n { targetField: 'product.interestRateDerivative.swapStream[0].calculationPeriodAmount.calculation.fixedRateSchedule.initialValue', \n patterns: ['fixed_rate', 'rate', 'coupon_rate'], confidence: 0.85 },\n { targetField: 'product.interestRateDerivative.swapStream[0].calculationPeriodAmount.calculation.floatingRateCalculation.floatingRateIndex', \n patterns: ['floating_rate_index', 'index', 'benchmark', 'reference_rate'], confidence: 0.9 },\n { targetField: 'product.interestRateDerivative.swapStream[0].calculationPeriodAmount.calculation.dayCountFraction', \n patterns: ['day_count', 'daycount', 'day_count_fraction', 'accrual_basis'], confidence: 0.9 },\n \n // Dates\n { targetField: 'product.interestRateDerivative.swapStream[0].calculationPeriodDates.effectiveDate.unadjustedDate', \n patterns: ['effective_date', 'start_date', 'value_date'], confidence: 0.9 },\n { targetField: 'product.interestRateDerivative.swapStream[0].calculationPeriodDates.terminationDate.unadjustedDate', \n patterns: ['maturity_date', 'termination_date', 'end_date', 'expiry_date'], confidence: 0.9 },\n \n // Business day conventions\n { targetField: 'product.interestRateDerivative.swapStream[0].calculationPeriodDates.calculationPeriodDatesAdjustments.businessDayConvention', \n patterns: ['business_day_convention', 'bdc', 'adjustment_convention'], confidence: 0.8 },\n { targetField: 'product.interestRateDerivative.swapStream[0].calculationPeriodDates.calculationPeriodDatesAdjustments.businessCenters.businessCenter[0]', \n patterns: ['business_center', 'calendar', 'holiday_calendar'], confidence: 0.8 },\n \n // Payment frequency\n { targetField: 'product.interestRateDerivative.swapStream[0].paymentDates.paymentFrequency.periodMultiplier', \n patterns: ['payment_frequency_multiplier', 'freq_multiplier'], confidence: 0.7 },\n { targetField: 'product.interestRateDerivative.swapStream[0].paymentDates.paymentFrequency.period', \n patterns: ['payment_frequency_period', 'payment_frequency', 'freq_period'], confidence: 0.8 },\n \n // Calculation frequency\n { targetField: 'product.interestRateDerivative.swapStream[0].calculationPeriodDates.calculationPeriodFrequency.periodMultiplier', \n patterns: ['calc_frequency_multiplier', 'calculation_frequency_multiplier'], confidence: 0.7 },\n { targetField: 'product.interestRateDerivative.swapStream[0].calculationPeriodDates.calculationPeriodFrequency.period', \n patterns: ['calc_frequency_period', 'calculation_frequency'], confidence: 0.8 },\n ];\n }\n\n private findBestMapping(sourceField: string, mappings: Array<{targetField: string, patterns: string[], confidence: number, suggestions?: string[]}>): {targetField: string, confidence: number, suggestions?: string[]} | null {\n const normalizedSource = sourceField.toLowerCase().replace(/[_\\s-]/g, '');\n \n let bestMatch: {targetField: string, confidence: number, suggestions?: string[]} | null = null;\n let highestScore = 0;\n\n for (const mapping of mappings) {\n for (const pattern of mapping.patterns) {\n const normalizedPattern = pattern.toLowerCase().replace(/[_\\s-]/g, '');\n \n let score = 0;\n if (normalizedSource === normalizedPattern) {\n score = mapping.confidence;\n } else if (normalizedSource.includes(normalizedPattern) || normalizedPattern.includes(normalizedSource)) {\n score = mapping.confidence * 0.8;\n } else {\n // Calculate similarity score\n const similarity = this.calculateSimilarity(normalizedSource, normalizedPattern);\n if (similarity > 0.7) {\n score = mapping.confidence * similarity;\n }\n }\n\n if (score > highestScore && score > 0.5) {\n highestScore = score;\n bestMatch = {\n targetField: mapping.targetField,\n confidence: score,\n suggestions: mapping.suggestions,\n };\n }\n }\n }\n\n return bestMatch;\n }\n\n private calculateSimilarity(str1: string, str2: string): number {\n const longer = str1.length > str2.length ? str1 : str2;\n const shorter = str1.length > str2.length ? str2 : str1;\n \n if (longer.length === 0) return 1.0;\n \n const editDistance = this.levenshteinDistance(longer, shorter);\n return (longer.length - editDistance) / longer.length;\n }\n\n private levenshteinDistance(str1: string, str2: string): number {\n const matrix = [];\n \n for (let i = 0; i <= str2.length; i++) {\n matrix[i] = [i];\n }\n \n for (let j = 0; j <= str1.length; j++) {\n matrix[0][j] = j;\n }\n \n for (let i = 1; i <= str2.length; i++) {\n for (let j = 1; j <= str1.length; j++) {\n if (str2.charAt(i - 1) === str1.charAt(j - 1)) {\n matrix[i][j] = matrix[i - 1][j - 1];\n } else {\n matrix[i][j] = Math.min(\n matrix[i - 1][j - 1] + 1,\n matrix[i][j - 1] + 1,\n matrix[i - 1][j] + 1\n );\n }\n }\n }\n \n return matrix[str2.length][str1.length];\n }\n\n private async validateFieldValue(targetField: string, value: string): Promise {\n if (!value || value.trim() === '') {\n return { source: 'Empty Value', isValid: false };\n }\n\n // Determine validation type based on field path\n if (targetField.includes('currency') || targetField.includes('Currency')) {\n return await this.externalDataService.validateCurrency(value);\n } else if (targetField.includes('businessCenter')) {\n return await this.externalDataService.validateBusinessCenter(value);\n } else if (targetField.includes('floatingRateIndex')) {\n return await this.externalDataService.validateFloatingRateIndex(value);\n } else if (targetField.includes('dayCountFraction')) {\n return await this.externalDataService.validateDayCountFraction(value);\n } else if (targetField.includes('businessDayConvention')) {\n return await this.externalDataService.validateBusinessDayConvention(value);\n } else if (targetField.includes('partyId')) {\n return await this.externalDataService.validatePartyIdentifier(value, 'LEI');\n }\n\n return { source: 'No Validation', isValid: true };\n }\n\n private generateCDMData(firstRow: Record, mappings: FieldMapping[]): Partial {\n const cdmData: any = {\n tradeHeader: {\n partyTradeIdentifier: [{}],\n tradeDate: new Date(),\n },\n product: {\n interestRateDerivative: {\n swapStream: [{}],\n },\n },\n party: [{ partyId: [{}] }],\n tradeLot: [],\n };\n\n for (const mapping of mappings) {\n const value = firstRow[mapping.sourceField];\n if (value !== undefined && value !== '') {\n this.setNestedValue(cdmData, mapping.targetField, this.convertValue(value, mapping.targetField));\n }\n }\n\n return cdmData;\n }\n\n private setNestedValue(obj: any, path: string, value: any): void {\n const keys = path.split('.');\n let current = obj;\n\n for (let i = 0; i < keys.length - 1; i++) {\n const key = keys[i];\n \n // Handle array notation like \"party[0]\"\n const arrayMatch = key.match(/^(\\w+)\\[(\\d+)\\]$/);\n if (arrayMatch) {\n const [, arrayKey, index] = arrayMatch;\n const idx = parseInt(index);\n \n if (!current[arrayKey]) current[arrayKey] = [];\n while (current[arrayKey].length <= idx) {\n current[arrayKey].push({});\n }\n current = current[arrayKey][idx];\n } else {\n if (!current[key]) current[key] = {};\n current = current[key];\n }\n }\n\n const lastKey = keys[keys.length - 1];\n const arrayMatch = lastKey.match(/^(\\w+)\\[(\\d+)\\]$/);\n \n if (arrayMatch) {\n const [, arrayKey, index] = arrayMatch;\n const idx = parseInt(index);\n \n if (!current[arrayKey]) current[arrayKey] = [];\n while (current[arrayKey].length <= idx) {\n current[arrayKey].push('');\n }\n current[arrayKey][idx] = value;\n } else {\n current[lastKey] = value;\n }\n }\n\n private convertValue(value: string, targetField: string): any {\n // Convert string values to appropriate types based on field\n if (targetField.includes('Date') || targetField.includes('date')) {\n return new Date(value);\n } else if (targetField.includes('initialValue') || targetField.includes('amount') || targetField.includes('Value')) {\n const num = parseFloat(value);\n return isNaN(num) ? value : num;\n } else if (targetField.includes('periodMultiplier')) {\n const num = parseInt(value);\n return isNaN(num) ? 1 : num;\n }\n \n return value;\n }\n}","deleted","label",[171,172,154],"backend","services","permalink","https://www.val.town/x/MRPRRSIDENT/CDMBenchmarking/code/backend/services/file-processor.ts","extension","actionData","errors"]