131 lines
5.7 KiB
JavaScript
131 lines
5.7 KiB
JavaScript
const { sequelize, Entities , Financials , Statementsmaster , Sectionsmaster , Subsectionsmaster , Itemsmaster , Brokenperiods , Entityfinyear} = require('../models')
|
|
const { logMsg } = require('../services/logger.js')
|
|
const reader = require('xlsx');
|
|
var path = require('path');
|
|
const fs = require('fs');
|
|
|
|
//Master data List Api
|
|
exports.ImportExcelData = async (req, res , filename) =>
|
|
{
|
|
try
|
|
{
|
|
const losid = req.body.los_id;
|
|
console.log(losid);
|
|
var filePath = path.resolve('storage/uploads/'+filename);
|
|
|
|
const file = reader.readFile(filePath)
|
|
|
|
var ExcelDataConstruct = new Promise(async(resolve, reject) =>
|
|
{
|
|
let data = [];
|
|
const sheets = file.SheetNames;
|
|
for(let i = 0; i < sheets.length; i++)
|
|
{
|
|
|
|
const sheetName = req.body.cin;
|
|
const SheetLevelData = reader.utils.sheet_to_json(file.Sheets[file.SheetNames[i]]);
|
|
const resultArray = [];
|
|
if(SheetLevelData.length != 0)
|
|
{
|
|
if(sheetName.toUpperCase() == 'PROPRIETORSHIP'){
|
|
where_Condition = { losid : losid , entity_type : 'PROPRIETORSHIP', probe42_data_status: 2 }
|
|
}else if(sheetName.toUpperCase() == 'PARTNERSHIP'){
|
|
where_Condition = { losid : losid , entity_type : 'PARTNERSHIP', probe42_data_status: 2 }
|
|
}else{
|
|
where_Condition = { losid : losid , cin : sheetName , probe42_data_status: 2 }
|
|
}
|
|
|
|
EntityData = await Entities.findOne({raw:true, where: where_Condition }).catch(err => {});
|
|
if(EntityData)
|
|
{
|
|
for (const obj of SheetLevelData)
|
|
{
|
|
if(obj.hasOwnProperty('item_id'))
|
|
{
|
|
const itemid = obj.item_id;
|
|
delete obj.item_id;
|
|
delete obj.itemtext;
|
|
|
|
for (const key in obj)
|
|
{
|
|
const yearValue = obj[key];
|
|
const newObj = { entity_id:EntityData.id , itemid , fy:key , value:yearValue };
|
|
resultArray.push(newObj);
|
|
}
|
|
}else{ logMsg.info("Item id Not Exist For losid = "+losid+" CIN no = "+sheetName); }
|
|
}
|
|
}else{
|
|
logMsg.info("Cin Not Exist For losid = "+losid+" CIN no = "+sheetName);
|
|
}
|
|
|
|
}else{
|
|
logMsg.info("Empty Excel Uploaded For losid = "+losid);
|
|
}
|
|
|
|
|
|
data.push(resultArray);
|
|
if(i+1 == sheets.length){ resolve(data);}
|
|
}//end of for loop // this loop for sheet level loop
|
|
});
|
|
|
|
|
|
ExcelDataConstruct.then(async(DataArray)=>
|
|
{
|
|
if(DataArray.length > 0)
|
|
{
|
|
// Using the spread operator
|
|
const mergedArray = [].concat(...DataArray);
|
|
if(mergedArray.length > 0){
|
|
var FinancialsDataUpdate = new Promise(async(resolve, reject) =>
|
|
{
|
|
mergedArray.forEach(async(value, key , array)=>{
|
|
checkIfExist = await Financials.findOne({ raw:true, where: {fy:value.fy,entity_id:value.entity_id,itemid:value.itemid,is_active:1} }).catch(err=>{ logMsg.info(err); });
|
|
if(checkIfExist)
|
|
{
|
|
await Financials.update({ "value":value.value,"sub_value":value.value},{ where: { id:checkIfExist.id }}).catch(err=>{ logMsg.info(err); });
|
|
}
|
|
else
|
|
{
|
|
logMsg.info("Value not updated for - EntityId : "+value.entity_id+" , FY : "+value.fy+",ItemId : "+value.itemid);
|
|
}
|
|
|
|
if (key === array.length -1) { resolve(DataArray); }
|
|
});
|
|
});
|
|
FinancialsDataUpdate.then(async(data)=>
|
|
{
|
|
const filteredData = data.filter(subArray => subArray.length > 0);
|
|
filteredData.forEach(async(value, key , array)=>{
|
|
var entity_id = value[0]['entity_id'];
|
|
await Entities.update( {manual_data_status:1},{where:{id:entity_id}} ).then(data=>{}).catch(err=>{ logMsg.info(err); });
|
|
});
|
|
res.send({'status':200,'message':"success",'data':"No data"});
|
|
});
|
|
|
|
}else{
|
|
res.send({'status':404,'message':"Invalid Data or Empty Excel",'data':"No data"});
|
|
}
|
|
|
|
}else{
|
|
res.send({'status':404,'message':"Invalid Data or Empty Excel",'data':"No data"});
|
|
}
|
|
|
|
});
|
|
|
|
|
|
|
|
fs.unlink(filePath, (error) => {
|
|
if (error) {
|
|
console.error('Error deleting the file:', error);
|
|
} else {
|
|
console.log('File has been deleted successfully.');
|
|
}
|
|
});
|
|
|
|
|
|
|
|
} catch(err) {
|
|
res.send({'status':404,message: err.message || "Some error occurred while retrieving BankDetails." ,'data': "No data" });
|
|
} //end of try catch
|
|
|
|
}; |