300 lines
13 KiB
JavaScript
300 lines
13 KiB
JavaScript
'use strict';
|
|
const express = require('express');
|
|
const router = express.Router();
|
|
const https = require('https');
|
|
const axios = require('axios');
|
|
const FormData = require('form-data');
|
|
const { verifyToken } = require('../middleware/auth');
|
|
const { getPool } = require('../services/sqlPool');
|
|
|
|
const httpsAgent = new https.Agent({ rejectUnauthorized: false });
|
|
|
|
// ── TCS GSP config ────────────────────────────────────────────────────────
|
|
const CFG = {
|
|
M2JW: {
|
|
mappingCd: process.env.ITC04_M2JW_MAPPING_CD || 'MFHGSTTN0000880',
|
|
templateCd: process.env.ITC04_M2JW_TEMPLATE_CD || 'ITC04M2JWTemplate880',
|
|
},
|
|
JW2M: {
|
|
mappingCd: process.env.ITC04_JW2M_MAPPING_CD || 'MFHGSTTN0000881',
|
|
templateCd: process.env.ITC04_JW2M_TEMPLATE_CD || 'ITC04JW2MTemplate881',
|
|
},
|
|
};
|
|
|
|
// DB column name → CSV header name mapping
|
|
// (desc is a SQL reserved word; SQL returns it as desc_field, CSV header must be desc)
|
|
const COL_MAP = [
|
|
['trans_type_code','trans_type_code'],
|
|
['fp', 'fp'],
|
|
['ack_sr_no', 'ack_sr_no'],
|
|
['self_gstin', 'self_gstin'],
|
|
['resultflag', 'resultflag'],
|
|
['ctin', 'ctin'],
|
|
['ctin_name', 'ctin_name'],
|
|
['jw_stcd', 'jw_stcd'],
|
|
['chnum', 'chnum'],
|
|
['chdt', 'chdt'],
|
|
['goods_ty', 'goods_ty'],
|
|
['uqc', 'uqc'],
|
|
['qty', 'qty'],
|
|
['desc_field', 'desc'], // SQL alias → TCS CSV header
|
|
['txval', 'txval'],
|
|
['tx_i', 'tx_i'],
|
|
['tx_c', 'tx_c'],
|
|
['tx_s', 'tx_s'],
|
|
['tx_cs', 'tx_cs'],
|
|
];
|
|
const COLS = COL_MAP.map(([db]) => db);
|
|
|
|
// ── Quarter period helpers ─────────────────────────────────────────────────
|
|
// SQL generates fp as MMYYYY (e.g. 042026 = Q1 Apr-Jun 2026)
|
|
// TCS GSP URL requires a different code: 13yyyy / 14yyyy / 15yyyy / 16yyyy
|
|
function toTcsPeriod(fp) {
|
|
// fp = 'MMYYYY' e.g. '042026'
|
|
const mm = parseInt(fp.substring(0, 2), 10);
|
|
const yyyy = fp.substring(2);
|
|
let code;
|
|
if (mm === 4) code = '13'; // Q1 Apr-Jun
|
|
else if (mm === 7) code = '14'; // Q2 Jul-Sep
|
|
else if (mm === 10) code = '15'; // Q3 Oct-Dec
|
|
else if (mm === 1) code = '16'; // Q4 Jan-Mar
|
|
else code = '13'; // fallback
|
|
return code + yyyy; // e.g. '132026'
|
|
}
|
|
|
|
// ── SQL query builders ────────────────────────────────────────────────────
|
|
function buildSQL(transType, series, resultflag = 'A') {
|
|
const selfGstin = process.env.TCS_GSP_GSTIN || '06AAACM4564C1ZT';
|
|
const flag = ['A','M','D'].includes(resultflag) ? resultflag : 'A';
|
|
return `
|
|
SELECT
|
|
'${transType}' AS trans_type_code,
|
|
-- TCS format: Q1=13yyyy, Q2=14yyyy, Q3=15yyyy, Q4=16yyyy
|
|
CAST(
|
|
CASE
|
|
WHEN MONTH(T0.DocDate) BETWEEN 4 AND 6 THEN '13'
|
|
WHEN MONTH(T0.DocDate) BETWEEN 7 AND 9 THEN '14'
|
|
WHEN MONTH(T0.DocDate) BETWEEN 10 AND 12 THEN '15'
|
|
ELSE '16'
|
|
END
|
|
+ CAST(
|
|
CASE WHEN MONTH(T0.DocDate) >= 4 THEN YEAR(T0.DocDate)
|
|
ELSE YEAR(T0.DocDate) - 1
|
|
END AS VARCHAR(4))
|
|
AS VARCHAR(6)) AS fp,
|
|
ROW_NUMBER() OVER (ORDER BY T0.DocEntry, T2.LineNum) AS ack_sr_no,
|
|
'${selfGstin}' AS self_gstin,
|
|
'${flag}' AS resultflag,
|
|
CASE WHEN CRD1.GSTRegnNo IS NOT NULL AND CRD1.GSTRegnNo <> ''
|
|
THEN CRD1.GSTRegnNo ELSE '' END AS ctin,
|
|
T0.CardName AS ctin_name,
|
|
CASE WHEN CRD1.GSTRegnNo IS NULL OR CRD1.GSTRegnNo = ''
|
|
THEN T0.U_State ELSE '' END AS jw_stcd,
|
|
CAST(T0.DocNum AS VARCHAR(16)) AS chnum,
|
|
CONVERT(VARCHAR(10), T0.DocDate, 105) AS chdt,
|
|
'8b' AS goods_ty,
|
|
T2.unitMsr AS uqc,
|
|
CAST(T2.Quantity AS DECIMAL(15,2)) AS qty,
|
|
-- Cast to VARCHAR, truncate to 70 chars, fall back to ItemCode if empty
|
|
CAST(LEFT(ISNULL(NULLIF(LTRIM(RTRIM(T2.Dscription)), ''), T2.ItemCode), 70) AS VARCHAR(70)) AS desc_field,
|
|
CAST(T2.LineTotal AS DECIMAL(11,2)) AS txval,
|
|
CAST(ISNULL(T2.U_IGST_Rate1, 0) AS DECIMAL(11,2)) AS tx_i,
|
|
CAST(ISNULL(T2.U_CGST_RATE1, 0) AS DECIMAL(11,2)) AS tx_c,
|
|
CAST(ISNULL(T2.U_SGST_RATE1, 0) AS DECIMAL(11,2)) AS tx_s,
|
|
CAST(0.00 AS DECIMAL(11,2)) AS tx_cs
|
|
FROM OWTR T0
|
|
INNER JOIN WTR1 T2 ON T0.DocEntry = T2.DocEntry
|
|
LEFT JOIN NNM1 T1 ON T0.Series = T1.Series
|
|
LEFT JOIN CRD1
|
|
ON CRD1.CardCode = T0.CardCode
|
|
AND CRD1.AdresType = 'S'
|
|
AND CRD1.Address = T0.ShipToCode
|
|
WHERE T0.DocDate BETWEEN @from AND @to
|
|
AND T1.SeriesName LIKE '${series}%'
|
|
ORDER BY T0.DocEntry, T2.LineNum`;
|
|
}
|
|
|
|
// ── Fetch data from SAP ───────────────────────────────────────────────────
|
|
async function fetchData(pool, from, to, type, resultflag = 'A') {
|
|
const series = type === 'JW2M' ? 'JWR' : 'JW';
|
|
const sql = buildSQL(type, series, resultflag);
|
|
const r = await pool.request()
|
|
.input('from', from)
|
|
.input('to', to)
|
|
.query(sql);
|
|
return r.recordset || [];
|
|
}
|
|
|
|
// ── Sanitize description for TCS ITC04 ────────────────────────────────────
|
|
// TCS CLNDESC validator rejects: & ( ) . and other special chars.
|
|
// Allowed: A-Z a-z 0-9 space / - , _ '
|
|
function sanitizeDesc(v) {
|
|
return String(v || '')
|
|
.replace(/[\r\n\t]/g, ' ') // newlines/tabs → space
|
|
.replace(/[^\x20-\x7E]/g, '') // strip non-ASCII
|
|
.replace(/&/g, 'and') // & → and
|
|
.replace(/\(/g, ' ') // ( → space
|
|
.replace(/\)/g, ' ') // ) → space
|
|
.replace(/\./g, ' ') // . → space (TCS rejects decimal points in desc)
|
|
.replace(/[^A-Za-z0-9 /,\-_']/g, ' ') // strip remaining special chars
|
|
.replace(/\s+/g, ' ') // collapse multiple spaces
|
|
.trim()
|
|
.substring(0, 50); // TCS ITC04 template: max 50 chars
|
|
}
|
|
|
|
// Numeric columns — written as plain numbers, no quotes ever
|
|
const NUMERIC_COLS = new Set(['ack_sr_no','qty','txval','tx_i','tx_c','tx_s','tx_cs']);
|
|
|
|
// ── Build CSV buffer ──────────────────────────────────────────────────────
|
|
function buildCsv(rows) {
|
|
const header = COL_MAP.map(([, csvName]) => csvName).join(',');
|
|
const lines = rows.map(r =>
|
|
COL_MAP.map(([dbCol]) => {
|
|
const raw = r[dbCol] ?? '';
|
|
|
|
if (NUMERIC_COLS.has(dbCol)) return raw; // plain number, no quoting
|
|
|
|
const v = dbCol === 'desc_field' ? sanitizeDesc(raw) : String(raw).trim();
|
|
|
|
// TCS does not expect RFC-quoted CSV — strip commas instead of quoting
|
|
return v.replace(/,/g, ' ');
|
|
}).join(',')
|
|
);
|
|
return Buffer.from([header, ...lines].join('\r\n'), 'utf8');
|
|
}
|
|
|
|
// ── TCS auth token ────────────────────────────────────────────────────────
|
|
async function getTcsToken() {
|
|
const baseUrl = process.env.TCS_GSP_BASE_URL || 'https://g31.tcsgsp.in';
|
|
const username = process.env.TCS_GSP_USERNAME || '';
|
|
const password = process.env.TCS_GSP_PASSWORD || '';
|
|
const resp = await axios.get(
|
|
`${baseUrl}/Tax-Tool-Core/services/accessMgmt/generateToken`,
|
|
{ headers: { 'Content-Type': 'application/json', username, password }, httpsAgent }
|
|
);
|
|
const token = resp.data?.access_token || resp.data?.token || resp.data?.accessToken;
|
|
if (!token) throw new Error('TCS token missing: ' + JSON.stringify(resp.data));
|
|
return token;
|
|
}
|
|
|
|
// ── GET /api/itc04/data ───────────────────────────────────────────────────
|
|
router.get('/data', verifyToken, async (req, res) => {
|
|
try {
|
|
const { from, to, type = 'M2JW', resultflag = 'A' } = req.query;
|
|
if (!from || !to) return res.status(400).json({ success: false, message: 'from and to dates are required' });
|
|
if (!['M2JW','JW2M'].includes(type)) return res.status(400).json({ success: false, message: 'type must be M2JW or JW2M' });
|
|
|
|
const pool = await getPool();
|
|
const rows = await fetchData(pool, from, to, type, resultflag);
|
|
const total = rows.reduce((s, r) => s + (parseFloat(r.txval) || 0), 0);
|
|
|
|
res.json({ success: true, data: rows, count: rows.length, totalValue: parseFloat(total.toFixed(2)) });
|
|
} catch (err) {
|
|
res.status(500).json({ success: false, message: err.message });
|
|
}
|
|
});
|
|
|
|
// ── GET /api/itc04/download?from=&to=&type= ───────────────────────────────
|
|
router.get('/download', verifyToken, async (req, res) => {
|
|
try {
|
|
const { from, to, type = 'M2JW', resultflag = 'A', period } = req.query;
|
|
if (!from || !to) return res.status(400).json({ success: false, message: 'from and to required' });
|
|
|
|
const pool = await getPool();
|
|
const rows = await fetchData(pool, from, to, type, resultflag);
|
|
if (!rows.length) return res.status(404).json({ success: false, message: 'No data found for the selected period' });
|
|
|
|
const fp = period || rows[0]?.fp || 'period';
|
|
const csv = buildCsv(rows);
|
|
const filename = `ITC04_${type}_${fp}.csv`;
|
|
|
|
res.setHeader('Content-Type', 'text/csv');
|
|
res.setHeader('Content-Disposition', `attachment; filename="${filename}"`);
|
|
res.send(csv);
|
|
} catch (err) {
|
|
res.status(500).json({ success: false, message: err.message });
|
|
}
|
|
});
|
|
|
|
// ── POST /api/itc04/upload ────────────────────────────────────────────────
|
|
router.post('/upload', verifyToken, async (req, res) => {
|
|
try {
|
|
const { from, to, type = 'M2JW', resultflag = 'A', period } = req.body;
|
|
if (!from || !to) return res.status(400).json({ success: false, message: 'from and to dates are required' });
|
|
|
|
const baseUrl = process.env.TCS_GSP_BASE_URL || 'https://g31.tcsgsp.in';
|
|
const gstin = process.env.TCS_GSP_GSTIN || '';
|
|
const clientCode = process.env.TCS_GSP_CLIENT_CODE || '';
|
|
const cfg = CFG[type] || CFG.M2JW;
|
|
|
|
if (!gstin || !clientCode) return res.status(400).json({ success: false, message: 'TCS_GSP_GSTIN and TCS_GSP_CLIENT_CODE must be set in .env' });
|
|
|
|
const pool = await getPool();
|
|
const rows = await fetchData(pool, from, to, type, resultflag);
|
|
if (!rows.length) return res.status(404).json({ success: false, message: 'No data found for the selected period' });
|
|
|
|
// Use manually entered period if provided, otherwise derive from data
|
|
const fp = (period && /^\d{6}$/.test(period)) ? period : (rows[0]?.fp || '132026');
|
|
const csv = buildCsv(rows);
|
|
const token = await getTcsToken();
|
|
|
|
console.log(`[ITC04] fp=${fp} records=${rows.length}`);
|
|
|
|
const form = new FormData();
|
|
form.append('upldGstinLst', `"${gstin}"`);
|
|
form.append('file', csv, { filename: `ITC04_${type}_${fp}.csv`, contentType: 'text/csv' });
|
|
|
|
const url = `${baseUrl}/Tax-Tool-Core/services/auth/invoiceUpload/challanUploadITC04CSV/${clientCode}/${cfg.mappingCd}/${cfg.templateCd}/${gstin}/CSV/${fp}?oprFlag=SALES`;
|
|
|
|
const uploadResp = await axios.post(url, form, {
|
|
headers: {
|
|
...form.getHeaders(),
|
|
authorization: `Bearer ${token}`,
|
|
clientCode,
|
|
gstin,
|
|
},
|
|
httpsAgent,
|
|
maxContentLength: Infinity,
|
|
maxBodyLength: Infinity,
|
|
});
|
|
|
|
const d = uploadResp.data;
|
|
console.log('[ITC04] Upload HTTP status:', uploadResp.status);
|
|
console.log('[ITC04] Upload response :', JSON.stringify(d, null, 2));
|
|
|
|
// TCS GSP can return ackNo at root level OR nested inside data/result/response
|
|
function extractAckNo(obj) {
|
|
if (!obj || typeof obj !== 'object') return null;
|
|
const keys = ['ackNo','AckNo','ack_no','ackno','acknowledgeNo','acknowledgementNo','ackNumber'];
|
|
for (const k of keys) {
|
|
if (obj[k] != null && obj[k] !== '') return String(obj[k]);
|
|
}
|
|
// Check one level deeper (data / result / response wrappers)
|
|
for (const wrap of ['data','result','response','Result','Data']) {
|
|
if (obj[wrap] && typeof obj[wrap] === 'object') {
|
|
const nested = extractAckNo(obj[wrap]);
|
|
if (nested) return nested;
|
|
}
|
|
}
|
|
return null;
|
|
}
|
|
|
|
const ackNo = extractAckNo(d);
|
|
const status = d.Status || d.status || d.statusCd || uploadResp.status;
|
|
const msg = d.Message || d.message || d.msg || 'Upload submitted';
|
|
|
|
// Treat statusCd 400 as error
|
|
if (String(status) === '400') {
|
|
return res.status(400).json({ success: false, message: msg, raw: d });
|
|
}
|
|
|
|
res.json({ success: true, ackNo, status, message: msg, raw: d });
|
|
} catch (err) {
|
|
const msg = err.response?.data ? JSON.stringify(err.response.data) : err.message;
|
|
console.error('[ITC04] Upload error:', msg);
|
|
res.status(500).json({ success: false, message: msg });
|
|
}
|
|
});
|
|
|
|
module.exports = router;
|