317 lines
9.3 KiB
TypeScript
317 lines
9.3 KiB
TypeScript
/**
|
|
* each endpoint will be something like
|
|
* /api/datamart/{name}?{criteria}
|
|
*
|
|
* when getting the current queries we will need to map through the available queries we currently have and send back.
|
|
* example
|
|
*{
|
|
* "name": "getopenorders",
|
|
* "endpoint": "/api/datamart/getopenorders",
|
|
* "description": "Returns open orders based on day count sent over, sDay 15 days in the past eDay 5 days in the future, can be left empty for this default days",
|
|
* "options": "sDay,eDay"
|
|
* },
|
|
*
|
|
* when a criteria is password over we will handle it by counting how many were passed up to 3 then deal with each one respectively
|
|
*/
|
|
|
|
import { and, between, inArray, notInArray } from "drizzle-orm";
|
|
import { db } from "../db/db.controller.js";
|
|
import { invHistoricalData } from "../db/schema/historicalInv.schema.js";
|
|
import { prodQuery } from "../prodSql/prodSqlQuery.controller.js";
|
|
import {
|
|
type SqlQuery,
|
|
sqlQuerySelector,
|
|
} from "../prodSql/prodSqlQuerySelector.utils.js";
|
|
import { returnFunc } from "../utils/returnHelper.utils.js";
|
|
import { tryCatch } from "../utils/trycatch.utils.js";
|
|
import { datamartData } from "./datamartData.utlis.js";
|
|
|
|
type Data = {
|
|
name: string;
|
|
options: any;
|
|
optionsRequired?: boolean;
|
|
howManyOptionsRequired?: number;
|
|
};
|
|
|
|
const lstDbRun = async (data: Data) => {
|
|
if (data.options) {
|
|
if (data.name === "psiInventory") {
|
|
const ids = data.options.articles.split(",").map((id: any) => id.trim());
|
|
const whse = data.options.whseToInclude
|
|
? data.options.whseToInclude
|
|
.split(",")
|
|
.map((w: any) => w.trim())
|
|
.filter(Boolean)
|
|
: [];
|
|
|
|
const locations = data.options.exludeLanes
|
|
? data.options.exludeLanes
|
|
.split(",")
|
|
.map((l: any) => l.trim())
|
|
.filter(Boolean)
|
|
: [];
|
|
|
|
const conditions = [
|
|
inArray(invHistoricalData.article, ids),
|
|
between(
|
|
invHistoricalData.histDate,
|
|
data.options.startDate,
|
|
data.options.endDate,
|
|
),
|
|
];
|
|
|
|
// only add the warehouse condition if there are any whse values
|
|
if (whse.length > 0) {
|
|
conditions.push(inArray(invHistoricalData.whseId, whse));
|
|
}
|
|
|
|
// locations we dont want in the system
|
|
if (locations.length > 0) {
|
|
conditions.push(notInArray(invHistoricalData.location, locations));
|
|
}
|
|
|
|
return await db
|
|
.select()
|
|
.from(invHistoricalData)
|
|
.where(and(...conditions));
|
|
}
|
|
}
|
|
return [];
|
|
};
|
|
export const runDatamartQuery = async (data: Data) => {
|
|
// search the query db for the query by name
|
|
const considerLstDBRuns = ["psiInventory"];
|
|
|
|
if (considerLstDBRuns.includes(data.name)) {
|
|
const lstDB = await lstDbRun(data);
|
|
|
|
return returnFunc({
|
|
success: true,
|
|
level: "info",
|
|
module: "datamart",
|
|
subModule: "lstDBrn",
|
|
message: `Data for: ${data.name}`,
|
|
data: lstDB,
|
|
notify: false,
|
|
});
|
|
}
|
|
|
|
const featureQ = sqlQuerySelector(`featureCheck`) as SqlQuery;
|
|
|
|
const { data: fd, error: fe } = await tryCatch(
|
|
prodQuery(featureQ.query, `Running feature check`),
|
|
);
|
|
|
|
if (fe) {
|
|
return returnFunc({
|
|
success: false,
|
|
level: "error",
|
|
module: "datamart",
|
|
subModule: "query",
|
|
message: `feature check failed`,
|
|
data: fe as any,
|
|
notify: false,
|
|
});
|
|
}
|
|
|
|
// for queries that will need to be ran on legacy until we get the plant updated need to go in here
|
|
const doubleQueries = ["inventory"];
|
|
const sqlQuery = sqlQuerySelector(
|
|
`datamart.${fd.data[0].activated > 0 && !doubleQueries.includes(data.name) ? data.name : `legacy.${data.name}`}`,
|
|
) as SqlQuery;
|
|
|
|
// checking if warehousing is as it will start to effect a lot of queries for plants that are not on 2.
|
|
|
|
const getDataMartInfo = datamartData.filter((x) => x.endpoint === data.name);
|
|
|
|
// const optionsMissing =
|
|
// !data.options || Object.keys(data.options).length === 0;
|
|
|
|
const isValid =
|
|
Object.keys(data.options ?? {}).length >=
|
|
(getDataMartInfo[0]?.howManyOptionsRequired ?? 0);
|
|
|
|
if (getDataMartInfo[0]?.optionsRequired && !isValid) {
|
|
return returnFunc({
|
|
success: false,
|
|
level: "error",
|
|
module: "datamart",
|
|
subModule: "query",
|
|
message: `This query is required to have ${getDataMartInfo[0]?.howManyOptionsRequired} option(s) set in order use it, please add in your option(s) data and try again.`,
|
|
data: [getDataMartInfo[0].options],
|
|
notify: false,
|
|
});
|
|
}
|
|
|
|
if (!sqlQuery.success) {
|
|
return returnFunc({
|
|
success: false,
|
|
level: "error",
|
|
module: "datamart",
|
|
subModule: "query",
|
|
message: `Error getting ${data.name} info`,
|
|
data: [sqlQuery.message],
|
|
notify: false,
|
|
});
|
|
}
|
|
|
|
// create the query with no changed just to have it here
|
|
let datamartQuery = sqlQuery?.query || "";
|
|
|
|
// split the criteria by "," then and then update the query
|
|
if (data.options) {
|
|
switch (data.name) {
|
|
case "activeArticles":
|
|
break;
|
|
case "deliveryByDateRange":
|
|
datamartQuery = datamartQuery
|
|
.replace("[startDate]", `${data.options.startDate}`)
|
|
.replace("[endDate]", `${data.options.endDate}`)
|
|
.replace(
|
|
"--and r.ArticleHumanReadableId in ([articles]) ",
|
|
data.options.articles
|
|
? `and r.ArticleHumanReadableId in (${data.options.articles})`
|
|
: "--and r.ArticleHumanReadableId in ([articles]) ",
|
|
);
|
|
|
|
break;
|
|
case "customerInventory":
|
|
datamartQuery = datamartQuery
|
|
.replace(
|
|
"--and IdAdressen",
|
|
`and IdAdressen in (${data.options.customer})`,
|
|
)
|
|
.replace(
|
|
"--and x.IdWarenlager in (0)",
|
|
`${data.options.whseToInclude ? `and x.IdWarenlager in (${data.options.whseToInclude})` : `--and x.IdWarenlager in (0)`}`,
|
|
);
|
|
break;
|
|
case "openOrders":
|
|
datamartQuery = datamartQuery
|
|
.replace("[startDay]", `${data.options.startDay}`)
|
|
.replace("[endDay]", `${data.options.endDay}`);
|
|
break;
|
|
case "inventory":
|
|
datamartQuery = datamartQuery
|
|
.replaceAll(
|
|
"--,l.RunningNumber",
|
|
`${data.options.includeRunningNumbers ? `,l.RunningNumber` : `--,l.RunningNumber`}`,
|
|
)
|
|
.replaceAll(
|
|
"--,l.MachineLocation,l.MachineName,l.ProductionLotRunningNumber as lot",
|
|
`${data.options.lots ? `,l.MachineLocation,l.MachineName,l.ProductionLotRunningNumber as lot` : `--,l.MachineLocation,l.MachineName,l.ProductionLotRunningNumber as lot`}`,
|
|
)
|
|
.replaceAll(
|
|
"--,l.WarehouseDescription,l.LaneDescription",
|
|
`${data.options.locations ? `,l.WarehouseDescription,l.LaneDescription` : `--,l.WarehouseDescription,l.LaneDescription`}`,
|
|
);
|
|
break;
|
|
case "fakeEDIUpdate":
|
|
datamartQuery = datamartQuery.replace(
|
|
"--AND h.CustomerHumanReadableId in (0)",
|
|
`${data.options.address ? `AND h.CustomerHumanReadableId in (${data.options.address})` : `--AND h.CustomerHumanReadableId in (0)`}`,
|
|
);
|
|
|
|
break;
|
|
case "forecast":
|
|
datamartQuery = datamartQuery.replace(
|
|
"where DeliveryAddressHumanReadableId in ([customers])",
|
|
data.options.customers
|
|
? `where DeliveryAddressHumanReadableId in (${data.options.customers})`
|
|
: "--where DeliveryAddressHumanReadableId in ([customers])",
|
|
);
|
|
|
|
break;
|
|
case "activeArticles2":
|
|
datamartQuery = datamartQuery.replace(
|
|
"and a.HumanReadableId in ([articles])",
|
|
data.options.articles
|
|
? `and a.HumanReadableId in (${data.options.articles})`
|
|
: "--and a.HumanReadableId in ([articles])",
|
|
);
|
|
|
|
break;
|
|
|
|
case "psiDeliveryData":
|
|
datamartQuery = datamartQuery
|
|
.replace("[startDate]", `${data.options.startDate}`)
|
|
.replace("[endDate]", `${data.options.endDate}`)
|
|
.replace(
|
|
"[articles]",
|
|
data.options.articles ? `${data.options.articles}` : "[articles]",
|
|
);
|
|
break;
|
|
case "productionData":
|
|
datamartQuery = datamartQuery
|
|
.replace("[startDate]", `${data.options.startDate}`)
|
|
.replace("[endDate]", `${data.options.endDate}`)
|
|
.replace(
|
|
"and ArticleHumanReadableId in ([articles])",
|
|
data.options.articles
|
|
? `and ArticleHumanReadableId in (${data.options.articles})`
|
|
: "--and ArticleHumanReadableId in ([articles])",
|
|
);
|
|
break;
|
|
case "psiPlanningData":
|
|
datamartQuery = datamartQuery
|
|
.replace("[startDate]", `${data.options.startDate}`)
|
|
.replace("[endDate]", `${data.options.endDate}`)
|
|
.replace(
|
|
"and p.IdArtikelvarianten in ([articles])",
|
|
data.options.articles
|
|
? `and p.IdArtikelvarianten in (${data.options.articles})`
|
|
: "--and p.IdArtikelvarianten in ([articles])",
|
|
);
|
|
break;
|
|
default:
|
|
return returnFunc({
|
|
success: false,
|
|
level: "error",
|
|
module: "datamart",
|
|
subModule: "query",
|
|
message: `${data.name} encountered an error as it might not exist in LST please contact support if this continues to happen`,
|
|
data: [sqlQuery.message],
|
|
notify: true,
|
|
});
|
|
}
|
|
}
|
|
|
|
const { data: queryRun, error } = await tryCatch(
|
|
prodQuery(datamartQuery, `Running datamart query: ${data.name}`),
|
|
);
|
|
|
|
if (error) {
|
|
return returnFunc({
|
|
success: false,
|
|
level: "error",
|
|
module: "datamart",
|
|
subModule: "query",
|
|
message: `Data for: ${data.name} encountered an error while trying to get it`,
|
|
data: [error],
|
|
notify: false,
|
|
});
|
|
}
|
|
|
|
if (!queryRun.success) {
|
|
return returnFunc({
|
|
success: false,
|
|
level: "error",
|
|
module: "datamart",
|
|
subModule: "query",
|
|
message: queryRun.message,
|
|
data: queryRun.data,
|
|
notify: false,
|
|
});
|
|
}
|
|
|
|
return returnFunc({
|
|
success: true,
|
|
level: "info",
|
|
module: "datamart",
|
|
subModule: "query",
|
|
message: `Data for: ${data.name}`,
|
|
data: queryRun.data,
|
|
notify: false,
|
|
});
|
|
};
|