// massive-of-json-to-db
var oracledb = require('oracledb'); // To DB
var moment = require('moment');
const fs = require("fs"); // FS
var FILEIN = 'avgprice_bd-prepared.json';
var YEAR = 2019;
var QUARTERNUMBER = 1;
var locationId;
var fileContent = fs.readFileSync(FILEIN, "utf8"); // File with massive of objects
var maxId = 0; // !!
oracledb.autoCommit = true;
// Date-time locale initial
moment.locale('ru');
var dateOptions = {
year: 'numeric',
month: 'long',
day: 'numeric',
hour: 'numeric',
minute: 'numeric',
second: 'numeric'
};
var intoQueryStr = ` INTO TEMP_AVG_PRICE (ID_CRE_RECORD, DATETIME, QUARTER, "YEAR", state, locality,
offices_business_center,
offices_other,
retail_shopping_center,
retail_street,
retail_other,
warehouse_complex,
warehouse_other,
land_living,
land_commercial,
land_industrial) VALUES `
// Check hyphen, replace to null
var replaceHyphen = (element) => ( element == '-'? null : element );
var arr = fileStringToMassiveOfObjects(fileContent);
var queries; // String of queries
oracledb.getConnection(
{
user : "system",
password : "*",
connectString : "localhost/orz" //
},
function (err, connection) {
if (err) {
console.error(err.message);
return;
}
console.log('go');
queries = `INSERT ALL \n`;
//arr.length
arr.forEach(function(item, i, arr) {
if(i<2){
var now = new Date();
var d = moment().format('L');
d += " " + moment().format('LTS');
// Form string with query
queries += ` ${intoQueryStr} (${maxId+i+1}, TO_timestamp('${d}', 'DD.MM.YYYY HH24:MI:SS'),
${QUARTERNUMBER}, ${YEAR},
'${replaceHyphen(arr[i]['Регион (субъект РФ)'])}',
'${replaceHyphen(arr[i]['Населенный пункт (район)'])}',
${replaceHyphen(arr[i]['Бизнес-Центры (помещения в них)'])},
${replaceHyphen(arr[i]['Прочие офисные'])},
${replaceHyphen(arr[i]['Торговые центры (помещения в них)'])},
${replaceHyphen(arr[i]['Street retail (ПСН)'])},
${replaceHyphen(arr[i]['Прочие торговые'])},
${replaceHyphen(arr[i]['Профессиональные производственно-складские комплексы (логистика)'])},
${replaceHyphen(arr[i]['Прочие производственно-складские'])},
${replaceHyphen(arr[i]['ИЖС'])},
${replaceHyphen(arr[i]['Коммерция'])},
${replaceHyphen(arr[i]['Производство'])} )
\n`
};
});
queries += ` SELECT 1 FROM DUAL`
console.log( queries );
connection.execute(
queries,
{},
{ autoCommit: true },
function (err, result) {
console.log('Handling result');
if (err) {
console.error(err.message);
return;
}else{
console.log('No errors');
}
console.log('result = ' + result);
console.log('result.rowsAffected = ' + result.rowsAffected);
connection.release(
function (err) {
console.log('In func connection.release')
if (err) {
console.log("release error1");
console.error(err.message);
doRelease(connection);
throw(err);
} else {
console.log("released connection")
doRelease(connection);
};
}); // end release
}); // end function
console.log('End close')
console.log('End func')
});
console.log('End script')
// From file-string to massive of objects
function fileStringToMassiveOfObjects(fileContent){
var arr = fileContent.split('},');
arr[0] = arr[0].substr(1); // Del first char [
arr[arr.length - 1] = arr[arr.length - 1].slice(0, -1); // Del end char ]
// Passage through the array
/* item – очередной элемент массива.
i – его номер.
arr – массив, который перебирается. */
arr.forEach(function(item, i, arr) {
if (i != (arr.length - 1)){
item += '}'; // Add char } to the end of element
};
arr[i] = JSON.parse(item);
});
return arr;
};
function doRelease(connection) {
connection.close(
function(err) {
if (err)
console.error(err.message);
});
}
------------------------------------------------------------------------------------------------------------------------------
Результат сформированной строки queries :
INSERT ALL
INTO TEMP_AVG_PRICE (ID_CRE_RECORD, DATETIME, QUARTER, "YEAR", state, locality,
offices_business_center,
offices_other,
retail_shopping_center,
retail_street,
retail_other,
warehouse_complex,
warehouse_other,
land_living,
land_commercial,
land_industrial) VALUES (1, TO_timestamp('04.02.2019 16:57:39', 'DD.MM.YYYY HH24:MI:SS'),
1, 2019,
'Белгородская область',
'Белгород',
56500,
41500,
63300,
55600,
48500,
null,
11900,
960,
3460,
1110 )
INTO TEMP_AVG_PRICE (ID_CRE_RECORD, DATETIME, QUARTER, "YEAR", state, locality,
offices_business_center,
offices_other,
retail_shopping_center,
retail_street,
retail_other,
warehouse_complex,
warehouse_other,
land_living,
land_commercial,
land_industrial) VALUES (2, TO_timestamp('04.02.2019 16:57:39', 'DD.MM.YYYY HH24:MI:SS'),
1, 2019,
'Белгородская область',
'Старый Оскол',
null,
32200,
null,
38600,
30700,
null,
6500,
660,
1390,
450 )
SELECT 1 FROM DUAL