@isoftdata/universal-object-htp-utility - npm Package Compare versions

Comparing version 3.19.1 to 3.20.0

tests/selectThenUpsertRows.test.ts

+import assert from 'assert'
+import { test } from 'node:test'
+import { selectThenUpsertRows } from '../shared.js'
+
+// Creates a fake DB connection whose SELECT returns a row (rowExists=true) or nothing (rowExists=false).
+// Captures every SQL call so we can assert on what queries were built.
+function makeMockConnection(rowExists: boolean) {
+    const calls: Array<{ sql: string, values: unknown[] }> = []
+    const connection = {
+        query: async (opts: { sql: string, values: unknown[] }) => {
+            calls.push(opts)
+            if (opts.sql.trimStart().toUpperCase().startsWith('SELECT')) {
+                return [rowExists ? [{ '1': 1 }] : [], []]
+            }
+            return [{ affectedRows: 1 }, []]
+        }
+    }
+    return { connection, calls }
+}
+
+// When the row does not exist in the DB, we expect: SELECT then INSERT
+test('inserts when the row does not exist', async () => {
+    const { connection, calls } = makeMockConnection(false)
+    const row = { productcode: 1, paymentid: 42, storeid: 5, date: '2024-01-01', void: 'False' }
+
+    const result = await selectThenUpsertRows(connection as any, 'payment', [row], ['productcode', 'paymentid', 'storeid'])
+
+    assert.equal(calls.length, 2, 'should make exactly 2 queries (SELECT + INSERT)')
+    assert.ok(calls[0].sql.trimStart().toUpperCase().startsWith('SELECT'), 'first query should be SELECT')
+    assert.ok(calls[1].sql.trimStart().toUpperCase().startsWith('INSERT'), 'second query should be INSERT')
+    assert.equal(result.affectedRows, 1)
+})
+
+// When the row already exists, we expect: SELECT then UPDATE
+test('updates when the row already exists', async () => {
+    const { connection, calls } = makeMockConnection(true)
+    const row = { productcode: 1, paymentid: 42, storeid: 5, date: '2024-06-01', void: 'True' }
+
+    const result = await selectThenUpsertRows(connection as any, 'payment', [row], ['productcode', 'paymentid', 'storeid'])
+
+    assert.equal(calls.length, 2, 'should make exactly 2 queries (SELECT + UPDATE)')
+    assert.ok(calls[0].sql.trimStart().toUpperCase().startsWith('SELECT'), 'first query should be SELECT')
+    assert.ok(calls[1].sql.trimStart().toUpperCase().startsWith('UPDATE'), 'second query should be UPDATE')
+    assert.equal(result.affectedRows, 1)
+})
+
+// UPDATE should only set the non-key columns, and WHERE should use the key columns
+test('UPDATE sets only non-key columns and WHERE uses key columns', async () => {
+    const { connection, calls } = makeMockConnection(true)
+    const row = { productcode: 1, paymentid: 42, storeid: 5, date: '2024-06-01', void: 'True' }
+
+    await selectThenUpsertRows(connection as any, 'payment', [row], ['productcode', 'paymentid', 'storeid'])
+
+    const updateCall = calls[1]
+    const updateValues = updateCall.values as unknown[]
+    // values layout: [table, ...setValues(col,val pairs), ...whereValues(col,val pairs)]
+    // set columns are the non-key ones: date, void
+    assert.ok(updateValues.includes('date'), 'UPDATE should set non-key column "date"')
+    assert.ok(updateValues.includes('void'), 'UPDATE should set non-key column "void"')
+    // key columns should appear in WHERE but NOT in SET
+    const setSection = updateValues.slice(0, updateValues.indexOf('productcode'))
+    assert.ok(!setSection.includes('paymentid'), 'key column "paymentid" should not be in SET section')
+})
+
+// For multiple rows, each row gets its own SELECT + INSERT/UPDATE pair
+test('handles multiple rows independently', async () => {
+    const { connection, calls } = makeMockConnection(false)
+    const rows = [
+        { productcode: 1, paymentid: 1, storeid: 1, date: '2024-01-01' },
+        { productcode: 1, paymentid: 2, storeid: 1, date: '2024-02-01' },
+    ]
+
+    const result = await selectThenUpsertRows(connection as any, 'payment', rows, ['productcode', 'paymentid', 'storeid'])
+
+    assert.equal(calls.length, 4, 'should make 4 queries total (SELECT+INSERT per row)')
+    assert.equal(result.affectedRows, 2)
+})

tests/tsconfig.json

