123 lines
4.5 KiB
JavaScript
123 lines
4.5 KiB
JavaScript
const sql = require('C:/Users/13623/AppData/Roaming/npm/node_modules/@eamonboyle/mssql-mcp/node_modules/mssql');
|
|
|
|
const config = {
|
|
server: process.env.DB_SERVER,
|
|
database: process.env.DB_DATABASE,
|
|
user: process.env.DB_USER,
|
|
password: process.env.DB_PASSWORD,
|
|
options: {
|
|
encrypt: false,
|
|
trustServerCertificate: String(process.env.DB_TRUST_CERT || 'true').toLowerCase() === 'true'
|
|
}
|
|
};
|
|
|
|
const headerTables = ['in_stockin', 'in_stockin_finish', 'out_stockout', 'out_stockout_finish'];
|
|
const detailTables = ['in_stockin_detail', 'in_stockin_detail_finish', 'out_stockout_detail', 'out_stockout_detail_finish'];
|
|
|
|
const headerColumns = [
|
|
['document_n', 'NVARCHAR(80)', '单据编号'],
|
|
['document_dat', 'DATE', '单据日期'],
|
|
['project_nam', 'NVARCHAR(255)', '项目号/项目名称'],
|
|
['counterpart', 'NVARCHAR(255)', '供应商/客户'],
|
|
['receiving_or', 'NVARCHAR(255)', '收料组织'],
|
|
['purchasing_or', 'NVARCHAR(255)', '采购组织'],
|
|
['receiving_dep', 'NVARCHAR(255)', '收料部门'],
|
|
['purchasing_dep', 'NVARCHAR(255)', '采购部门'],
|
|
['handle', 'NVARCHAR(255)', '仓管员/发货人/发料人'],
|
|
['creato', 'NVARCHAR(255)', '采购员/制单人'],
|
|
['logistics_', 'NVARCHAR(255)', '物流公司'],
|
|
['logistics_n', 'NVARCHAR(255)', '物流单号'],
|
|
['warehous', 'NVARCHAR(255)', '库房'],
|
|
['print_coun', 'INT', '打印次数'],
|
|
['tota', 'DECIMAL(18,2)', '合计'],
|
|
['reviewe', 'NVARCHAR(255)', '审核人/审批人'],
|
|
['create_dat', 'DATETIME2', '创建日期'],
|
|
['review_dat', 'DATETIME2', '审核日期'],
|
|
['receive', 'NVARCHAR(255)', '收货人'],
|
|
['receiver_a', 'NVARCHAR(255)', '收货地址'],
|
|
['receiver_phon', 'NVARCHAR(50)', '收货人电话'],
|
|
['material_re', 'NVARCHAR(255)', '领料人']
|
|
];
|
|
|
|
const detailColumns = [
|
|
['line_n', 'INT', '序号'],
|
|
['item_cod', 'NVARCHAR(255)', '物料编码/产品代码'],
|
|
['item_nam', 'NVARCHAR(255)', '物料名称/产品名称'],
|
|
['spec_mode', 'NVARCHAR(255)', '规格型号'],
|
|
['uni', 'NVARCHAR(100)', '单位'],
|
|
['actual_qt', 'DECIMAL(18,2)', '实发数量'],
|
|
['warehous', 'NVARCHAR(255)', '仓库'],
|
|
['locatio', 'NVARCHAR(255)', '仓位'],
|
|
['inventory_', 'NVARCHAR(255)', '库存状态'],
|
|
['source_doc_n', 'NVARCHAR(255)', '源单单号'],
|
|
['remar', 'NVARCHAR(255)', '备注']
|
|
];
|
|
|
|
function commentSql(tableName, columnName, comment) {
|
|
const safeComment = comment.replace(/'/g, "''");
|
|
return `
|
|
IF COL_LENGTH('dbo.${tableName}', '${columnName}') IS NOT NULL
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM sys.extended_properties ep
|
|
INNER JOIN sys.columns c ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
|
|
INNER JOIN sys.tables t ON t.object_id = c.object_id
|
|
WHERE ep.name = 'MS_Description'
|
|
AND t.name = '${tableName}'
|
|
AND c.name = '${columnName}'
|
|
)
|
|
EXEC sys.sp_updateextendedproperty
|
|
@name = N'MS_Description',
|
|
@value = N'${safeComment}',
|
|
@level0type = N'SCHEMA', @level0name = N'dbo',
|
|
@level1type = N'TABLE', @level1name = N'${tableName}',
|
|
@level2type = N'COLUMN', @level2name = N'${columnName}';
|
|
ELSE
|
|
EXEC sys.sp_addextendedproperty
|
|
@name = N'MS_Description',
|
|
@value = N'${safeComment}',
|
|
@level0type = N'SCHEMA', @level0name = N'dbo',
|
|
@level1type = N'TABLE', @level1name = N'${tableName}',
|
|
@level2type = N'COLUMN', @level2name = N'${columnName}';
|
|
END`;
|
|
}
|
|
|
|
function addColumnSql(tableName, columnName, columnType, comment) {
|
|
return `
|
|
IF COL_LENGTH('dbo.${tableName}', '${columnName}') IS NULL
|
|
ALTER TABLE dbo.${tableName} ADD ${columnName} ${columnType} NULL;
|
|
${commentSql(tableName, columnName, comment)}`;
|
|
}
|
|
|
|
async function main() {
|
|
if (!config.server || !config.database || !config.user || !config.password) {
|
|
throw new Error('Missing DB_SERVER / DB_DATABASE / DB_USER / DB_PASSWORD env vars');
|
|
}
|
|
|
|
const statements = [];
|
|
for (const table of headerTables) {
|
|
for (const [name, type, comment] of headerColumns) {
|
|
statements.push(addColumnSql(table, name, type, comment));
|
|
}
|
|
}
|
|
for (const table of detailTables) {
|
|
for (const [name, type, comment] of detailColumns) {
|
|
statements.push(addColumnSql(table, name, type, comment));
|
|
}
|
|
}
|
|
|
|
const pool = await sql.connect(config);
|
|
try {
|
|
await pool.request().batch(statements.join('\n'));
|
|
console.log('Bill schema updated successfully.');
|
|
} finally {
|
|
await pool.close();
|
|
}
|
|
}
|
|
|
|
main().catch((error) => {
|
|
console.error(error);
|
|
process.exit(1);
|
|
});
|