import { DomainEntityService } from '../base/DomainEntityService'; import { Inventory } from './Inventory'; import { Service, Inject } from 'typedi'; import { FindOptions } from '../base/FindOptions'; import { Equal, In } from 'typeorm'; import { ConfigurationService } from '../configuration/ConfigurationService'; import { CustomerService } from '../customer/CustomerService'; import { PartnerService } from '../partner/PartnerService'; import { DynamicAttrsService } from '../attribute/DynamicAttrsService'; import { DataFileService } from '../datafile/DataFileService'; import { SubmissionPeriodService } from '../submission/SubmissionPeriodService'; import { InventoryQuantityService } from './InventoryQuantityService'; import { InventoryPriceService } from './InventoryPriceService'; import { Writer } from '../../writer/Writer'; import { InventoryInput } from './InventoryInput'; import { ServiceError } from '../base/ServiceError'; import { Operator } from '../base/filters/Operator'; import { AppDataSource } from '../../platform/DataSource'; @Service() export class InventoryService extends DomainEntityService<Inventory> { @Inject() protected configurationService: ConfigurationService; @Inject() protected customerService: CustomerService; @Inject() protected partnerService: PartnerService; @Inject() protected dynamicAttrsService: DynamicAttrsService; @Inject() protected dataFileService: DataFileService; @Inject() protected submissionPeriodService: SubmissionPeriodService; @Inject() protected inventoryQuantityService: InventoryQuantityService; @Inject() protected inventoryPriceService: InventoryPriceService; @Inject('Writer') protected writer: Writer; constructor() { super(Inventory); } getServiceName(): string { return 'Inventory'; } async findNeedCorrectionInventory( customerId: string, partnerId: string, options: FindOptions = new FindOptions() ): Promise<Inventory[]> { const customer = await this.customerService.findOneById(customerId); const partner = await this.partnerService.findOneById(partnerId); const { offset, limit, filters = {}, sort } = options; if (!customer || (!partner && !PartnerService.isAll(partnerId))) { return Promise.resolve([]); } const config = await this.configurationService.getConfiguration(customerId); const queueName = config.get('needCorrectionInventoryQueueName', 'resign'); const maxAge = config.get('nmiMaxAgeInDays', '30'); const query = this.repository .createQueryBuilder('inv') .leftJoinAndSelect( 'inv.dynamicAttrs', 'da', '"da"."ATTRIBUTE_TYPE" = \'IL\' and "da"."CUSTOMER_SID" = "inv"."CUSTOMER_SID"' ) .innerJoinAndSelect( 'inv.dataFile', 'df', '"df"."CUSTOMER_SID" = "inv"."CUSTOMER_SID"' ) .leftJoinAndSelect( 'inv.submissionPeriod', 'sp', '"sp"."CUSTOMER_SID" = "inv"."CUSTOMER_SID"' ) .leftJoinAndSelect( 'sp.submissionSchedule', 'ss', '"sp"."CUSTOMER_SID" = "ss"."CUSTOMER_SID"' ) .leftJoinAndSelect('ss.dataType', 'dt') .leftJoinAndSelect( 'sp.submissionPeriodLineItemView', 'spli', '"sp"."CUSTOMER_SID" = "spli"."CUSTOMER_SID"' ) .leftJoinAndSelect( 'sp.dataFileSummaryInfo', 'dfsi', '"sp"."CUSTOMER_SID" = "dfsi"."CUSTOMER_SID"' ) .leftJoinAndMapOne( 'inv.onHandQuantity', 'inv.inventoryQuantities', 'onHandInvQuantity', '"onHandInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'ON HAND\')' ) .leftJoinAndMapOne( 'inv.onOrderQuantity', 'inv.inventoryQuantities', 'onOrderInvQuantity', '"onOrderInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'ON ORDER\')' ) .leftJoinAndMapOne( 'inv.committedQuantity', 'inv.inventoryQuantities', 'committedInvQuantity', '"committedInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'COMMITTED\')' ) .leftJoinAndMapOne( 'inv.floatQuantity', 'inv.inventoryQuantities', 'floatInvQuantity', '"floatInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'FLOAT\')' ) .leftJoinAndMapOne( 'inv.backorderedQuantity', 'inv.inventoryQuantities', 'backorderedInvQuantity', '"backorderedInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'BACKORDERED\')' ) .leftJoinAndMapOne( 'inv.returnedQuantity', 'inv.inventoryQuantities', 'returnedInvQuantity', '"returnedInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'RETURNED\')' ) .leftJoinAndMapOne( 'inv.inTransitQuantity', 'inv.inventoryQuantities', 'inTransitInvQuantity', '"inTransitInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'TRANSIT\')' ) .leftJoinAndMapOne( 'inv.unitPrice', 'inv.inventoryPrices', 'invPrice', '"invPrice"."PRICE_TYPE_SID" = ( SELECT SID FROM PRICE_TYPE WHERE ' + 'NAME = \'REPORTED_PRICE\') AND "invPrice"."DELETED" = 0' ) .innerJoin( 'PEH_INV_QUEUE', 'piq', '"piq"."CLIENT_SKU" = "inv"."CLIENT_SKU" and "piq"."INV_LINE_ITEM_SID" = "inv"."SID" ' + 'and "piq"."CUSTOMER_SID" = "inv"."CUSTOMER_SID"' ) .offset(offset) .limit(limit); // add filters for quantity filters let quantityFiltersMap = new Map(); quantityFiltersMap.set('onOrderQuantity', 'onOrderInvQuantity'); quantityFiltersMap.set('onHandQuantity', 'onHandInvQuantity'); quantityFiltersMap.set('committedQuantity', 'committedInvQuantity'); quantityFiltersMap.set('floatQuantity', 'floatInvQuantity'); quantityFiltersMap.set('backorderedQuantity', 'backorderedInvQuantity'); quantityFiltersMap.set('returnedQuantity', 'returnedInvQuantity'); quantityFiltersMap.set('inTransitQuantity', 'inTransitInvQuantity'); for (let [key, value] of quantityFiltersMap) { const quantityFilters = filters[key]; delete filters[key]; if (quantityFilters) { const quantityWhere = this.inventoryQuantityService.buildWhereExpression( quantityFilters, value ); query.andWhere(quantityWhere); } } // add unit price condition const unitPriceFilters = filters['unitPrice']; delete filters['unitPrice']; if (unitPriceFilters) { const unitPriceWhere = this.inventoryPriceService.buildWhereExpression( unitPriceFilters, 'invPrice' ); query.andWhere(unitPriceWhere); } this.buildWhere(filters, query); query.andWhere('"inv"."CUSTOMER_SID" = ' + customer.sid) .andWhere('"inv"."INVENTORY_DATE" >= sysdate - ' + maxAge) .andWhere('"piq"."QUEUE_NAME" = \'' + queueName + "'") .andWhere( { 'deleted' : Equal(0) } ); if (!PartnerService.isAll(partnerId)) { query.andWhere('"inv"."REPORTING_PARTNER_SID" = ' + partner.sid); } // add order by this.addOrderBys(query, sort); return query.getMany(); } async updateInventory( customerId: string, partnerId: string, data: InventoryInput[] ): Promise<ServiceError[]> { let inventoryToCreate: Inventory[] = []; let sids: number[] = data.map((input: InventoryInput) => { return input.sid; }); let errors: ServiceError[] = []; // Check if partner has access to ili let options: FindOptions = { offset: 0, limit: 1000, filters: { sid: { operator: Operator.IN, values: sids } } }; let inventory: Inventory[] = await this.findNeedCorrectionInventory( customerId, partnerId, options ); let sidsToUpdate: number[] = []; errors = sids.map((sid) => { if (inventory) { let s: Inventory = inventory.find((inv: Inventory) => { return inv.sid.toString() === sid.toString(); }); if (s) { sidsToUpdate.push(sid); return null; } } return new ServiceError( 'INVENTORY_NOT_FOUND', `Inventory line sid : ${sid} not found` ); }); if (inventory && inventory.length > 0) { let invLineItems: Inventory[] = await this.repository .createQueryBuilder('inv') // load partner and gs number .innerJoinAndSelect('inv.reportingPartner', 'rp') .leftJoinAndSelect('rp.gsNumbers', 'gs') // load dynamic attrs .leftJoinAndSelect('inv.dynamicAttrs', 'da') .leftJoinAndMapOne( 'inv.onHandQuantity', 'inv.inventoryQuantities', 'onHandInvQuantity', '"onHandInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'ON HAND\')' ) .leftJoinAndMapOne( 'inv.onOrderQuantity', 'inv.inventoryQuantities', 'onOrderInvQuantity', '"onOrderInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'ON ORDER\')' ) .leftJoinAndMapOne( 'inv.committedQuantity', 'inv.inventoryQuantities', 'committedInvQuantity', '"committedInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'COMMITTED\')' ) .leftJoinAndMapOne( 'inv.floatQuantity', 'inv.inventoryQuantities', 'floatInvQuantity', '"floatInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'FLOAT\')' ) .leftJoinAndMapOne( 'inv.backorderedQuantity', 'inv.inventoryQuantities', 'backorderedInvQuantity', '"backorderedInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'BACKORDERED\')' ) .leftJoinAndMapOne( 'inv.returnedQuantity', 'inv.inventoryQuantities', 'returnedInvQuantity', '"returnedInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'RETURNED\')' ) .leftJoinAndMapOne( 'inv.inTransitQuantity', 'inv.inventoryQuantities', 'inTransitInvQuantity', '"inTransitInvQuantity"."QUANTITY_TYPE_SID" = ( SELECT SID FROM QUANTITY_TYPE WHERE QUANTITY_TYPE = \'TRANSIT\')' ) .leftJoinAndMapOne( 'inv.unitPrice', 'inv.inventoryPrices', 'invPrice', '"invPrice"."PRICE_TYPE_SID" = ( SELECT SID FROM PRICE_TYPE WHERE NAME = \'REPORTED_PRICE\') AND "invPrice"."DELETED" = 0' ) .where({ sid: In(sidsToUpdate) }) .andWhere('"gs"."CUSTOMER_SID" = :customerSid', { customerSid: inventory[0].customerSid }) .getMany(); await Promise.all( invLineItems.map(async (invLineItem) => { // load serial numbers let input: InventoryInput = data.find((inv: InventoryInput) => { return inv.sid.toString() === invLineItem.sid.toString(); }); invLineItem = Object.assign({}, invLineItem, input); // add inv line to list to write to S3. inventoryToCreate.push(invLineItem); }) ); console.log(`Inventory to write to S3 : ${inventoryToCreate.length}`); // Write to S3 if (inventoryToCreate.length > 0) { await this.writer.write( customerId, partnerId, this.getServiceName(), inventoryToCreate ); } // It is safe to delete from PEH after writing to S3, just in case if delete fails, // the inv will still be deleted from PEH the json file from S3 is loaded into nucleus. // If we delete first and for some reason the writing to S3 fails, there is no way to roll back the db update. await Promise.all( inventoryToCreate.map(async (invLineItem) => { try { // delete inventory line from PEH await AppDataSource.query( 'delete from peh_inv_queue where inv_line_item_sid = :sid', [invLineItem.sid] ); console.log(`Deleted line item : ${invLineItem.sid} from PEH`); } catch (e) { console.log( `Error while deleting from PEH for inv line : ${JSON.stringify( invLineItem )} for Customer : ${customerId}` ); console.error(e); // eat the error as if delete fails, the only con is User will still be able to see the inv line // until the file on S3 is loaded into nucleus } }) ); } return errors; } }
Preview:
downloadDownload PNG
downloadDownload JPEG
downloadDownload SVG
Tip: You can change the style, width & colours of the snippet with the inspect tool before clicking Download!
Click to optimize width for Twitter