+{
+  "extends": "../tsconfig.json",
+  "compilerOptions": {
+    "types": ["node"],
+    "noEmit": true,
+    "rootDir": ".."
+  },
+  "include": ["./**/*.ts"],
+  "exclude": ["../node_modules", "../dist"]
+}

dist/index.d.ts

@@ -17,8 +17,8 @@ import { deleteCategory, upsertCategoryRows } from './category.js';
 import { deleteSellPriceClassRow, upsertSellPriceClassRows } from './sellPriceClass.js';
-import { upsertPaymentRows, deletePaymentRow } from './payment.js';
-import { upsertPaymentLineRows, deletePaymentLineRow } from './paymentLine.js';
+import { insertPaymentRows, upsertPaymentRows, deletePaymentRow } from './payment.js';
+import { insertPaymentLineRows, upsertPaymentLineRows, deletePaymentLineRow } from './paymentLine.js';
 import { upsertSalesOrderRows, deleteSalesOrderRow } from './salesOrder.js';
-import { upsertSalesOrderAdjustmentRows, deleteSalesOrderAdjustmentRow } from './salesOrderAdjustment.js';
+import { insertSalesOrderAdjustmentRows, upsertSalesOrderAdjustmentRows, deleteSalesOrderAdjustmentRow } from './salesOrderAdjustment.js';
 import { upsertSalesOrderLineRows, deleteSalesOrderLineRow } from './salesOrderLineRow.js';
-export { deleteCategory, deleteCompanyInfo, deleteCustomerAddressRow, deleteCustomerOptionRow, deleteCustomerOptionValueRow, deleteCustomerPriceContractRow, deleteCustomerRow, deleteInventoryData, deleteInventoryFileData, deleteInventoryFileRow, deleteInventoryOptionListRow, deleteInventoryOptionRow, deleteInventoryOptionRows, deleteInventorySourceData, deleteInventorySourceRow, deleteMnfcrmodModelRow, deletePartUseRow, deletePaymentRow, deletePaymentLineRow, deleteSalesOrderAdjustmentRow, deleteSalesOrderLineRow, deleteSalesOrderRow, deleteSellPriceClassRow, deleteVehicle, getPartUseInfoQuery, insertInventoryData, insertInventoryFileData, insertInventoryOptionListRows, insertInventoryOptionRows, insertMnfcrmodModelRows, upsertCategoryRows, upsertCompanyInfoRows, upsertCustomerAddressRows, upsertCustomerOptionRows, upsertCustomerOptionValueRows, upsertCustomerPriceContractRows, upsertCustomerRows, upsertInventoryFileData, upsertInventoryOptionListRows, upsertInventoryOptionRows, upsertInventoryRows, upsertInventorySourceRows, upsertMnfcrmodModelRows, upsertPartUseRows, upsertPaymentRows, upsertPaymentLineRows, upsertSalesOrderAdjustmentRows, upsertSalesOrderLineRows, upsertSalesOrderRows, upsertSellPriceClassRows, upsertVehicleRows };
+export { deleteCategory, deleteCompanyInfo, deleteCustomerAddressRow, deleteCustomerOptionRow, deleteCustomerOptionValueRow, deleteCustomerPriceContractRow, deleteCustomerRow, deleteInventoryData, deleteInventoryFileData, deleteInventoryFileRow, deleteInventoryOptionListRow, deleteInventoryOptionRow, deleteInventoryOptionRows, deleteInventorySourceData, deleteInventorySourceRow, deleteMnfcrmodModelRow, deletePartUseRow, deletePaymentRow, deletePaymentLineRow, deleteSalesOrderAdjustmentRow, deleteSalesOrderLineRow, deleteSalesOrderRow, deleteSellPriceClassRow, deleteVehicle, getPartUseInfoQuery, insertInventoryData, insertInventoryFileData, insertInventoryOptionListRows, insertInventoryOptionRows, insertMnfcrmodModelRows, insertPaymentLineRows, insertPaymentRows, insertSalesOrderAdjustmentRows, upsertCategoryRows, upsertCompanyInfoRows, upsertCustomerAddressRows, upsertCustomerOptionRows, upsertCustomerOptionValueRows, upsertCustomerPriceContractRows, upsertCustomerRows, upsertInventoryFileData, upsertInventoryOptionListRows, upsertInventoryOptionRows, upsertInventoryRows, upsertInventorySourceRows, upsertMnfcrmodModelRows, upsertPartUseRows, upsertPaymentRows, upsertPaymentLineRows, upsertSalesOrderAdjustmentRows, upsertSalesOrderLineRows, upsertSalesOrderRows, upsertSellPriceClassRows, upsertVehicleRows };
 export type { HtpPaymentRow, HtpPaymentLineRow, HtpSalesOrderAdjustmentRow, HtpSalesOrderLineRow, HtpSalesOrderRow, PaymentRow, PaymentLineRow, SalesOrderAdjustmentRow, SalesOrderLineRow, SalesOrderRow, } from './types.js';

