bodyshop/server/accounting/qbo/qbo-payables.js

const urlBuilder = require("./qbo").urlBuilder;
const StandardizeName = require("./qbo").StandardizeName;

const logger = require("../../utils/logger");
const Dinero = require("dinero.js");
const DineroQbFormat = require("../accounting-constants").DineroQbFormat;
const apiGqlClient = require("../../graphql-client/graphql-client").client;
const queries = require("../../graphql-client/queries");
const { refresh: refreshOauthToken } = require("./qbo-callback");
const OAuthClient = require("intuit-oauth");
const moment = require("moment-timezone");
const findTaxCode = require("../qb-receivables-lines").findTaxCode;

exports.default = async (req, res) => {
  const oauthClient = new OAuthClient({
    clientId: process.env.QBO_CLIENT_ID,
    clientSecret: process.env.QBO_SECRET,
    environment: process.env.NODE_ENV === "production" ? "production" : "sandbox",
    redirectUri: process.env.QBO_REDIRECT_URI
  });

  try {
    //Fetch the API Access Tokens & Set them for the session.
    const response = await apiGqlClient.request(queries.GET_QBO_AUTH, {
      email: req.user.email
    });

    const { qbo_realmId } = response.associations[0];

    oauthClient.setToken(response.associations[0].qbo_auth);

    if (!qbo_realmId) {
      res.status(401).json({ error: "No company associated." });
      return;
    }

    await refreshOauthToken(oauthClient, req);

    const { bills: billsToQuery, elgen } = req.body;

    const BearerToken = req.BearerToken;
    const client = req.userGraphQLClient;

    logger.log("qbo-payable-create", "DEBUG", req.user.email, null, { billsToQuery });

    const result = await client
      .setHeaders({ Authorization: BearerToken })
      .request(queries.QUERY_BILLS_FOR_PAYABLES_EXPORT, {
        bills: billsToQuery
      });

    const { bills, bodyshops } = result;
    const ret = [];
    const bodyshop = bodyshops[0];

    for (const bill of bills) {
      try {
        let vendorRecord;
        vendorRecord = await QueryVendorRecord(oauthClient, qbo_realmId, req, bill);

        if (!vendorRecord) {
          vendorRecord = await InsertVendorRecord(oauthClient, qbo_realmId, req, bill);
        }

        await InsertBill(oauthClient, qbo_realmId, req, bill, vendorRecord, bodyshop);

        // //No error. Mark the job exported & insert export log.
        if (elgen) {
          await client.setHeaders({ Authorization: BearerToken }).request(queries.QBO_MARK_BILL_EXPORTED, {
            billId: bill.id,
            bill: {
              exported: true,
              exported_at: moment().tz(bodyshop.timezone)
            },
            logs: [
              {
                bodyshopid: bodyshop.id,
                billid: bill.id,
                successful: true,
                useremail: req.user.email
              }
            ]
          });
        }

        ret.push({ billid: bill.id, success: true });
      } catch (error) {
        logger.log("qbo-paybles-create-error", "ERROR", req.user.email, null, {
          error:
            error?.authResponse?.body ||
            error?.response?.data?.Fault?.Error.map((e) => e.Detail).join(", ") ||
            error?.message
        });
        ret.push({
          billid: bill.id,
          success: false,
          errorMessage:
            error?.authResponse?.body ||
            error?.response?.data?.Fault?.Error.map((e) => e.Detail).join(", ") ||
            error?.message
        });

        //Add the export log error.
        if (elgen) {
          await client.setHeaders({ Authorization: BearerToken }).request(queries.INSERT_EXPORT_LOG, {
            logs: [
              {
                bodyshopid: bodyshop.id,
                billid: bill.id,
                successful: false,
                message: JSON.stringify([error?.authResponse?.body || error?.message]),
                useremail: req.user.email
              }
            ]
          });
        }
      }
    }

    res.status(200).json(ret);
  } catch (error) {
    //console.log(error);
    logger.log("qbo-payable-create-error", "ERROR", req.user.email, null, {
      error: error.message,
      stack: error.stack
    });
    res.status(400).json(error);
  }
};

async function QueryVendorRecord(oauthClient, qbo_realmId, req, bill) {
  try {
    const url = urlBuilder(
      qbo_realmId,
      "query",
      `select * From vendor where DisplayName = '${StandardizeName(bill.vendor.name)}'`
    );
    const result = await oauthClient.makeApiCall({
      url: url,
      method: "POST",
      headers: {
        "Content-Type": "application/json"
      }
    });
    logger.LogIntegrationCall({
      platform: "QBO",
      method: "POST",
      name: "QueryVendorRecord",
      billid: bill.id,
      status: result.status,
      bodyshopid: bill.job.shopid,
      email: req.user.email
    });
    logger.log("qbo-payables-query", "DEBUG", req.user.email, null, {
      method: "QueryVendorRecord",
      call: url,
      result: result.json
    });

    return result.json?.QueryResponse?.Vendor?.[0];
  } catch (error) {
    logger.log("qbo-payables-error", "DEBUG", req.user.email, bill.id, {
      method: "QueryVendorRecord",
      error: error.message,
      stack: error.stack
    });
    throw error;
  }
}

