multi table + multi data source customer model

base-data-model.js

const { isUndefined, omitBy } = require(`lodash/fp`);
const hash = require(`object-hash`);

class DataModel {
  constructor(cache) {
    if (isUndefined(cache)) {
      throw new Error(`Data Models require a cache`);
    }

    this._cache = cache;
  }

  get models() {
    return this._models;
  }

  set models(models) {
    // Remove self-reference
    this._models = omitBy(value => value === this, models);
  }

  withCache(keyObj, ttl, execute) {
    // Construct a key in the form: ClassName:hash-of-execute-fn:hash-of-key
    const className = Reflect.getPrototypeOf(this).constructor.name;
    const cacheKey = `${className}:${hash(execute)}:${hash(keyObj)}`;

    const cachedResponse = this._cache.get(cacheKey);
    if (cachedResponse) {
      return cachedResponse;
    }

    const result = Promise.resolve()
      // Ensure that we're caching a Promise
      .then(() => execute())
      .then(data => ({
        __isUsingModelCache__: true,
        data,
        ttl
      }))
      // On Error, clear the cache and rethrow to let GraphQL deal with the exception
      .catch(err => {
        this._cache.del(cacheKey);
        throw err;
      });

    this._cache.set(cacheKey, result, ttl);

    return result;
  }
}

module.exports = DataModel;

graphql-customer.js

const {
  GraphQLBoolean,
  GraphQLFloat,
  GraphQLInt,
  GraphQLList,
  GraphQLObjectType,
  GraphQLString
} = require(`graphql`);

const { withCache } = require(`../util`);
const { Addresses, addressesResolver } = require(`../common/address`);
const { AddressType, PaymentTerm } = require(`../common/enums`);
const { Branch, branchResolver } = require(`../branch`);
const { LedgerLines, ledgerLinesResolver } = require(`./ledger`);
const { SalesOrders, salesOrdersResolver } = require(`../sales-order`);
const { Salesperson, salespersonResolver } = require(`../salesperson`);

const OutstandingBalance = new GraphQLObjectType({
  name: `OutstandingBalance`,
  fields: {
    under30: {
      type: GraphQLFloat,
      resolve: withCache(({ id }, _, { models }) =>
        models.customer.getOutstandingBalance(id, 0, 30)
      )
    },
    over30: {
      type: GraphQLFloat,
      resolve: withCache(({ id }, _, { models }) =>
        models.customer.getOutstandingBalance(id, 30, 60)
      )
    },
    over60: {
      type: GraphQLFloat,
      resolve: withCache(({ id }, _, { models }) =>
        models.customer.getOutstandingBalance(id, 60, 90)
      )
    },
    over90: {
      type: GraphQLFloat,
      resolve: withCache(({ id }, _, { models }) =>
        models.customer.getOutstandingBalance(id, 90)
      )
    },
    total: {
      type: GraphQLFloat,
      resolve: withCache(({ id }, _, { models }) =>
        models.customer.getOutstandingBalance(id)
      )
    }
  }
});

const Customer = new GraphQLObjectType({
  name: `Customer`,
  fields: () => ({
    id: { type: GraphQLString },
    // There is a bug that only allows us to use only some of the customer addresses because
    // the query is incomplete. We can't implement the proper query today because it needs to
    // be optimized to run on all 14k of our customers for search purposes.
    // https://gitlab.com/lnc-dev/constellation/issues/237
    addresses: {
      type: Addresses,
      args: {
        type: { type: AddressType }
      },
      resolve: ({ addresses }, { type }) =>
        addressesResolver(addresses, { type })
    },
    branch: {
      type: Branch,
      resolve: ({ branchId }, args, context, info) =>
        branchResolver(null, { id: branchId }, context, info)
    },
    contact: { type: GraphQLString },
    creditApp: {
      type: GraphQLString,
      description: `Denotes the status of a customer's credit application`
    },
    creditLimit: { type: GraphQLInt },
    isOnHold: { type: GraphQLBoolean },
    name: { type: GraphQLString },
    ledger: {
      type: LedgerLines,
      resolve: ledgerLinesResolver
    },
    outstandingBalance: {
      type: OutstandingBalance,
      resolve: rawCustomer => rawCustomer
    },
    paymentTerm: {
      type: PaymentTerm,
      description: `This term specifies the period allowed to a buyer to pay off the amount due.`
    },
    salesOrders: {
      type: SalesOrders,
      args: {
        shouldExcludeCanceledOrders: { type: GraphQLBoolean }
      },
      resolve: ({ id }, { shouldExcludeCanceledOrders }, context, info) =>
        salesOrdersResolver(
          null,
          { customerId: id, shouldExcludeCanceledOrders },
          context,
          info
        )
    },
    salesperson: {
      type: Salesperson,
      resolve: ({ salespersonId }, args, context, info) =>
        salespersonResolver(null, { id: salespersonId }, context, info)
    },
    telephone: { type: GraphQLString }
  })
});