dist/index.js

@@ -17,7 +17,7 @@ import { deleteCategory, upsertCategoryRows } from './category.js';
 import { deleteSellPriceClassRow, upsertSellPriceClassRows } from './sellPriceClass.js';
-import { upsertPaymentRows, deletePaymentRow } from './payment.js';
-import { upsertPaymentLineRows, deletePaymentLineRow } from './paymentLine.js';
+import { insertPaymentRows, upsertPaymentRows, deletePaymentRow } from './payment.js';
+import { insertPaymentLineRows, upsertPaymentLineRows, deletePaymentLineRow } from './paymentLine.js';
 import { upsertSalesOrderRows, deleteSalesOrderRow } from './salesOrder.js';
-import { upsertSalesOrderAdjustmentRows, deleteSalesOrderAdjustmentRow } from './salesOrderAdjustment.js';
+import { insertSalesOrderAdjustmentRows, upsertSalesOrderAdjustmentRows, deleteSalesOrderAdjustmentRow } from './salesOrderAdjustment.js';
 import { upsertSalesOrderLineRows, deleteSalesOrderLineRow } from './salesOrderLineRow.js';
-export { deleteCategory, deleteCompanyInfo, deleteCustomerAddressRow, deleteCustomerOptionRow, deleteCustomerOptionValueRow, deleteCustomerPriceContractRow, deleteCustomerRow, deleteInventoryData, deleteInventoryFileData, deleteInventoryFileRow, deleteInventoryOptionListRow, deleteInventoryOptionRow, deleteInventoryOptionRows, deleteInventorySourceData, deleteInventorySourceRow, deleteMnfcrmodModelRow, deletePartUseRow, deletePaymentRow, deletePaymentLineRow, deleteSalesOrderAdjustmentRow, deleteSalesOrderLineRow, deleteSalesOrderRow, deleteSellPriceClassRow, deleteVehicle, getPartUseInfoQuery, insertInventoryData, insertInventoryFileData, insertInventoryOptionListRows, insertInventoryOptionRows, insertMnfcrmodModelRows, upsertCategoryRows, upsertCompanyInfoRows, upsertCustomerAddressRows, upsertCustomerOptionRows, upsertCustomerOptionValueRows, upsertCustomerPriceContractRows, upsertCustomerRows, upsertInventoryFileData, upsertInventoryOptionListRows, upsertInventoryOptionRows, upsertInventoryRows, upsertInventorySourceRows, upsertMnfcrmodModelRows, upsertPartUseRows, upsertPaymentRows, upsertPaymentLineRows, upsertSalesOrderAdjustmentRows, upsertSalesOrderLineRows, upsertSalesOrderRows, upsertSellPriceClassRows, upsertVehicleRows };
+export { deleteCategory, deleteCompanyInfo, deleteCustomerAddressRow, deleteCustomerOptionRow, deleteCustomerOptionValueRow, deleteCustomerPriceContractRow, deleteCustomerRow, deleteInventoryData, deleteInventoryFileData, deleteInventoryFileRow, deleteInventoryOptionListRow, deleteInventoryOptionRow, deleteInventoryOptionRows, deleteInventorySourceData, deleteInventorySourceRow, deleteMnfcrmodModelRow, deletePartUseRow, deletePaymentRow, deletePaymentLineRow, deleteSalesOrderAdjustmentRow, deleteSalesOrderLineRow, deleteSalesOrderRow, deleteSellPriceClassRow, deleteVehicle, getPartUseInfoQuery, insertInventoryData, insertInventoryFileData, insertInventoryOptionListRows, insertInventoryOptionRows, insertMnfcrmodModelRows, insertPaymentLineRows, insertPaymentRows, insertSalesOrderAdjustmentRows, upsertCategoryRows, upsertCompanyInfoRows, upsertCustomerAddressRows, upsertCustomerOptionRows, upsertCustomerOptionValueRows, upsertCustomerPriceContractRows, upsertCustomerRows, upsertInventoryFileData, upsertInventoryOptionListRows, upsertInventoryOptionRows, upsertInventoryRows, upsertInventorySourceRows, upsertMnfcrmodModelRows, upsertPartUseRows, upsertPaymentRows, upsertPaymentLineRows, upsertSalesOrderAdjustmentRows, upsertSalesOrderLineRows, upsertSalesOrderRows, upsertSellPriceClassRows, upsertVehicleRows };

