inventoryservice.ts

PHOTO EMBED

Tue Dec 19 2023 12:06:06 GMT+0000 (Coordinated Universal Time)

Saved by @thanuj #sql 

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;
  }
}
content_copyCOPY