const customerResolver = withCache((_, { id }, { models }) =>
  models.customer.getCustomerById(id)
);

const customersResolver = withCache((_, args, { models }) =>
  models.customer.getAllCustomers()
);

module.exports = {
  Customer,
  Customers: new GraphQLList(Customer),
  customerResolver,
  customersResolver
};

mssql-customer.js

const DataLoader = require(`dataloader`);
const { flow, get, trim, mapValues } = require(`lodash/fp`);

const {
  ADDRESS_TYPES,
  CREDIT_APP_STATUSES,
  TIME_IN_MILLISECONDS
} = require(`../../constants`);
const DataModel = require(`./lib/base-data-model`);
const db = require(`../../db`);
const { groupFieldsByType, orderGroupBy, sortResults } = require(`./lib/util`);

const formatHoldStatus = rawResult => ({
  ...rawResult,
  // CustomerOnHold is equal to "Y" or "N". Why we don't use Booleans in the DB, I have no idea...
  isOnHold: rawResult.isOnHold === `Y`
});

const formatCustomers = customers =>
  customers.map(
    flow(
      mapValues(trim),
      formatHoldStatus,
      groupFieldsByType(`address`)(`addresses`)
    )
  );

const formatAddresses = addresses =>
  addresses
    .reduce(
      (addressBook, value) =>
        addressBook.concat(
          flow(
            groupFieldsByType(`address`)(`addresses`),
            get(`addresses`)
          )(value)
        ),
      []
    )
    .map(mapValues(trim));

// In SYSPRO, the credit app status field is not an ENUM. In an effort to keep these values consistent,
// we log an error if there is a customer with an invalid status.
const verifyCreditAppStatuses = customers =>
  customers.map(customer => {
    if (!CREDIT_APP_STATUSES.includes(customer.creditApp)) {
      // TODO: Fix credit app values in the SYSPRO DB and log an error when an invalid value is found
      // https://gitlab.com/lnc-dev/constellation/issues/292
      //
      // logger.error(
      //   `"${
      //     customer.creditApp
      //   }" is not a valid credit app status. Please update ${customer.id} (${
      //     customer.name
      //   })`
      // );

      return {
        ...customer,
        creditApp: null
      };
    }

    return customer;
  });

const soldToType = ADDRESS_TYPES.soldTo.value;
const shipToType = ADDRESS_TYPES.shipTo.value;

const getBaseCustomer = () =>
  db
    .table(`ArCustomer`)
    .select([
      db.ref(`ArCustomer.Branch`).as(`branchId`),
      db.ref(`ArCustomer.Name`).as(`name`),
      db.ref(`Contact`).as(`contact`),
      db.ref(`CreditLimit`).as(`creditLimit`),
      db.ref(`Customer`).as(`id`),
      db.ref(`CustomerOnHold`).as(`isOnHold`),
      db.ref(`Salesperson`).as(`salespersonId`),
      db.ref(`ArCustomer.Name`).as(`address:${soldToType}:recipientName`),
      db.ref(`SoldPostalCode`).as(`address:${soldToType}:zip`),
      db.ref(`SoldToAddr1`).as(`address:${soldToType}:addressLine1`),
      db.ref(`SoldToAddr2`).as(`address:${soldToType}:addressLine2`),
      db.ref(`SoldToAddr3`).as(`address:${soldToType}:city`),
      db.ref(`SoldToAddr4`).as(`address:${soldToType}:state`),
      db.ref(`SoldToAddr5`).as(`address:${soldToType}:county`),
      db.ref(`Telephone`).as(`telephone`),
      db.ref(`TermsCode`).as(`paymentTerm`),
      db.ref(`UserField1`).as(`creditApp`)
    ]);

