import { Injectable, Logger } from '@nestjs/common'; import { desc, eq } from 'drizzle-orm'; import * as ExcelJS from 'exceljs'; import { z } from 'zod'; import { CreateImportSchema, CreateSkuSchema, CreateLocationSchema, CreateCustomerSchema, CreateSupplierSchema, AsnLineInputSchema, OrderLineInputSchema } from '@wms/shared'; import { DbService } from '@/common/db/db.service'; import { importJobs } from '@/db/schema'; import { requireRequestContext } from '@/common/context/request-context'; import { ValidationError, NotFoundError } from '@/common/errors/domain-error'; import { resolveStorerScope, serialize } from '@/common/util'; import { SkusService } from '@/modules/master-data/skus.service'; import { WarehousesService } from '@/modules/master-data/warehouses.service'; import { PartnersService } from '@/modules/master-data/partners.service'; import { AsnService } from '@/modules/inbound/asn.service'; import { OrdersService } from '@/modules/outbound/orders.service'; type Row = Record; type ImportType = z.infer['type']; /** Column templates: header -> description. Used both for the downloadable template and for validation. */ export const IMPORT_TEMPLATES: Record = { SKU: { note: 'One row per SKU. Pack columns are optional; EACH is always created.', columns: [ { key: 'code', label: 'SKU Code', required: true, example: 'WIDGET-1' }, { key: 'description', label: 'Description', required: true, example: 'Widget size 1' }, { key: 'category', label: 'Category', example: 'Widgets' }, { key: 'brand', label: 'Brand', example: 'Acme' }, { key: 'storageCondition', label: 'Storage Condition', example: 'AMBIENT' }, { key: 'rotationRule', label: 'Rotation', example: 'FEFO' }, { key: 'lotControlled', label: 'Lot Controlled (Y/N)', example: 'Y' }, { key: 'expiryControlled', label: 'Expiry Controlled (Y/N)', example: 'Y' }, { key: 'serialControlled', label: 'Serial Controlled (Y/N)', example: 'N' }, { key: 'shelfLifeDays', label: 'Shelf Life Days', example: '365' }, { key: 'baseUom', label: 'Base UoM', example: 'EA' }, { key: 'weightKg', label: 'Weight kg (each)', example: '0.5' }, { key: 'cbm', label: 'CBM (each)', example: '0.002' }, { key: 'eanEach', label: 'Barcode Each', example: '6291000000011' }, { key: 'caseQty', label: 'Case Qty', example: '12' }, { key: 'caseBarcode', label: 'Case Barcode', example: '16291000000018' }, { key: 'palletQty', label: 'Pallet Qty', example: '480' }, { key: 'palletTi', label: 'Pallet TI', example: '10' }, { key: 'palletHi', label: 'Pallet HI', example: '4' }, { key: 'hsCode', label: 'HS Code', example: '8471.30' }, ], }, LOCATION: { note: 'Zone code must exist in the warehouse.', columns: [ { key: 'zoneCode', label: 'Zone Code', required: true, example: 'AMB' }, { key: 'code', label: 'Location Code', required: true, example: 'A-01-1-A' }, { key: 'barcode', label: 'Barcode', example: 'A0101A' }, { key: 'checkDigit', label: 'Check Digit', example: '37' }, { key: 'aisle', label: 'Aisle', example: 'A' }, { key: 'bay', label: 'Bay', example: '01' }, { key: 'level', label: 'Level', example: '1' }, { key: 'position', label: 'Position', example: 'A' }, { key: 'type', label: 'Type', example: 'PALLET_RACK' }, { key: 'usage', label: 'Usage', example: 'RESERVE' }, { key: 'maxLpns', label: 'Pallet Positions', example: '1' }, { key: 'cubicCapacityM3', label: 'Cubic Capacity m3', example: '1.8' }, { key: 'weightCapacityKg', label: 'Weight Capacity kg', example: '1200' }, { key: 'pickSequence', label: 'Pick Sequence', example: '10' }, { key: 'allowMixedSku', label: 'Allow Mixed SKU (Y/N)', example: 'N' }, { key: 'allowMixedLots', label: 'Allow Mixed Lots (Y/N)', example: 'Y' }, ], }, ASN: { note: 'Repeat the header columns on every line row; lines are grouped by Reference.', columns: [ { key: 'reference', label: 'Reference (PO)', required: true, example: 'PO-1001' }, { key: 'type', label: 'Type', example: 'PURCHASE' }, { key: 'expectedDate', label: 'Expected Date', example: '2026-10-15' }, { key: 'containerNo', label: 'Container No', example: 'MSKU1234567' }, { key: 'containerSize', label: 'Container Size', example: '40' }, { key: 'supplierCode', label: 'Supplier Code', example: 'SUP01' }, { key: 'skuCode', label: 'SKU Code', required: true, example: 'WIDGET-1' }, { key: 'qty', label: 'Qty', required: true, example: '120' }, { key: 'packLevel', label: 'Pack Level', example: 'CASE' }, { key: 'lot', label: 'Lot', example: 'L2026-01' }, { key: 'expiryDate', label: 'Expiry Date', example: '2027-01-31' }, { key: 'lineRef', label: 'Line Ref', example: '10' }, ], }, ORDER: { note: 'Repeat the header columns on every line row; lines are grouped by Reference.', columns: [ { key: 'reference', label: 'Reference (SO)', required: true, example: 'SO-5001' }, { key: 'customerCode', label: 'Customer Code', example: 'CUST01' }, { key: 'shipToName', label: 'Ship To Name', example: 'Store A' }, { key: 'shipToAddress', label: 'Ship To Address', example: 'Block 3, Shuwaikh' }, { key: 'shipToCity', label: 'City', example: 'Kuwait' }, { key: 'shipToPhone', label: 'Phone', example: '+965 1234 5678' }, { key: 'deliveryLocationCode', label: 'Delivery Location Code', example: 'AV-01' }, { key: 'requestedShipDate', label: 'Requested Ship Date', example: '2026-10-16' }, { key: 'carrierName', label: 'Carrier', example: 'Own fleet' }, { key: 'priority', label: 'Priority 1-9', example: '5' }, { key: 'skuCode', label: 'SKU Code', required: true, example: 'WIDGET-1' }, { key: 'qty', label: 'Qty', required: true, example: '24' }, { key: 'packLevel', label: 'Pack Level', example: 'EACH' }, { key: 'lot', label: 'Requested Lot', example: '' }, { key: 'serials', label: 'Serials (comma separated)', example: '' }, { key: 'lineRef', label: 'Line Ref', example: '10' }, ], }, SERIAL: { note: 'Not used for import; serials are captured on the handheld at receipt.', columns: [] }, CUSTOMER: { note: 'Ship-to parties of the storer.', columns: [{ key: 'code', label: 'Customer Code', required: true, example: 'CUST01' }, { key: 'name', label: 'Name', required: true, example: 'Store A' }, { key: 'address', label: 'Address', example: 'Block 3' }, { key: 'city', label: 'City', example: 'Kuwait' }, { key: 'country', label: 'Country (ISO2)', example: 'KW' }, { key: 'contactName', label: 'Contact', example: 'Ahmed' }, { key: 'phone', label: 'Phone', example: '+965...' }, { key: 'email', label: 'Email', example: 'a@b.com' }], }, SUPPLIER: { note: 'Suppliers (vendors).', columns: [{ key: 'code', label: 'Supplier Code', required: true, example: 'SUP01' }, { key: 'name', label: 'Name', required: true, example: 'Acme Factory' }, { key: 'address', label: 'Address', example: '' }, { key: 'city', label: 'City', example: '' }, { key: 'country', label: 'Country (ISO2)', example: 'CN' }, { key: 'contactName', label: 'Contact', example: '' }, { key: 'phone', label: 'Phone', example: '' }, { key: 'email', label: 'Email', example: '' }], }, }; const yn = (v?: string) => ['Y', 'YES', 'TRUE', '1'].includes((v ?? '').trim().toUpperCase()); const num = (v?: string) => (v === undefined || v === '' ? undefined : v); /** * Excel / CSV imports with per-row validation, dry run, and a job record. */ @Injectable() export class ImportsService { private readonly logger = new Logger(ImportsService.name); constructor( private readonly db: DbService, private readonly skus: SkusService, private readonly warehousesService: WarehousesService, private readonly partners: PartnersService, private readonly asnService: AsnService, private readonly ordersService: OrdersService, ) {} async template(type: ImportType): Promise { const t = IMPORT_TEMPLATES[type]; const wb = new ExcelJS.Workbook(); const ws = wb.addWorksheet(type); ws.addRow(t.columns.map((c) => c.label)); ws.addRow(t.columns.map((c) => c.example)); ws.getRow(1).font = { bold: true }; ws.columns.forEach((c) => (c.width = 20)); const notes = wb.addWorksheet('README'); notes.addRow([t.note]); notes.addRow(['Required columns:', t.columns.filter((c) => c.required).map((c) => c.label).join(', ')]); notes.addRow(['Column key', 'Label']); t.columns.forEach((c) => notes.addRow([c.key, c.label])); return Buffer.from(await wb.xlsx.writeBuffer()); } async run(dto: z.infer, file: { originalname: string; buffer: Buffer; mimetype: string }) { const ctx = requireRequestContext(); const rows = await this.parse(file, IMPORT_TEMPLATES[dto.type].columns); const [job] = await this.db.run((tx) => tx.insert(importJobs).values({ tenantId: ctx.tenantId!, warehouseId: dto.warehouseId ?? null, storerId: dto.storerId ?? null, type: dto.type, fileName: file.originalname, status: 'PROCESSING', dryRun: dto.dryRun, totalRows: rows.length, createdById: ctx.userId! }).returning()); const errors: { row: number; field?: string; message: string }[] = []; const refs: unknown[] = []; let success = 0; try { switch (dto.type) { case 'SKU': ({ success } = await this.importSkus(rows, dto, errors, refs)); break; case 'LOCATION': ({ success } = await this.importLocations(rows, dto, errors, refs)); break; case 'CUSTOMER': case 'SUPPLIER': ({ success } = await this.importPartners(dto.type, rows, dto, errors, refs)); break; case 'ASN': ({ success } = await this.importDocuments('ASN', rows, dto, errors, refs)); break; case 'ORDER': ({ success } = await this.importDocuments('ORDER', rows, dto, errors, refs)); break; default: throw new ValidationError(`Import type ${dto.type} is not supported`); } } catch (e) { errors.push({ row: 0, message: (e as Error).message }); } const [done] = await this.db.run((tx) => tx.update(importJobs).set({ status: errors.length && !success ? 'FAILED' : 'COMPLETED', successRows: success, errorRows: errors.length, errors, resultRefs: refs.slice(0, 500), completedAt: new Date() }).where(eq(importJobs.id, job.id)).returning()); return serialize(done); } list() { return this.db.run((tx) => tx.select().from(importJobs).orderBy(desc(importJobs.createdAt)).limit(100)); } get(id: string) { return this.db.run(async (tx) => { const [j] = await tx.select().from(importJobs).where(eq(importJobs.id, id)).limit(1); if (!j) throw new NotFoundError('Import job', id); return j; }); } // ---------------- parsing ---------------- private async parse(file: { originalname: string; buffer: Buffer; mimetype: string }, columns: { key: string; label: string }[]): Promise { const name = file.originalname.toLowerCase(); let grid: string[][]; if (name.endsWith('.csv') || file.mimetype.includes('csv')) grid = this.parseCsv(file.buffer.toString('utf8')); else { const wb = new ExcelJS.Workbook(); await wb.xlsx.load(file.buffer as unknown as ArrayBuffer); const ws = wb.worksheets[0]; if (!ws) throw new ValidationError('Workbook has no sheets'); grid = []; ws.eachRow({ includeEmpty: false }, (row) => { const vals: string[] = []; for (let c = 1; c <= (ws.columnCount || row.cellCount); c++) { const v = row.getCell(c).value; vals.push(v === null || v === undefined ? '' : typeof v === 'object' && 'result' in (v as object) ? String((v as { result: unknown }).result ?? '') : v instanceof Date ? v.toISOString().slice(0, 10) : typeof v === 'object' && 'text' in (v as object) ? String((v as { text: string }).text) : String(v)); } grid.push(vals); }); } if (grid.length < 2) throw new ValidationError('File has no data rows'); const header = grid[0].map((h) => h.trim()); const keyFor = (h: string) => columns.find((c) => c.key.toLowerCase() === h.toLowerCase() || c.label.toLowerCase() === h.toLowerCase())?.key; const keys = header.map(keyFor); const unknown = header.filter((h, i) => h && !keys[i]); if (unknown.length) this.logger.warn(`Ignoring unknown columns: ${unknown.join(', ')}`); return grid.slice(1).filter((r) => r.some((v) => v && v.trim() !== '')).map((r) => { const row: Row = {}; keys.forEach((k, i) => { if (k) row[k] = (r[i] ?? '').toString().trim(); }); return row; }); } private parseCsv(text: string): string[][] { const out: string[][] = []; let row: string[] = []; let cell = ''; let quoted = false; for (let i = 0; i < text.length; i++) { const ch = text[i]; if (quoted) { if (ch === '"' && text[i + 1] === '"') { cell += '"'; i++; } else if (ch === '"') quoted = false; else cell += ch; } else if (ch === '"') quoted = true; else if (ch === ',') { row.push(cell); cell = ''; } else if (ch === '\n' || ch === '\r') { if (ch === '\r' && text[i + 1] === '\n') i++; row.push(cell); out.push(row); row = []; cell = ''; } else cell += ch; } if (cell || row.length) { row.push(cell); out.push(row); } return out; } // ---------------- importers ---------------- private async importSkus(rows: Row[], dto: z.infer, errors: { row: number; field?: string; message: string }[], refs: unknown[]) { const storerId = resolveStorerScope(dto.storerId); if (!storerId) throw new ValidationError('storerId is required for SKU imports'); const valid: z.infer[] = []; rows.forEach((r, i) => { const packs = []; if (r.caseQty) packs.push({ level: 'CASE', uomCode: 'CS', qtyInBase: r.caseQty, barcode: r.caseBarcode || undefined }); if (r.palletQty) packs.push({ level: 'PALLET', uomCode: 'PL', qtyInBase: r.palletQty, palletTi: r.palletTi ? Number(r.palletTi) : undefined, palletHi: r.palletHi ? Number(r.palletHi) : undefined }); const parsed = CreateSkuSchema.safeParse({ storerId, code: r.code, description: r.description, category: r.category || undefined, brand: r.brand || undefined, storageCondition: r.storageCondition || 'AMBIENT', rotationRule: r.rotationRule || 'FIFO', lotControlled: yn(r.lotControlled), expiryControlled: yn(r.expiryControlled), serialControlled: yn(r.serialControlled), shelfLifeDays: r.shelfLifeDays ? Number(r.shelfLifeDays) : undefined, baseUom: r.baseUom || 'EA', weightKg: num(r.weightKg), cbm: num(r.cbm), hsCode: r.hsCode || undefined, barcodes: r.eanEach ? [{ barcode: r.eanEach, packLevel: 'EACH', isPrimary: true }] : [], packs, }); if (!parsed.success) parsed.error.issues.forEach((is) => errors.push({ row: i + 2, field: is.path.join('.'), message: is.message })); else valid.push(parsed.data); }); if (dto.dryRun) return { success: valid.length }; const res = await this.db.run((tx) => this.skus.upsertMany(tx, storerId, valid)); refs.push({ created: res.created, updated: res.updated }); return { success: valid.length }; } private async importLocations(rows: Row[], dto: z.infer, errors: { row: number; field?: string; message: string }[], refs: unknown[]) { if (!dto.warehouseId) throw new ValidationError('warehouseId is required for LOCATION imports'); const zonesRows = await this.warehousesService.listZones(dto.warehouseId); const zoneId = new Map(zonesRows.map((z) => [z.code, z.id])); let success = 0; for (const [i, r] of rows.entries()) { const zid = zoneId.get(r.zoneCode); if (!zid) { errors.push({ row: i + 2, field: 'zoneCode', message: `Unknown zone ${r.zoneCode}` }); continue; } const parsed = CreateLocationSchema.safeParse({ warehouseId: dto.warehouseId, zoneId: zid, code: r.code, barcode: r.barcode || undefined, checkDigit: r.checkDigit || undefined, aisle: r.aisle || undefined, bay: r.bay || undefined, level: r.level || undefined, position: r.position || undefined, type: r.type || 'PALLET_RACK', usage: r.usage || 'RESERVE', maxLpns: r.maxLpns ? Number(r.maxLpns) : undefined, cubicCapacityM3: num(r.cubicCapacityM3), weightCapacityKg: num(r.weightCapacityKg), pickSequence: r.pickSequence ? Number(r.pickSequence) : 0, allowMixedSku: r.allowMixedSku ? yn(r.allowMixedSku) : true, allowMixedLots: r.allowMixedLots ? yn(r.allowMixedLots) : true }); if (!parsed.success) { parsed.error.issues.forEach((is) => errors.push({ row: i + 2, field: is.path.join('.'), message: is.message })); continue; } if (dto.dryRun) { success++; continue; } try { const row = await this.warehousesService.createLocation(parsed.data); refs.push({ id: (row as { id: string }).id, code: r.code }); success++; } catch (e) { errors.push({ row: i + 2, message: (e as Error).message }); } } return { success }; } private async importPartners(type: 'CUSTOMER' | 'SUPPLIER', rows: Row[], dto: z.infer, errors: { row: number; field?: string; message: string }[], refs: unknown[]) { const storerId = resolveStorerScope(dto.storerId); if (type === 'CUSTOMER' && !storerId) throw new ValidationError('storerId is required for CUSTOMER imports'); let success = 0; for (const [i, r] of rows.entries()) { const base = { code: r.code, name: r.name, address: { line1: r.address || undefined, city: r.city || undefined, country: r.country || undefined }, contacts: r.contactName || r.phone || r.email ? [{ name: r.contactName || r.name, phone: r.phone || undefined, email: r.email || undefined }] : [] }; const parsed = type === 'CUSTOMER' ? CreateCustomerSchema.safeParse({ ...base, storerId }) : CreateSupplierSchema.safeParse({ ...base, storerId: storerId ?? null }); if (!parsed.success) { parsed.error.issues.forEach((is) => errors.push({ row: i + 2, field: is.path.join('.'), message: is.message })); continue; } if (dto.dryRun) { success++; continue; } try { const row = type === 'CUSTOMER' ? await this.partners.createCustomer(parsed.data as never) : await this.partners.createSupplier(parsed.data as never); refs.push({ id: (row as { id: string }).id, code: r.code }); success++; } catch (e) { errors.push({ row: i + 2, message: (e as Error).message }); } } return { success }; } private async importDocuments(kind: 'ASN' | 'ORDER', rows: Row[], dto: z.infer, errors: { row: number; field?: string; message: string }[], refs: unknown[]) { const storerId = resolveStorerScope(dto.storerId); if (!storerId || !dto.warehouseId) throw new ValidationError('storerId and warehouseId are required'); const groups = new Map(); rows.forEach((r, i) => { if (!r.reference) { errors.push({ row: i + 2, field: 'reference', message: 'Reference is required' }); return; } const g = groups.get(r.reference) ?? { rows: [], firstRow: i + 2 }; g.rows.push(r); groups.set(r.reference, g); }); let success = 0; for (const [reference, g] of groups) { const head = g.rows[0]; try { if (kind === 'ASN') { const lines = g.rows.map((r, li) => AsnLineInputSchema.parse({ lineNo: li + 1, skuCode: r.skuCode, expectedQty: r.qty, packLevel: r.packLevel || 'EACH', expectedLot: r.lot || undefined, expectedExpiry: r.expiryDate || undefined, externalLineRef: r.lineRef || undefined })); if (dto.dryRun) { success++; continue; } const a = await this.asnService.create({ warehouseId: dto.warehouseId, storerId, externalRef: reference, type: (head.type as never) || 'PURCHASE', priority: 5, expectedAt: head.expectedDate ? `${head.expectedDate}T08:00:00+03:00` : undefined, containerNo: head.containerNo || undefined, containerSize: (head.containerSize as never) || undefined, documents: [], attributes: { supplierCode: head.supplierCode }, lines }, 'IMPORT'); refs.push({ reference, number: (a as { asnNumber: string }).asnNumber }); } else { const lines = g.rows.map((r, li) => OrderLineInputSchema.parse({ lineNo: li + 1, skuCode: r.skuCode, orderedQty: r.qty, packLevel: r.packLevel || 'EACH', requestedLot: r.lot || undefined, requestedSerials: r.serials ? r.serials.split(',').map((s) => s.trim()).filter(Boolean) : [], externalLineRef: r.lineRef || undefined })); if (dto.dryRun) { success++; continue; } const o = await this.ordersService.create({ warehouseId: dto.warehouseId, storerId, externalRef: reference, type: 'SALES', priority: head.priority ? Number(head.priority) : 5, customerCode: head.customerCode || undefined, shipTo: { name: head.shipToName || undefined, phone: head.shipToPhone || undefined, address: { line1: head.shipToAddress || undefined, city: head.shipToCity || undefined }, deliveryLocationCode: head.deliveryLocationCode || undefined }, carrierName: head.carrierName || undefined, requestedShipDate: head.requestedShipDate || undefined, attributes: {}, lines }, 'IMPORT'); refs.push({ reference, number: (o as { orderNumber: string }).orderNumber }); } success++; } catch (e) { const msg = e instanceof z.ZodError ? e.issues.map((i) => `${i.path.join('.')}: ${i.message}`).join('; ') : (e as Error).message; errors.push({ row: g.firstRow, field: 'reference', message: `${reference}: ${msg}` }); } } return { success }; } }