async function InsertVendorRecord(oauthClient, qbo_realmId, req, bill) {
  const Vendor = {
    DisplayName: StandardizeName(bill.vendor.name)
  };
  try {
    const url = urlBuilder(qbo_realmId, "vendor");
    const result = await oauthClient.makeApiCall({
      url: url,
      method: "POST",
      headers: {
        "Content-Type": "application/json"
      },
      body: JSON.stringify(Vendor)
    });
    logger.LogIntegrationCall({
      platform: "QBO",
      method: "POST",
      name: "InsertVendorRecord",
      billid: bill.id,
      status: result.status,
      bodyshopid: bill.job.shopid,
      email: req.user.email
    });
    logger.log("qbo-payments-insert", "DEBUG", req.user.email, null, {
      method: "InsertVendorRecord",
      call: url,
      Vendor: Vendor,
      result: result.json
    });

    if (result.status >= 400) {
      throw new Error(JSON.stringify(result.json.Fault));
    }
    if (result.status === 200) return result?.json.Vendor;
  } catch (error) {
    logger.log("qbo-payables-error", "DEBUG", req.user.email, bill.id, {
      method: "InsertVendorRecord",
      validationError: error.message,
      stack: error.stack
    });
    throw error;
  }
}

async function InsertBill(oauthClient, qbo_realmId, req, bill, vendor, bodyshop) {
  const { accounts, taxCodes, classes } = await QueryMetaData(oauthClient, qbo_realmId, req, bill.job.shopid);
  let lines;
  if (bodyshop.accountingconfig.accumulatePayableLines === true) {
    lines = Object.values(
      bill.billlines.reduce((acc, il) => {
        const { cost_center, actual_cost, quantity = 1 } = il;

        if (!acc[cost_center]) {
          acc[cost_center] = { ...il, actual_cost: 0, quantity: 1 };
        }

        acc[cost_center].actual_cost += Math.round(actual_cost * quantity * 100);

        return acc;
      }, {})
    ).map((il) => {
      il.actual_cost /= 100;
      return generateBillLine(
        il,
        accounts,
        bill.job.class,
        bodyshop.md_responsibility_centers.sales_tax_codes,
        classes,
        taxCodes,
        bodyshop.md_responsibility_centers.costs,
        bodyshop.accountingconfig,
        bodyshop.region_config
      );
    });
  } else {
    lines = bill.billlines.map((il) =>
      generateBillLine(
        il,
        accounts,
        bill.job.class,
        bodyshop.md_responsibility_centers.sales_tax_codes,
        classes,
        taxCodes,
        bodyshop.md_responsibility_centers.costs,
        bodyshop.accountingconfig,
        bodyshop.region_config
      )
    );
  }
  //QB USA with GST
  //This was required for the No. 1 Collision Group.
  if (bodyshop.accountingconfig?.qbo && bodyshop.accountingconfig?.qbo_usa && bodyshop.region_config.includes("CA_")) {
    lines.push({
      DetailType: "AccountBasedExpenseLineDetail",

      AccountBasedExpenseLineDetail: {
        ...(bill.job.class ? { ClassRef: { value: classes[bill.job.class] } } : {}),
        AccountRef: {
          value: accounts[bodyshop.md_responsibility_centers.taxes.federal_itc.accountdesc]
        }
      },

      Amount: Dinero({
        amount: Math.round(
          bill.billlines.reduce((acc, val) => {
            return acc + (val.applicable_taxes?.federal ? val.actual_cost * val.quantity || 0 : 0);
          }, 0) * 100
        )
      })
        .percentage(bill.federal_tax_rate)
        .toFormat(DineroQbFormat)
    });
  }

  let billQbo, VendorCredit;
  const billObject = {
    VendorRef: {
      value: vendor.Id
    },
    ...(vendor.TermRef &&
      !bill.is_credit_memo && {
        SalesTermRef: {
          value: vendor.TermRef.value
        }
      }),
    TxnDate: moment(bill.date)
      //.tz(bill.job.bodyshop.timezone)
      .format("YYYY-MM-DD"),
    ...(!bill.is_credit_memo &&
      bill.vendor.due_date && {
        DueDate: moment(bill.date)
          //.tz(bill.job.bodyshop.timezone)
          .add(bill.vendor.due_date, "days")
          .format("YYYY-MM-DD")
      }),
    DocNumber: bill.invoice_number,
    //...(bill.job.class ? { ClassRef: { Id: classes[bill.job.class] } } : {}),
    ...(!(
      bodyshop.accountingconfig?.qbo &&
      bodyshop.accountingconfig?.qbo_usa &&
      bodyshop.region_config.includes("CA_")
    )
      ? { GlobalTaxCalculation: "TaxExcluded" }
      : {}),
    ...(bodyshop.accountingconfig.qbo_departmentid?.trim() !== "" && {
      DepartmentRef: { value: bodyshop.accountingconfig.qbo_departmentid }
    }),
    PrivateNote: `RO ${bill.job.ro_number || ""}`,
    Line: lines
  };

  if (bill.is_credit_memo) {
    VendorCredit = billObject;
  } else {
    billQbo = billObject;
  }

  const logKey = bill.is_credit_memo ? "VendorCredit" : "billQbo";
  const logValue = bill.is_credit_memo ? VendorCredit : billQbo;

  logger.log("qbo-payable-objectlog", "DEBUG", req.user.email, bill.id, {
    [logKey]: logValue
  });
  try {
    const url = urlBuilder(qbo_realmId, bill.is_credit_memo ? "vendorcredit" : "bill");
    const result = await oauthClient.makeApiCall({
      url: url,
      method: "POST",
      headers: {
        "Content-Type": "application/json"
      },
      body: JSON.stringify(bill.is_credit_memo ? VendorCredit : billQbo)
    });
    logger.LogIntegrationCall({
      platform: "QBO",
      method: "POST",
      name: "InsertBill",
      billid: bill.id,
      status: result.status,
      bodyshopid: bill.job.shopid,
      email: req.user.email
    });
    logger.log("qbo-payables-insert", "DEBUG", req.user.email, null, {
      method: "InsertBill",
      call: url,
      postingObj: bill.is_credit_memo ? VendorCredit : billQbo,
      result: result.json
    });

    if (result.status >= 400) {
      throw new Error(JSON.stringify(result.json.Fault));
    }
    if (result.status === 200) return result?.json;
  } catch (error) {
    logger.log("qbo-payables-error", "DEBUG", req.user.email, bill.id, {
      method: "InsertBill",
      validationError: error.message,
      accountmeta: JSON.stringify({ accounts, taxCodes, classes }),
      stack: error.stack
    });
    throw error;
  }
}

