125 lines
12 KiB
JavaScript
125 lines
12 KiB
JavaScript
// services/sales/schema.js
|
|
// Idempotent DDL + seed for every Sales Order module table (ZSO_*), run once
|
|
// at server start (server.js bootstrap). New ERP-created orders/samples start
|
|
// at ID 50001 so they can never collide with migrated msale IDs (msale was at
|
|
// ~11.7k in 2026) — the ID is what goes into SAP ORDR.U_WEB_SO_NO.
|
|
'use strict';
|
|
const { query } = require('./db');
|
|
const { USER_TYPES, DEFAULT_DIVISIONS, DEFAULT_SETTINGS } = require('./constants');
|
|
|
|
const T = (name, body) => `IF OBJECT_ID('dbo.${name}','U') IS NULL CREATE TABLE dbo.${name} (${body})`;
|
|
|
|
const DDL = [
|
|
T('ZSO_SETTINGS', `SKEY NVARCHAR(100) NOT NULL PRIMARY KEY, SVALUE NVARCHAR(MAX) NULL, UPDATED_AT DATETIME2 DEFAULT SYSDATETIME(), UPDATED_BY NVARCHAR(100) NULL`),
|
|
T('ZSO_DIVISIONS', `ID INT NOT NULL PRIMARY KEY, NAME NVARCHAR(100) NOT NULL, CODE NVARCHAR(10) NULL, ITEM_GROUPS NVARCHAR(200) NULL,
|
|
WAREHOUSE NVARCHAR(16) NULL, SORT INT DEFAULT 0, ACTIVE BIT DEFAULT 1`),
|
|
T('ZSO_PRODUCTS', `ID INT IDENTITY(1,1) PRIMARY KEY, CATALOG NVARCHAR(10) NOT NULL DEFAULT 'domestic', DIVISION_ID INT NOT NULL,
|
|
ITEM_CODE NVARCHAR(100) NOT NULL, DISPLAY_CODE NVARCHAR(100) NULL, DESCRIPTION NVARCHAR(400) NULL, SHORT_DESC NVARCHAR(400) NULL,
|
|
PACK_SIZE INT DEFAULT 1, HSN NVARCHAR(50) NULL, UNIT_PRICE DECIMAL(19,4) DEFAULT 0, IS_INSTRUMENT BIT DEFAULT 0, ACTIVE BIT DEFAULT 1,
|
|
LEGACY_ID INT NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), UPDATED_AT DATETIME2 NULL, UPDATED_BY NVARCHAR(100) NULL`),
|
|
T('ZSO_USER_TYPES', `ID INT NOT NULL PRIMARY KEY, NAME NVARCHAR(100) NOT NULL, SCOPE NVARCHAR(10) NOT NULL DEFAULT 'all',
|
|
LIST_STATUSES NVARCHAR(100) NULL, DEFAULT_STEPS NVARCHAR(MAX) NULL, DEFAULT_MODULES NVARCHAR(MAX) NULL, ACTIVE BIT DEFAULT 1`),
|
|
T('ZSO_USER_PROFILES', `USER_ID INT NOT NULL PRIMARY KEY, USER_TYPE_ID INT NULL, DIVISIONS NVARCHAR(200) NULL, EMP_CODE NVARCHAR(30) NULL,
|
|
TERRITORY NVARCHAR(150) NULL, CC_EMAILS NVARCHAR(500) NULL, LEGACY_EMP_ID INT NULL, UPDATED_AT DATETIME2 NULL, UPDATED_BY NVARCHAR(100) NULL`),
|
|
T('ZSO_SALES_PERSONS', `ID INT IDENTITY(1,1) PRIMARY KEY, NAME NVARCHAR(200) NOT NULL, EMAIL NVARCHAR(150) NULL, DESIGNATION NVARCHAR(100) NULL,
|
|
DIVISIONS NVARCHAR(100) NULL, USER_ID INT NULL, ACTIVE BIT DEFAULT 1, LEGACY_ID INT NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME()`),
|
|
T('ZSO_SP_CUSTOMER_MAP', `ID INT IDENTITY(1,1) PRIMARY KEY, SALES_PERSON_ID INT NOT NULL, CARD_CODE NVARCHAR(30) NOT NULL, ACTIVE BIT DEFAULT 1,
|
|
CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), CREATED_BY NVARCHAR(100) NULL`),
|
|
T('ZSO_CUSTOMERS', `CARD_CODE NVARCHAR(30) NOT NULL PRIMARY KEY, CARD_NAME NVARCHAR(200) NULL, EMAIL NVARCHAR(200) NULL, PASSWORD_HASH NVARCHAR(200) NULL,
|
|
ACTIVE BIT DEFAULT 1, LOCKED BIT DEFAULT 0, LOGIN_ATTEMPTS INT DEFAULT 0, MUST_CHANGE_PWD BIT DEFAULT 0, LAST_LOGIN DATETIME2 NULL,
|
|
CREDIT_LIMIT DECIMAL(19,2) DEFAULT 0, LEGACY_ID INT NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), UPDATED_AT DATETIME2 NULL, UPDATED_BY NVARCHAR(100) NULL`),
|
|
T('ZSO_ORDERS', `ID INT IDENTITY(50001,1) PRIMARY KEY, REF_NO NVARCHAR(30) NULL, COMPANY NVARCHAR(100) NULL,
|
|
SO_TYPE TINYINT NOT NULL, ORDER_TYPE NVARCHAR(12) NULL, CARD_CODE NVARCHAR(30) NOT NULL, CARD_NAME NVARCHAR(200) NULL, DIVISION_ID INT NOT NULL,
|
|
CUST_ORDER_NO NVARCHAR(100) NULL, ORDER_DATE DATE NULL, DELIVERY_DATE DATE NULL, CONTACT_PERSON NVARCHAR(200) NULL, SALES_EMPLOYEE NVARCHAR(200) NULL,
|
|
SHIP_TO_CODE NVARCHAR(100) NULL, SHIP_TO_TEXT NVARCHAR(1000) NULL, SHIP_STATE NVARCHAR(10) NULL, BILL_TO_CODE NVARCHAR(100) NULL, BILL_TO_TEXT NVARCHAR(1000) NULL,
|
|
TAX_CODE NVARCHAR(30) NULL, IGST_RATE DECIMAL(9,3) DEFAULT 0, CGST_RATE DECIMAL(9,3) DEFAULT 0, SGST_RATE DECIMAL(9,3) DEFAULT 0, TCS_RATE DECIMAL(9,3) DEFAULT 0,
|
|
IGST_VAL DECIMAL(19,2) DEFAULT 0, CGST_VAL DECIMAL(19,2) DEFAULT 0, SGST_VAL DECIMAL(19,2) DEFAULT 0, TCS_VAL DECIMAL(19,2) DEFAULT 0,
|
|
SUB_TOTAL DECIMAL(19,2) DEFAULT 0, GRAND_TOTAL DECIMAL(19,2) DEFAULT 0, CREDIT_DAYS INT NULL,
|
|
PAYMENT_MODE NVARCHAR(30) NULL, PAYMENT_REF NVARCHAR(150) NULL, PAYMENT_DATE DATE NULL, PAYMENT_DETAIL NVARCHAR(500) NULL,
|
|
PAID_AMOUNT DECIMAL(19,2) NULL, TDS_VAL DECIMAL(19,2) DEFAULT 0, WALLET_USED DECIMAL(19,2) NULL, REBATE DECIMAL(19,2) NULL, PAID_STATUS NVARCHAR(30) NULL,
|
|
CUSTOMER_REMARKS NVARCHAR(MAX) NULL, EMPLOYEE_REMARKS NVARCHAR(MAX) NULL, INTERNAL_REMARKS NVARCHAR(MAX) NULL,
|
|
STATUS INT NOT NULL DEFAULT 1, IS_MULTI_CONSIGNEE BIT DEFAULT 0, NEEDS_REVIEW BIT DEFAULT 0,
|
|
SAP_DOC_ENTRY INT NULL, SAP_DOC_NUM INT NULL, SAP_ORDER_DATE DATE NULL, SAP_POSTED_AT DATETIME2 NULL, SAP_POSTED_BY NVARCHAR(100) NULL, SAP_ERROR NVARCHAR(MAX) NULL,
|
|
INVOICE_JSON NVARCHAR(MAX) NULL, LAST_SAP_SYNC DATETIME2 NULL,
|
|
CREATED_BY_TYPE NVARCHAR(10) NULL, CREATED_BY NVARCHAR(100) NULL, CREATED_BY_NAME NVARCHAR(200) NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(),
|
|
UPDATED_AT DATETIME2 NULL, UPDATED_BY_NAME NVARCHAR(200) NULL, LEGACY BIT DEFAULT 0`),
|
|
T('ZSO_ORDER_ITEMS', `ID INT IDENTITY(1,1) PRIMARY KEY, ORDER_ID INT NOT NULL, LINE_NO INT NOT NULL, PRODUCT_ID INT NULL, ITEM_CODE NVARCHAR(100) NOT NULL,
|
|
DISPLAY_CODE NVARCHAR(100) NULL, DESCRIPTION NVARCHAR(400) NULL, QTY DECIMAL(19,3) NOT NULL, NOP INT NULL, PRICE DECIMAL(19,4) DEFAULT 0,
|
|
SPECIAL_PRICE DECIMAL(19,4) DEFAULT 0, COMMISSION DECIMAL(9,3) DEFAULT 0, HSN NVARCHAR(50) NULL, LINE_TOTAL DECIMAL(19,2) DEFAULT 0, ACTIVE BIT DEFAULT 1`),
|
|
T('ZSO_ORDER_CONSIGNEES', `ID INT IDENTITY(1,1) PRIMARY KEY, ORDER_ID INT NOT NULL, SHIP_TO_CODE NVARCHAR(100) NULL, SHIP_TO_TEXT NVARCHAR(1000) NULL,
|
|
SE_NAME NVARCHAR(200) NULL, SE_EMAIL NVARCHAR(200) NULL, ITEMS_JSON NVARCHAR(MAX) NULL, SAP_DOC_ENTRY INT NULL, SAP_DOC_NUM INT NULL, ACTIVE BIT DEFAULT 1`),
|
|
T('ZSO_DOCS', `ID INT IDENTITY(1,1) PRIMARY KEY, ENTITY NVARCHAR(20) NOT NULL, ENTITY_ID INT NOT NULL, DOC_TYPE NVARCHAR(30) NOT NULL,
|
|
TITLE NVARCHAR(300) NULL, FILE_NAME NVARCHAR(300) NOT NULL, ORIG_NAME NVARCHAR(300) NULL, ACTIVE BIT DEFAULT 1,
|
|
CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), CREATED_BY_NAME NVARCHAR(200) NULL`),
|
|
T('ZSO_LOG', `ID INT IDENTITY(1,1) PRIMARY KEY, ENTITY NVARCHAR(20) NOT NULL, ENTITY_ID INT NOT NULL, ACTION NVARCHAR(50) NOT NULL,
|
|
FROM_STATUS INT NULL, TO_STATUS INT NULL, REMARKS NVARCHAR(MAX) NULL, ACTOR_TYPE NVARCHAR(10) NULL, ACTOR NVARCHAR(100) NULL,
|
|
ACTOR_NAME NVARCHAR(200) NULL, AT DATETIME2 DEFAULT SYSDATETIME()`),
|
|
T('ZSO_WALLET_TXN', `ID INT IDENTITY(1,1) PRIMARY KEY, CARD_CODE NVARCHAR(30) NOT NULL, TXN_TYPE NVARCHAR(10) NOT NULL, AMOUNT DECIMAL(19,2) NOT NULL,
|
|
TDS DECIMAL(19,2) DEFAULT 0, STATUS NVARCHAR(12) NOT NULL, REF_TYPE NVARCHAR(15) NOT NULL, ORDER_ID INT NULL,
|
|
PAYMENT_MODE NVARCHAR(30) NULL, PAYMENT_REF NVARCHAR(150) NULL, PAYMENT_DATE DATE NULL, PAYMENT_DETAIL NVARCHAR(500) NULL, REMARKS NVARCHAR(1000) NULL,
|
|
BANK_API_ID NVARCHAR(50) NULL, DOC_ID INT NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), CREATED_BY_NAME NVARCHAR(200) NULL,
|
|
DECIDED_AT DATETIME2 NULL, DECIDED_BY_NAME NVARCHAR(200) NULL`),
|
|
T('ZSO_SAMPLES', `ID INT IDENTITY(50001,1) PRIMARY KEY, REF_NO NVARCHAR(30) NULL, CARD_CODE NVARCHAR(30) NOT NULL, CARD_NAME NVARCHAR(200) NULL,
|
|
ADDRESS NVARCHAR(1000) NULL, BB_LICENCE_NO NVARCHAR(200) NULL, BB_NAME NVARCHAR(200) NULL, DIVISION_ID INT NOT NULL, REQ_DATE DATE NULL,
|
|
DELIVERY_DAYS INT NULL, STATUS INT NOT NULL DEFAULT 1, INTERNAL_REMARKS NVARCHAR(MAX) NULL, MODIFICATION_REMARKS NVARCHAR(1000) NULL,
|
|
REJECTION_REMARKS NVARCHAR(1000) NULL, STOCK_AVAILABLE BIT NULL, QA_DUE_DATE DATE NULL, QA_VERIFIED BIT NULL, OUT_OF_STOCK_DATE DATE NULL,
|
|
CHALLAN_NO NVARCHAR(100) NULL, CHALLAN_DATE DATE NULL, COURIER_NO NVARCHAR(100) NULL, DISPATCH_INFO NVARCHAR(500) NULL, FEEDBACK NVARCHAR(1000) NULL,
|
|
CREATED_BY INT NULL, CREATED_BY_NAME NVARCHAR(200) NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), UPDATED_AT DATETIME2 NULL,
|
|
UPDATED_BY_NAME NVARCHAR(200) NULL, LEGACY BIT DEFAULT 0`),
|
|
T('ZSO_SAMPLE_ITEMS', `ID INT IDENTITY(1,1) PRIMARY KEY, SAMPLE_ID INT NOT NULL, KIND NVARCHAR(10) NOT NULL DEFAULT 'domestic', PRODUCT_ID INT NULL,
|
|
ITEM_CODE NVARCHAR(100) NOT NULL, DISPLAY_CODE NVARCHAR(100) NULL, DESCRIPTION NVARCHAR(400) NULL, QTY DECIMAL(19,3) NOT NULL, NOP INT NULL,
|
|
BATCH_NO NVARCHAR(200) NULL, ACTIVE BIT DEFAULT 1`),
|
|
T('ZSO_EXPORT_SAMPLES', `ID INT IDENTITY(50001,1) PRIMARY KEY, REF_NO NVARCHAR(30) NULL, REGION NVARCHAR(500) NULL, SAMPLE_AGAINST NVARCHAR(500) NULL,
|
|
CUST_NAME NVARCHAR(200) NOT NULL, COUNTRY NVARCHAR(200) NULL, SHIPPING_ADDRESS NVARCHAR(1000) NULL, CONTACT_PERSON NVARCHAR(200) NULL,
|
|
CONTACT_EMAIL NVARCHAR(200) NULL, CONTACT_NO NVARCHAR(100) NULL, ACCOUNT_DETAILS NVARCHAR(300) NULL, COURIER_BY_CUSTOMER BIT DEFAULT 0,
|
|
DIVISION_ID INT NULL, REQ_DATE DATE NULL, DELIVERY_DATE DATE NULL, DELIVERY_DAYS INT NULL, REMARKS_QA NVARCHAR(1000) NULL,
|
|
REMARKS_LOGISTICS NVARCHAR(500) NULL, STATUS INT NOT NULL DEFAULT 1, STOCK_AVAILABLE BIT NULL, QA_DUE_DATE DATE NULL, QA_VERIFIED BIT NULL,
|
|
CHALLAN_NO NVARCHAR(100) NULL, CHALLAN_DATE DATE NULL, COURIER_NO NVARCHAR(100) NULL, DISPATCH_DETAILS NVARCHAR(500) NULL,
|
|
CREATED_BY INT NULL, CREATED_BY_NAME NVARCHAR(200) NULL, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), UPDATED_AT DATETIME2 NULL,
|
|
UPDATED_BY_NAME NVARCHAR(200) NULL, LEGACY BIT DEFAULT 0`),
|
|
// One row per SAP invoice already announced by email (never mail twice).
|
|
T('ZSO_INVOICE_NOTICES', `ID INT IDENTITY(1,1) PRIMARY KEY, ORDER_ID INT NOT NULL, INVOICE_DOC_ENTRY INT NOT NULL, INVOICE_NO INT NULL,
|
|
INVOICE_DATE DATE NULL, EMAILED BIT DEFAULT 0, CREATED_AT DATETIME2 DEFAULT SYSDATETIME(), CONSTRAINT UQ_ZSO_INV_NOTICE UNIQUE (ORDER_ID, INVOICE_DOC_ENTRY)`),
|
|
T('ZSO_INVOICE_FILES', `ID INT IDENTITY(1,1) PRIMARY KEY, INVOICE_NO NVARCHAR(50) NOT NULL, FILE_NAME NVARCHAR(300) NOT NULL,
|
|
UPLOADED_AT DATETIME2 DEFAULT SYSDATETIME()`),
|
|
];
|
|
|
|
const INDEXES = [
|
|
['IX_ZSO_ORDERS_CARD', 'ZSO_ORDERS(CARD_CODE, STATUS)'],
|
|
['IX_ZSO_ORDERS_STATUS', 'ZSO_ORDERS(STATUS, DIVISION_ID)'],
|
|
['IX_ZSO_ORDER_ITEMS_ORD', 'ZSO_ORDER_ITEMS(ORDER_ID)'],
|
|
['IX_ZSO_WALLET_CARD', 'ZSO_WALLET_TXN(CARD_CODE, STATUS)'],
|
|
['IX_ZSO_LOG_ENT', 'ZSO_LOG(ENTITY, ENTITY_ID)'],
|
|
['IX_ZSO_DOCS_ENT', 'ZSO_DOCS(ENTITY, ENTITY_ID)'],
|
|
['IX_ZSO_SPMAP_SP', 'ZSO_SP_CUSTOMER_MAP(SALES_PERSON_ID, CARD_CODE)'],
|
|
['IX_ZSO_PRODUCTS_DIV', 'ZSO_PRODUCTS(CATALOG, DIVISION_ID)'],
|
|
['IX_ZSO_SAMPLE_ITEMS_S', 'ZSO_SAMPLE_ITEMS(SAMPLE_ID, KIND)'],
|
|
];
|
|
|
|
async function bootstrap() {
|
|
for (const ddl of DDL) await query(ddl);
|
|
for (const [name, on] of INDEXES) {
|
|
const table = on.split('(')[0];
|
|
await query(`IF NOT EXISTS (SELECT 1 FROM sys.indexes WHERE name='${name}' AND object_id=OBJECT_ID('dbo.${table}')) CREATE INDEX ${name} ON dbo.${on}`);
|
|
}
|
|
// Seeds — only insert what's missing, never overwrite admin edits.
|
|
for (const d of DEFAULT_DIVISIONS) {
|
|
await query(`IF NOT EXISTS (SELECT 1 FROM dbo.ZSO_DIVISIONS WHERE ID=?) INSERT INTO dbo.ZSO_DIVISIONS (ID,NAME,CODE,ITEM_GROUPS,WAREHOUSE,SORT,ACTIVE) VALUES (?,?,?,?,?,?,1)`,
|
|
[d.id, d.id, d.name, d.code, d.itemGroups, d.warehouse, d.sort]);
|
|
}
|
|
for (const u of USER_TYPES) {
|
|
await query(`IF NOT EXISTS (SELECT 1 FROM dbo.ZSO_USER_TYPES WHERE ID=?) INSERT INTO dbo.ZSO_USER_TYPES (ID,NAME,SCOPE,LIST_STATUSES,DEFAULT_STEPS,DEFAULT_MODULES,ACTIVE) VALUES (?,?,?,?,?,?,1)`,
|
|
[u.id, u.id, u.name, u.scope, u.listStatuses, JSON.stringify(u.steps), JSON.stringify(u.modules)]);
|
|
}
|
|
// Invoice emails start from the day the feature is first deployed — older
|
|
// invoices are recorded silently so go-live doesn't send a backlog flood.
|
|
await query(`IF NOT EXISTS (SELECT 1 FROM dbo.ZSO_SETTINGS WHERE SKEY='invoiceEmailSince') INSERT INTO dbo.ZSO_SETTINGS (SKEY,SVALUE) VALUES ('invoiceEmailSince',?)`,
|
|
[JSON.stringify(new Date().toISOString().slice(0, 10))]);
|
|
for (const [k, v] of Object.entries(DEFAULT_SETTINGS)) {
|
|
await query(`IF NOT EXISTS (SELECT 1 FROM dbo.ZSO_SETTINGS WHERE SKEY=?) INSERT INTO dbo.ZSO_SETTINGS (SKEY,SVALUE) VALUES (?,?)`, [k, k, JSON.stringify(v)]);
|
|
}
|
|
console.log('[SALES] ✅ Sales Order module tables ready');
|
|
}
|
|
|
|
module.exports = { bootstrap };
|