Untitled
user_4058768
plain_text
9 months ago
15 kB
6
Indexable
// Table name: SDH_SO_QUYET_DINH { id, soQuyetDinh, ten, time, idHoiDong, action, active, idDeTai, mssv } const keys = ['ID']; const obj2Db = { 'id': 'ID', 'soQuyetDinh': 'SO_QUYET_DINH', 'ten': 'TEN', 'time': 'TIME', 'idHoiDong': 'ID_HOI_DONG', 'action': 'ACTION', 'active': 'ACTIVE', 'idDeTai': 'ID_DE_TAI', 'mssv': 'MSSV' }; module.exports = app => { const db = 'main'; const tableName = 'SDH_SO_QUYET_DINH'; const type = 'table'; const schema = { ID: { type: 'NUMBER', length: '22,0', autoIncrement: true, primaryKey: true }, SO_QUYET_DINH: { type: 'NUMBER', length: '22,0' }, TEN: { type: 'NVARCHAR2', length: '40' }, TIME: { type: 'NUMBER', length: '20,0' }, ID_HOI_DONG: { type: 'NVARCHAR2', length: '20' }, ACTION: { type: 'NVARCHAR2', length: '100' }, ACTIVE: { type: 'NUMBER', length: '22,0', defaultValue: '0' }, ID_DE_TAI: { type: 'NUMBER', length: '22,0' }, MSSV: { type: 'NVARCHAR2', length: '20' } }; const methods = { 'getSqdByMssv': 'SDH_SO_QUYET_DINH_BY_MSSV', 'getSqdSearchPage': 'SDH_SO_QUYET_DINH_SEARCH_PAGE', }; app.model.sdhSoQuyetDinh = { create: (data, done) => new Promise((resolve, reject) => { let statement = '', values = '', parameter = {}; Object.keys(data).forEach(column => { if (obj2Db[column]) { statement += ', ' + obj2Db[column]; values += ', :' + column; parameter[column] = data[column]; } }); if (statement.length == 0) { done && done('Data is empty!'); reject('Data is empty!'); } else { const sql = 'INSERT INTO SDH_SO_QUYET_DINH (' + statement.substring(2) + ') VALUES (' + values.substring(2) + ')'; app.database.oracle.connection.main.executeExtra(sql, parameter, (error, resultSet) => { if (error == null && resultSet && resultSet.lastRowid) { app.model.sdhSoQuyetDinh.get({ rowId: resultSet.lastRowid }).then(item => { done && done(null, item); resolve(item); }).catch(error => { done && done(error); reject(error); }); } else { done && done(error ? error : 'Execute SQL command fail! Sql = ' + sql); reject(error ? error : 'Execute SQL command fail! Sql = ' + sql); } }); } }), get: (condition, selectedColumns, orderBy, done) => new Promise((resolve, reject) => { if (condition == undefined) { done = null; condition = {}; selectedColumns = '*'; } else if (typeof condition == 'function') { done = condition; condition = {}; selectedColumns = '*'; } else if (selectedColumns && typeof selectedColumns == 'function') { done = selectedColumns; selectedColumns = '*'; } else { selectedColumns = selectedColumns ? selectedColumns : '*'; } if (orderBy) Object.keys(obj2Db).sort((a, b) => b.length - a.length).forEach(key => orderBy = orderBy.replaceAll(key, obj2Db[key])); condition = app.database.oracle.buildCondition(obj2Db, condition, ' AND '); const parameter = condition.parameter ? condition.parameter : {}; const sql = 'SELECT ' + app.database.oracle.parseSelectedColumns(obj2Db, selectedColumns) + ' FROM (SELECT * FROM SDH_SO_QUYET_DINH' + (condition.statement ? ' WHERE ' + condition.statement : '') + (orderBy ? ' ORDER BY ' + orderBy : '') + ') WHERE ROWNUM=1'; app.database.oracle.connection.main.executeExtra(sql, parameter, (error, resultSet) => { if (error) { done && done(error); reject(error); } else { const item = resultSet && resultSet.rows && resultSet.rows.length ? resultSet.rows[0] : null; done && done(null, item); resolve(item); } }); }), getAll: (condition, selectedColumns, orderBy, done) => new Promise((resolve, reject) => { if (condition == undefined) { done = null; condition = {}; selectedColumns = '*'; } else if (typeof condition == 'function') { done = condition; condition = {}; selectedColumns = '*'; } else if (selectedColumns && typeof selectedColumns == 'function') { done = selectedColumns; selectedColumns = '*'; } else { selectedColumns = selectedColumns ? selectedColumns : '*'; } if (orderBy) Object.keys(obj2Db).sort((a, b) => b.length - a.length).forEach(key => orderBy = orderBy.replaceAll(key, obj2Db[key])); condition = app.database.oracle.buildCondition(obj2Db, condition, ' AND '); const parameter = condition.parameter ? condition.parameter : {}; const sql = 'SELECT ' + app.database.oracle.parseSelectedColumns(obj2Db, selectedColumns) + ' FROM SDH_SO_QUYET_DINH' + (condition.statement ? ' WHERE ' + condition.statement : '') + (orderBy ? ' ORDER BY ' + orderBy : ''); app.database.oracle.connection.main.executeExtra(sql, parameter, (error, resultSet) => { if (error) { done && done(error); reject(error); } else { const items = resultSet && resultSet.rows ? resultSet.rows : []; done && done(null, items); resolve(items); } }); }), getPage: (pageNumber, pageSize, condition, selectedColumns, orderBy, done) => new Promise((resolve, reject) => { if (condition == undefined) { done = null; condition = {}; selectedColumns = '*'; } else if (typeof condition == 'function') { done = condition; condition = {}; selectedColumns = '*'; } else if (selectedColumns && typeof selectedColumns == 'function') { done = selectedColumns; selectedColumns = '*'; } else { selectedColumns = selectedColumns ? selectedColumns : '*'; } if (orderBy) Object.keys(obj2Db).sort((a, b) => b.length - a.length).forEach(key => orderBy = orderBy.replaceAll(key, obj2Db[key])); condition = app.database.oracle.buildCondition(obj2Db, condition, ' AND '); let leftIndex = (pageNumber <= 1 ? 0 : pageNumber - 1) * pageSize, parameter = condition.parameter ? condition.parameter : {}; const sqlCount = 'SELECT COUNT(*) FROM SDH_SO_QUYET_DINH' + (condition.statement ? ' WHERE ' + condition.statement : ''); app.database.oracle.connection.main.executeExtra(sqlCount, parameter, (error, res) => { if (error) { done && done(error); reject(error); } else { let result = {}; let totalItem = res && res.rows && res.rows[0] ? res.rows[0]['COUNT(*)'] : 0; result = { totalItem, pageSize, pageTotal: Math.ceil(totalItem / pageSize) }; result.pageNumber = Math.max(1, Math.min(pageNumber, result.pageTotal)); leftIndex = Math.max(0, result.pageNumber - 1) * pageSize; const sql = 'SELECT ' + app.database.oracle.parseSelectedColumns(obj2Db, selectedColumns) + ' FROM (SELECT SDH_SO_QUYET_DINH.*, ROW_NUMBER() OVER (ORDER BY ' + (orderBy ? orderBy : keys) + ') R FROM SDH_SO_QUYET_DINH' + (condition.statement ? ' WHERE ' + condition.statement : '') + ') WHERE R BETWEEN ' + (leftIndex + 1) + ' and ' + (leftIndex + pageSize); app.database.oracle.connection.main.executeExtra(sql, parameter, (error, resultSet) => { if (error) { done && done(error); reject(error); } else { result.list = resultSet && resultSet.rows ? resultSet.rows : []; done && done(null, result); resolve(result); } }); } }); }), update: (condition, changes, done) => new Promise((resolve, reject) => { condition = app.database.oracle.buildCondition(obj2Db, condition, ' AND '); changes = app.database.oracle.buildCondition(obj2Db, changes, ', ', 'NEW_'); if (Object.keys(condition).length == 0) { done && done('No condition!'); reject('No condition!'); } else if (changes.statement) { const parameter = app.clone(condition.parameter ? condition.parameter : {}, changes.parameter ? changes.parameter : {}); const sql = 'UPDATE SDH_SO_QUYET_DINH SET ' + changes.statement + (condition.statement ? ' WHERE ' + condition.statement : ''); app.database.oracle.connection.main.executeExtra(sql, parameter, (error, resultSet) => { if (error == null && resultSet && resultSet.lastRowid) { app.model.sdhSoQuyetDinh.get({ rowId: resultSet.lastRowid }).then(item => { done && done(null, item); resolve(item); }).catch(error => { done && done(error); reject(error); }); } else { done && done(error); reject(error); } }); } else { done && done('No changes!'); reject('No changes!'); } }), delete: (condition, done) => new Promise((resolve, reject) => { if (condition == undefined) { done = null; condition = {}; } else if (typeof condition == 'function') { done = condition; condition = {}; } condition = app.database.oracle.buildCondition(obj2Db, condition, ' AND '); if (Object.keys(condition).length == 0) { done && done('No condition!'); reject('No condition!'); } else { const parameter = condition.parameter ? condition.parameter : {}; const sql = 'DELETE FROM SDH_SO_QUYET_DINH' + (condition.statement ? ' WHERE ' + condition.statement : ''); app.database.oracle.connection.main.executeExtra(sql, parameter, error => { if (error) { done && done(error); reject(error); } else { done && done(); resolve(); } }); } }), count: (condition, done) => new Promise((resolve, reject) => { if (condition == undefined) { done = null; condition = {}; } else if (typeof condition == 'function') { done = condition; condition = {}; } condition = app.database.oracle.buildCondition(obj2Db, condition, ' AND '); const parameter = condition.parameter ? condition.parameter : {}; const sql = 'SELECT COUNT(*) FROM SDH_SO_QUYET_DINH' + (condition.statement ? ' WHERE ' + condition.statement : ''); app.database.oracle.connection.main.executeExtra(sql, parameter, (error, result) => { if (error) { done && done(error); reject(error); } else { done && done(null, result); resolve(result); } }); }), getSqdByMssv: (masosinhvien, done) => new Promise((resolve, reject) => { app.database.oracle.connection.main.executeExtra('BEGIN :ret:=sdh_so_quyet_dinh_by_mssv(:masosinhvien); END;', { ret: { dir: app.database.oracle.BIND_OUT, type: app.database.oracle.CURSOR }, masosinhvien }, (error, result) => app.database.oracle.fetchRowsFromCursor(error, result, (error, result) => { if (error) { done && done(error); reject(error); } else { done && done(null, result); resolve(result); } })); }), searchPage: (pagenumber, pagesize, filter, done) => new Promise((resolve, reject) => { app.database.oracle.connection.main.executeExtra('BEGIN :ret:=sdh_so_quyet_dinh_search_page(:pagenumber, :pagesize, :filter, :totalitem, :pagetotal); END;', { ret: { dir: app.database.oracle.BIND_OUT, type: app.database.oracle.CURSOR }, pagenumber: { val: pagenumber, dir: app.database.oracle.BIND_INOUT, type: app.database.oracle.NUMBER }, pagesize: { val: pagesize, dir: app.database.oracle.BIND_INOUT, type: app.database.oracle.NUMBER }, filter, totalitem: { dir: app.database.oracle.BIND_OUT, type: app.database.oracle.NUMBER }, pagetotal: { dir: app.database.oracle.BIND_OUT, type: app.database.oracle.NUMBER } }, (error, result) => app.database.oracle.fetchRowsFromCursor(error, result, (error, result) => { if (error) { done && done(error); reject(error); } else { done && done(null, result); resolve(result); } })); }), }; return { db, tableName, type, schema, methods, keys }; };
Editor is loading...
Leave a Comment