const getCustomersByIds = ids =>
  getBaseCustomer()
    .leftJoin(
      `TblCustomerClass`,
      `ArCustomer.CustomerClass`,
      `=`,
      `TblCustomerClass.Class`
    )
    .select([db.ref(`TblCustomerClass.Description`).as(`customerClass`)])
    .whereIn(`Customer`, ids);

// This query works for our current needs, but will need to be replaced with
// a more performant strategy to integrate the full customer address book
// throughout all of the app. https://gitlab.com/lnc-dev/constellation/issues/237
const getCustomerAddressBooks = ids =>
  db
    .table(`ArCustomer`)
    .select([
      db.ref(`ArCustomer.Customer`).as(`address:${shipToType}:customerId`),
      db.ref(`ArCustomer.Name`).as(`address:${shipToType}:recipientName`),
      db.ref(`ShipToAddr1`).as(`address:${shipToType}:addressLine1`),
      db.ref(`ShipToAddr2`).as(`address:${shipToType}:addressLine2`),
      db.ref(`ShipToAddr3`).as(`address:${shipToType}:city`),
      db.ref(`ShipToAddr4`).as(`address:${shipToType}:state`),
      db.ref(`ShipToAddr5`).as(`address:${shipToType}:county`),
      db.ref(`ShipPostalCode`).as(`address:${shipToType}:zip`)
    ])
    .leftJoin(`ArCustomer+`, `ArCustomer.Customer`, `=`, `ArCustomer+.Customer`)
    .whereIn(`ArCustomer.Customer`, ids)
    .union(function() {
      this.select([
        db.ref(`ArMultAddress.Customer`).as(`address:${shipToType}:customerId`),
        db.ref(`ShipToName`).as(`address:${shipToType}:recipientName`),
        db.ref(`ShipToAddr1`).as(`address:${shipToType}:addressLine1`),
        db.ref(`ShipToAddr2`).as(`address:${shipToType}:addressLine2`),
        db.ref(`ShipToAddr3`).as(`address:${shipToType}:city`),
        db.ref(`ShipToAddr4`).as(`address:${shipToType}:state`),
        db.ref(`ShipToAddr5`).as(`address:${shipToType}:county`),
        db.ref(`ShipPostalCode`).as(`address:${shipToType}:zip`)
      ])
        .from(`ArMultAddress`)
        .leftJoin(
          `ArMultAddress+`,
          `ArMultAddress.AddrCode`,
          `=`,
          `ArMultAddress+.AddrCode`
        )
        .whereIn(`ArMultAddress.Customer`, ids);
    });

class Customer extends DataModel {
  constructor(cache) {
    super(cache);

    this.customerById = new DataLoader(ids =>
      getCustomersByIds(ids)
        .then(verifyCreditAppStatuses)
        .then(formatCustomers)
        .then(sortResults(ids))
    );

    this.addressesByCustomerId = new DataLoader(ids =>
      getCustomerAddressBooks(ids)
        .then(formatAddresses)
        .then(orderGroupBy(ids, `customerId`))
    );
  }

  getAllCustomers() {
    return this.withCache({}, TIME_IN_MILLISECONDS.ONE_HOUR, () =>
      getBaseCustomer()
        .then(verifyCreditAppStatuses)
        .then(formatCustomers)
    );
  }

  getCustomerById(id) {
    return this.withCache(
      Array.from(arguments),
      TIME_IN_MILLISECONDS.ONE_HOUR,
      () => this.customerById.load(id)
    );
  }

  getLedgerLinesByCustomerId(id) {
    return this.models.accountsReceivable.getLedgerLinesByCustomerId(id);
  }

  getOutstandingBalance(id, atLeastDaysOld, atMostDaysOld) {
    return this.models.accountsReceivable.getOutstandingBalanceByCustomerId(
      id,
      atLeastDaysOld,
      atMostDaysOld
    );
  }

  getAddressBookByCustomerId(id) {
    return this.withCache(
      Array.from(arguments),
      TIME_IN_MILLISECONDS.ONE_DAY,
      () => this.addressesByCustomerId.load(id)
    );
  }
}

module.exports = Customer;