// [
//   {
//     DetailType: "AccountBasedExpenseLineDetail",
//     Amount: 200.0,
//     Id: "1",
//     AccountBasedExpenseLineDetail: {
//       AccountRef: {
//         value: "7",
//       },
//     },
//   },
// ],

const generateBillLine = (
  billLine,
  accounts,
  jobClass,
  ioSalesTaxCodes,
  classes,
  taxCodes,
  costCenters,
  accountingconfig,
  region_config
) => {
  const account = costCenters.find((c) => c.name === billLine.cost_center);
  return {
    DetailType: "AccountBasedExpenseLineDetail",

    AccountBasedExpenseLineDetail: {
      ...(jobClass ? { ClassRef: { value: classes[jobClass] } } : {}),
      TaxCodeRef:
        accountingconfig.qbo && accountingconfig.qbo_usa && region_config.includes("CA_")
          ? {}
          : {
              value: taxCodes[findTaxCode(billLine.applicable_taxes, ioSalesTaxCodes)]
            },
      AccountRef: {
        value: accounts[account.accountname]
      }
    },

    Amount: Dinero({
      amount: Math.round(billLine.actual_cost * 100)
    })
      .multiply(billLine.quantity || 1)
      .toFormat(DineroQbFormat)
  };
};

async function QueryMetaData(oauthClient, qbo_realmId, req, bodyshopid) {
  const accounts = await oauthClient.makeApiCall({
    url: urlBuilder(
      qbo_realmId,
      "query",
      `select * From Account where AccountType in ('Cost of Goods Sold', 'Other Current Liability')`
    ),
    method: "POST",
    headers: {
      "Content-Type": "application/json"
    }
  });
  logger.LogIntegrationCall({
    platform: "QBO",
    method: "POST",
    name: "QueryAccountType",
    status: accounts.status,
    bodyshopid,
    email: req.user.email
  });

  const taxCodes = await oauthClient.makeApiCall({
    url: urlBuilder(qbo_realmId, "query", `select * From TaxCode`),
    method: "POST",
    headers: {
      "Content-Type": "application/json"
    }
  });
  logger.LogIntegrationCall({
    platform: "QBO",
    method: "POST",
    name: "QueryTaxCode",
    status: taxCodes.status,
    bodyshopid,
    email: req.user.email
  });
  const classes = await oauthClient.makeApiCall({
    url: urlBuilder(qbo_realmId, "query", `select * From Class`),
    method: "POST",
    headers: {
      "Content-Type": "application/json"
    }
  });
  logger.LogIntegrationCall({
    platform: "QBO",
    method: "POST",
    name: "QueryClasses",
    status: classes.status,
    bodyshopid,
    email: req.user.email
  });

  const taxCodeMapping = Object.fromEntries((taxCodes.json?.QueryResponse?.TaxCode || []).map((t) => [t.Name, t.Id]));

  const accountMapping = Object.fromEntries(
    (accounts.json?.QueryResponse?.Account || []).map((t) => [t.FullyQualifiedName, t.Id])
  );

  const classMapping = Object.fromEntries((classes.json?.QueryResponse?.Class || []).map((c) => [c.Name, c.Id]));

  return {
    accounts: accountMapping,
    taxCodes: taxCodeMapping,
    classes: classMapping
  };
}