dist/payment.d.ts

@@ -5,2 +5,6 @@ export declare function upsertPaymentRows(connection: any, paymentArr: any, productCode: any): Promise<{
 }>;
+export declare function insertPaymentRows(connection: any, paymentArr: any, productCode: any): Promise<{
+    success: boolean;
+    data: any;
+}>;
 export declare function deletePaymentRow(connection: any, productCode: any, storeId: any, paymentId: any): Promise<{
@@ -7,0 +11,0 @@ success: boolean;

dist/payment.js

@@ -1,2 +0,2 @@
-import { multiRowUpsertQuery, write } from './shared.js';
+import { selectThenUpsertRows, multiRowInsertQuery, write } from './shared.js';
 function convertPaymentRow(row, productCode) {
@@ -9,3 +9,3 @@ return {
         documentnumber: row.documentNumber,
-        dateentered: row.dateEntered,
+        dateentered: (row.dateEntered && !isNaN(Date.parse(row.dateEntered))) ? row.dateEntered : new Date().toISOString().slice(0, 19).replace('T', ' '),
         date: row.date,
@@ -20,3 +20,3 @@ void: row.void,
         const convertedRows = paymentArr.map(row => convertPaymentRow(row, productCode));
-        const multiRowUpsertResponse = await multiRowUpsertQuery(connection, 'payment', convertedRows);
+        const multiRowUpsertResponse = await selectThenUpsertRows(connection, 'payment', convertedRows, ['productcode', 'paymentid', 'storeid']);
         responses.push(`payment affected rows: ${multiRowUpsertResponse.affectedRows}`);
@@ -35,2 +35,22 @@ return {
 }
+export async function insertPaymentRows(connection, paymentArr, productCode) {
+    const responses = [];
+    try {
+        const convertedRows = paymentArr.map(row => convertPaymentRow(row, productCode));
+        const columns = Object.keys(convertedRows[0]);
+        const values = convertedRows.map(row => Object.values(row));
+        const insertResponse = await multiRowInsertQuery(connection, 'payment', columns, values);
+        responses.push(`payment affected rows: ${insertResponse.affectedRows}`);
+        return {
+            success: true,
+            data: responses
+        };
+    }
+    catch (err) {
+        return {
+            success: false,
+            data: err
+        };
+    }
+}
 export async function deletePaymentRow(connection, productCode, storeId, paymentId) {
@@ -37,0 +57,0 @@ const responses = [];

dist/paymentLine.d.ts

@@ -5,2 +5,6 @@ export declare function upsertPaymentLineRows(connection: any, paymentLineArr: any, productCode: any): Promise<{
 }>;
+export declare function insertPaymentLineRows(connection: any, paymentLineArr: any, productCode: any): Promise<{
+    success: boolean;
+    data: any;
+}>;
 export declare function deletePaymentLineRow(connection: any, productCode: any, storeId: any, paymentId: any, paymentLineId: any): Promise<{
@@ -7,0 +11,0 @@ success: boolean;

dist/paymentLine.js

@@ -1,2 +0,2 @@
-import { multiRowUpsertQuery, write } from './shared.js';
+import { selectThenUpsertRows, multiRowInsertQuery, write } from './shared.js';
 function convertPaymentLineRow(row, productCode) {
@@ -17,3 +17,3 @@ return {
         const convertedRows = paymentLineArr.map(row => convertPaymentLineRow(row, productCode));
-        const multiRowUpsertResponse = await multiRowUpsertQuery(connection, 'paymentline', convertedRows);
+        const multiRowUpsertResponse = await selectThenUpsertRows(connection, 'paymentline', convertedRows, ['productcode', 'storeid', 'paymentid', 'paymentlineid']);
         responses.push(`paymentline affected rows: ${multiRowUpsertResponse.affectedRows}`);
@@ -32,2 +32,22 @@ return {
 }
+export async function insertPaymentLineRows(connection, paymentLineArr, productCode) {
+    const responses = [];
+    try {
+        const convertedRows = paymentLineArr.map(row => convertPaymentLineRow(row, productCode));
+        const columns = Object.keys(convertedRows[0]);
+        const values = convertedRows.map(row => Object.values(row));
+        const insertResponse = await multiRowInsertQuery(connection, 'paymentline', columns, values);
+        responses.push(`paymentline affected rows: ${insertResponse.affectedRows}`);
+        return {
+            success: true,
+            data: responses
+        };
+    }
+    catch (err) {
+        return {
+            success: false,
+            data: err
+        };
+    }
+}
 export async function deletePaymentLineRow(connection, productCode, storeId, paymentId, paymentLineId) {
@@ -34,0 +54,0 @@ const responses = [];

dist/salesOrder.js

@@ -49,4 +49,4 @@ import { multiRowUpsertQuery, write } from './shared.js';
         websaleorigin: row.webSaleOrigin,
-        vendorapprovalstatus: row.vendorApprovalStatus,
-        customerapprovalstatus: row.customerApprovalStatus,
+        vendor_approval_status: row.vendorApprovalStatus,
+        customer_approval_status: row.customerApprovalStatus,
     };
@@ -53,0 +53,0 @@ }

dist/salesOrderAdjustment.d.ts

@@ -5,2 +5,6 @@ export declare function upsertSalesOrderAdjustmentRows(connection: any, salesOrderAdjustmentArr: any, productCode: any): Promise<{
 }>;
+export declare function insertSalesOrderAdjustmentRows(connection: any, salesOrderAdjustmentArr: any, productCode: any): Promise<{
+    success: boolean;
+    data: any;
+}>;
 export declare function deleteSalesOrderAdjustmentRow(connection: any, productCode: any, salesOrderId: any, salesOrderLineId: any, salesOrderStoreId: any, salesOrderAdjustmentId: any): Promise<{
@@ -7,0 +11,0 @@ success: boolean;

dist/salesOrderAdjustment.js

@@ -1,2 +0,2 @@
-import { multiRowUpsertQuery, write } from './shared.js';
+import { selectThenUpsertRows, multiRowInsertQuery, write } from './shared.js';
 function convertSalesOrderAdjustmentRow(row, productCode) {
@@ -22,3 +22,3 @@ return {
         const convertedRows = salesOrderAdjustmentArr.map(row => convertSalesOrderAdjustmentRow(row, productCode));
-        const multiRowUpsertResponse = await multiRowUpsertQuery(connection, 'salesorderadjustment', convertedRows);
+        const multiRowUpsertResponse = await selectThenUpsertRows(connection, 'salesorderadjustment', convertedRows, ['productcode', 'salesorderid', 'salesorderlineid', 'salesorderstoreid', 'salesorderadjustmentid']);
         responses.push(`salesorderadjustment affected rows: ${multiRowUpsertResponse.affectedRows}`);
@@ -37,2 +37,22 @@ return {
 }
+export async function insertSalesOrderAdjustmentRows(connection, salesOrderAdjustmentArr, productCode) {
+    const responses = [];
+    try {
+        const convertedRows = salesOrderAdjustmentArr.map(row => convertSalesOrderAdjustmentRow(row, productCode));
+        const columns = Object.keys(convertedRows[0]);
+        const values = convertedRows.map(row => Object.values(row));
+        const insertResponse = await multiRowInsertQuery(connection, 'salesorderadjustment', columns, values);
+        responses.push(`salesorderadjustment affected rows: ${insertResponse.affectedRows}`);
+        return {
+            success: true,
+            data: responses
+        };
+    }
+    catch (err) {
+        return {
+            success: false,
+            data: err
+        };
+    }
+}
 export async function deleteSalesOrderAdjustmentRow(connection, productCode, salesOrderId, salesOrderLineId, salesOrderStoreId, salesOrderAdjustmentId) {
@@ -39,0 +59,0 @@ const responses = [];

dist/salesOrderLineRow.js

@@ -52,5 +52,5 @@ import { multiRowUpsertQuery, write } from './shared.js';
         daystoreturncore: row.daysToReturnCore,
-        returncodename: row.returnCodeName,
+        returncodename: row.returnCodeName ?? '',
         deliverydate: row.deliveryDate,
-        dateentered: row.dateEntered,
+        dateentered: (row.dateEntered && !isNaN(Date.parse(row.dateEntered))) ? row.dateEntered : new Date().toISOString().slice(0, 19).replace('T', ' '),
         websalelineid: row.webSaleLineId,
@@ -57,0 +57,0 @@ corestatus: row.coreStatus,

dist/shared.d.ts

@@ -17,2 +17,3 @@ import { Pool, Connection, QueryOptions, ResultSetHeader } from 'mysql2/promise';
 export declare function multiRowUpsertQuery(htpConnection: Pool | Connection, table: string, objectArray: object[]): Promise<ResultSetHeader>;
+export declare function selectThenUpsertRows(connection: Pool | Connection, table: string, rows: object[], keyColumns: string[]): Promise<ResultSetHeader>;
 export declare function multiRowInsertQuery(connection: Pool | Connection, table: string, columns: string[], columnValues: unknown[][]): Promise<ResultSetHeader>;

dist/shared.js

@@ -54,2 +54,34 @@ /**
 }
+// TODO: some day we may have to consider parallelizing this
+export async function selectThenUpsertRows(connection, table, rows, keyColumns) {
+    let totalAffected = 0;
+    for (const row of rows) {
+        const rowEntries = Object.entries(row);
+        const keyEntries = rowEntries.filter(([k]) => keyColumns.includes(k));
+        const valueEntries = rowEntries.filter(([k]) => !keyColumns.includes(k));
+        const whereClause = keyEntries.map(() => `?? = ?`).join(' AND ');
+        const whereValues = keyEntries.flatMap(([k, v]) => [k, v]);
+        const existing = await query(connection, {
+            sql: `SELECT 1 FROM ?? WHERE ${whereClause} LIMIT 1`,
+            values: [table, ...whereValues],
+        });
+        if (existing.length > 0) {
+            const setClause = valueEntries.map(() => `?? = ?`).join(', ');
+            const setValues = valueEntries.flatMap(([k, v]) => [k, v]);
+            const result = await write(connection, {
+                sql: `UPDATE ?? SET ${setClause} WHERE ${whereClause}`,
+                values: [table, ...setValues, ...whereValues],
+            });
+            totalAffected += result.affectedRows;
+        }
+        else {
+            const result = await write(connection, {
+                sql: `INSERT INTO ?? SET ?`,
+                values: [table, row],
+            });
+            totalAffected += result.affectedRows;
+        }
+    }
+    return { affectedRows: totalAffected };
+}
 export async function multiRowInsertQuery(connection, table, columns, columnValues) {
@@ -56,0 +88,0 @@ const queryOptions = {

dist/types.d.ts

@@ -79,4 +79,6 @@ type LowercaseKeys<T> = {
 }
-export type HtpSalesOrderRow = LowercaseKeys<SalesOrderRow> & {
+export type HtpSalesOrderRow = Omit<LowercaseKeys<SalesOrderRow>, 'vendorapprovalstatus' | 'customerapprovalstatus'> & {
     productcode: number;
+    vendor_approval_status: SalesOrderRow['vendorApprovalStatus'];
+    customer_approval_status: SalesOrderRow['customerApprovalStatus'];
 };
@@ -83,0 +85,0 @@ export interface SalesOrderAdjustmentRow {

index.ts

@@ -35,6 +35,6 @@ import { deleteCategory, upsertCategoryRows } from './category.js'
 import { deleteSellPriceClassRow, upsertSellPriceClassRows } from './sellPriceClass.js'
-import { upsertPaymentRows, deletePaymentRow } from './payment.js'
-import { upsertPaymentLineRows, deletePaymentLineRow } from './paymentLine.js'
+import { insertPaymentRows, upsertPaymentRows, deletePaymentRow } from './payment.js'
+import { insertPaymentLineRows, upsertPaymentLineRows, deletePaymentLineRow } from './paymentLine.js'
 import { upsertSalesOrderRows, deleteSalesOrderRow } from './salesOrder.js'
-import { upsertSalesOrderAdjustmentRows, deleteSalesOrderAdjustmentRow } from './salesOrderAdjustment.js'
+import { insertSalesOrderAdjustmentRows, upsertSalesOrderAdjustmentRows, deleteSalesOrderAdjustmentRow } from './salesOrderAdjustment.js'
 import { upsertSalesOrderLineRows, deleteSalesOrderLineRow } from './salesOrderLineRow.js'
@@ -72,2 +72,5 @@ export {
 	insertMnfcrmodModelRows,
+	insertPaymentLineRows,
+	insertPaymentRows,
+	insertSalesOrderAdjustmentRows,
 	upsertCategoryRows,
@@ -74,0 +77,0 @@ upsertCompanyInfoRows,

package.json

 {
   "name": "@isoftdata/universal-object-htp-utility",
-  "version": "3.19.1",
+  "version": "3.20.0",
   "description": "Functions to convert universal objects to htp schema",
@@ -5,0 +5,0 @@ "main": "dist/index.js",

payment.ts

@@ -1,2 +0,2 @@
-import { multiRowUpsertQuery, write } from './shared.js'
+import { selectThenUpsertRows, multiRowInsertQuery, write } from './shared.js'
 import { PaymentRow, HtpPaymentRow } from './types.js'
@@ -11,3 +11,3 @@
         documentnumber: row.documentNumber,
-        dateentered: row.dateEntered,
+        dateentered: (row.dateEntered && !isNaN(Date.parse(row.dateEntered))) ? row.dateEntered : new Date().toISOString().slice(0, 19).replace('T', ' '),
         date: row.date,
@@ -23,3 +23,3 @@ void: row.void,
         const convertedRows = paymentArr.map(row => convertPaymentRow(row, productCode))
-        const multiRowUpsertResponse = await multiRowUpsertQuery(connection, 'payment', convertedRows)
+        const multiRowUpsertResponse = await selectThenUpsertRows(connection, 'payment', convertedRows, ['productcode', 'paymentid', 'storeid'])
         responses.push(`payment affected rows: ${multiRowUpsertResponse.affectedRows}`)
@@ -38,2 +38,22 @@ return {
 
+export async function insertPaymentRows(connection, paymentArr, productCode) {
+    const responses = []
+    try {
+        const convertedRows = paymentArr.map(row => convertPaymentRow(row, productCode))
+        const columns = Object.keys(convertedRows[0])
+        const values = convertedRows.map(row => Object.values(row))
+        const insertResponse = await multiRowInsertQuery(connection, 'payment', columns, values)
+        responses.push(`payment affected rows: ${insertResponse.affectedRows}`)
+        return {
+            success: true,
+            data: responses
+        }
+    } catch (err) {
+        return {
+            success: false,
+            data: err
+        }
+    }
+}
+
 export async function deletePaymentRow(connection, productCode, storeId, paymentId) {
@@ -40,0 +60,0 @@ const responses = []

paymentLine.ts

@@ -1,2 +0,2 @@
-import { multiRowUpsertQuery, write } from './shared.js'
+import { selectThenUpsertRows, multiRowInsertQuery, write } from './shared.js'
 import { PaymentLineRow, HtpPaymentLineRow } from './types.js'
@@ -20,3 +20,3 @@
         const convertedRows = paymentLineArr.map(row => convertPaymentLineRow(row, productCode))
-        const multiRowUpsertResponse = await multiRowUpsertQuery(connection, 'paymentline', convertedRows)
+        const multiRowUpsertResponse = await selectThenUpsertRows(connection, 'paymentline', convertedRows, ['productcode', 'storeid', 'paymentid', 'paymentlineid'])
         responses.push(`paymentline affected rows: ${multiRowUpsertResponse.affectedRows}`)
@@ -35,2 +35,22 @@ return {
 
+export async function insertPaymentLineRows(connection, paymentLineArr, productCode) {
+    const responses = []
+    try {
+        const convertedRows = paymentLineArr.map(row => convertPaymentLineRow(row, productCode))
+        const columns = Object.keys(convertedRows[0])
+        const values = convertedRows.map(row => Object.values(row))
+        const insertResponse = await multiRowInsertQuery(connection, 'paymentline', columns, values)
+        responses.push(`paymentline affected rows: ${insertResponse.affectedRows}`)
+        return {
+            success: true,
+            data: responses
+        }
+    } catch (err) {
+        return {
+            success: false,
+            data: err
+        }
+    }
+}
+
 export async function deletePaymentLineRow(connection, productCode, storeId, paymentId, paymentLineId) {
@@ -37,0 +57,0 @@ const responses = []

salesOrder.ts

@@ -51,4 +51,4 @@ import { multiRowUpsertQuery, write } from './shared.js'
         websaleorigin: row.webSaleOrigin,
-        vendorapprovalstatus: row.vendorApprovalStatus,
-        customerapprovalstatus: row.customerApprovalStatus,
+        vendor_approval_status: row.vendorApprovalStatus,
+        customer_approval_status: row.customerApprovalStatus,
     }
@@ -55,0 +55,0 @@ }

salesOrderAdjustment.ts

@@ -1,2 +0,2 @@
-import { multiRowUpsertQuery, write } from './shared.js'
+import { selectThenUpsertRows, multiRowInsertQuery, write } from './shared.js'
 import { SalesOrderAdjustmentRow, HtpSalesOrderAdjustmentRow } from './types.js'
@@ -25,3 +25,3 @@
         const convertedRows = salesOrderAdjustmentArr.map(row => convertSalesOrderAdjustmentRow(row, productCode))
-        const multiRowUpsertResponse = await multiRowUpsertQuery(connection, 'salesorderadjustment', convertedRows)
+        const multiRowUpsertResponse = await selectThenUpsertRows(connection, 'salesorderadjustment', convertedRows, ['productcode', 'salesorderid', 'salesorderlineid', 'salesorderstoreid', 'salesorderadjustmentid'])
         responses.push(`salesorderadjustment affected rows: ${multiRowUpsertResponse.affectedRows}`)
@@ -40,2 +40,22 @@ return {
 
+export async function insertSalesOrderAdjustmentRows(connection, salesOrderAdjustmentArr, productCode) {
+    const responses = []
+    try {
+        const convertedRows = salesOrderAdjustmentArr.map(row => convertSalesOrderAdjustmentRow(row, productCode))
+        const columns = Object.keys(convertedRows[0])
+        const values = convertedRows.map(row => Object.values(row))
+        const insertResponse = await multiRowInsertQuery(connection, 'salesorderadjustment', columns, values)
+        responses.push(`salesorderadjustment affected rows: ${insertResponse.affectedRows}`)
+        return {
+            success: true,
+            data: responses
+        }
+    } catch (err) {
+        return {
+            success: false,
+            data: err
+        }
+    }
+}
+
 export async function deleteSalesOrderAdjustmentRow(connection, productCode, salesOrderId, salesOrderLineId, salesOrderStoreId, salesOrderAdjustmentId ) {
@@ -42,0 +62,0 @@ const responses = []

salesOrderLineRow.ts

@@ -54,5 +54,5 @@ import { multiRowUpsertQuery, write } from './shared.js'
         daystoreturncore: row.daysToReturnCore,
-        returncodename: row.returnCodeName,
+        returncodename: row.returnCodeName ?? '',
         deliverydate: row.deliveryDate,
-        dateentered: row.dateEntered,
+        dateentered: (row.dateEntered && !isNaN(Date.parse(row.dateEntered))) ? row.dateEntered : new Date().toISOString().slice(0, 19).replace('T', ' '),
         websalelineid: row.webSaleLineId,
@@ -59,0 +59,0 @@ corestatus: row.coreStatus,

shared.ts

@@ -61,2 +61,37 @@ import { Pool, Connection, QueryOptions, ResultSetHeader } from 'mysql2/promise'
 
+// TODO: some day we may have to consider parallelizing this
+export async function selectThenUpsertRows(connection: Pool | Connection, table: string, rows: object[], keyColumns: string[]): Promise<ResultSetHeader> {
+	let totalAffected = 0
+	for (const row of rows) {
+		const rowEntries = Object.entries(row)
+		const keyEntries = rowEntries.filter(([k]) => keyColumns.includes(k))
+		const valueEntries = rowEntries.filter(([k]) => !keyColumns.includes(k))
+
+		const whereClause = keyEntries.map(() => `?? = ?`).join(' AND ')
+		const whereValues = keyEntries.flatMap(([k, v]) => [k, v])
+
+		const existing = await query(connection, {
+			sql: `SELECT 1 FROM ?? WHERE ${whereClause} LIMIT 1`,
+			values: [table, ...whereValues],
+		})
+
+		if (existing.length > 0) {
+			const setClause = valueEntries.map(() => `?? = ?`).join(', ')
+			const setValues = valueEntries.flatMap(([k, v]) => [k, v])
+			const result = await write(connection, {
+				sql: `UPDATE ?? SET ${setClause} WHERE ${whereClause}`,
+				values: [table, ...setValues, ...whereValues],
+			})
+			totalAffected += result.affectedRows
+		} else {
+			const result = await write(connection, {
+				sql: `INSERT INTO ?? SET ?`,
+				values: [table, row],
+			})
+			totalAffected += result.affectedRows
+		}
+	}
+	return { affectedRows: totalAffected } as ResultSetHeader
+}
+
 export async function multiRowInsertQuery(connection: Pool | Connection, table: string, columns: string[], columnValues: unknown[][]): Promise<ResultSetHeader> {
@@ -63,0 +98,0 @@ const queryOptions = {

types.ts

@@ -83,3 +83,7 @@ // Many of the simpler objects will be converted to their lowercase equivalents with few changes,
 
-export type HtpSalesOrderRow = LowercaseKeys<SalesOrderRow> & { productcode: number }
+export type HtpSalesOrderRow = Omit<LowercaseKeys<SalesOrderRow>, 'vendorapprovalstatus' | 'customerapprovalstatus'> & {
+    productcode: number
+    vendor_approval_status: SalesOrderRow['vendorApprovalStatus']
+    customer_approval_status: SalesOrderRow['customerApprovalStatus']
+}
 
@@ -86,0 +90,0 @@ export interface SalesOrderAdjustmentRow {

New alerts

URL strings

Supply chain risk

Package contains fragments of external URLs or IP addresses, which the package may be accessing at runtime.

Fixed alerts

URL strings

Supply chain risk

Package contains fragments of external URLs or IP addresses, which the package may be accessing at runtime.

Improved metrics

Total package byte prevSize

333570

4.38%

Number of package files

93

2.2%

Lines of code

7515

3.81%

No dependency changes