const { sequelize, Entities , Financials , Statementsmaster  , Sectionsmaster , Subsectionsmaster , Itemsmaster , Brokenperiods , Entityfinyear} = require('../models')
const { logMsg } = require('../services/logger.js');
const  financialControllerHelper = require('../services/financial.controller.helper.js');
const  camundaHelper = require('../services/camunda.helper.js');
const  ApiCallHelper = require('../services/financials.apicall.helper.js');
const  Probe42DataMappingHelper = require('../services/probe42lineitem.mapping.helper.js');
const Sync = require('sync');
const { Op } = require("sequelize");




// all master data list for dropdown
exports.MasterDatas = async (req, res) => {
   await Statementsmaster.findAll({
                                    where :{is_active : 1} ,
                                    attributes: ['id', 'statement', 'is_active'],
                                    include :[{ 
                                                association: 'sectionsmasterData' , 
                                                where :{is_active : 1} ,
                                                attributes: ['id', 'section', 'is_active'],
                                                include :[{ 
                                                            association: 'subsectionsmasterData' , 
                                                            where :{is_active : 1} ,
                                                            attributes: ['id', 'subsection', 'is_active'],
                                                            required :false
                                                          }],  
                                                required :false
                                              }],
                                    required :false 
                                    })
 .then(data => 
      {
        res.send({'status':200,'message':"success",'statementsmasterrData':data});
        logMsg.info("Master Data List api call Success");
     })
        .catch(err => {
              res.status(404).send({'status':404,
              message: err.message || "Some error occurred while retrieving statements." ,'data': "No data" });
                      });
};
















exports.MenuList = async (req, res) => { 
  Sync(function()
  {
    try
    {
    const losid = req.query.losid;
     
     var EntityInsert = new Promise((resolve, reject) => {
      if(req.query.redirection_api_call == 1)
      { 
     //Redirection api call with losid
     redirectionApi = ApiCallHelper.redirectionApiCall(losid);
     redirectionApi.then(function(redirectionApiData){ 
        if(redirectionApiData == false)
        { 

          resolve(); 
          logMsg.info("Redirection api data fetching failed for losid = "+losid);
        } 
        else 
        {
          logMsg.info("Redirection api data fetched successfully for losid = "+losid);
          // insert pro42data into entities table using using foreach 
          redirectionApiData.forEach((Pro42Datavalue, key,array)=>{ 
           
           let cin_number = Pro42Datavalue.ciN_LLPIN.toUpperCase();
          // check if the Pro42Data (entity) already inserted
          Entities.findOne({ where: { losid : Pro42Datavalue.loginNo , pan : Pro42Datavalue.pan , cin : cin_number } }).then(Entity => { 
              if(Entity)
              { 
                // skip inserting 
                if (key === array.length -1) {resolve();}
              }
              else
              {
              
              Entities.create({ "losid":Pro42Datavalue.loginNo,"pan":Pro42Datavalue.pan,"cin":cin_number,"bizname":Pro42Datavalue.entityName,"entity_type":Pro42Datavalue.entityType,"probe42_data_status":1,"is_verified":0,"createdby":Pro42Datavalue.createdby})
              .then(Entitydata => { 
                logMsg.info("entity created , data:"+Entitydata);
                const cin =  Entitydata.dataValues.cin;
                if(cin != "")
                {
                //process instance Id update
                const entityid = Entitydata.dataValues.id;
                const user = "SMC";
                processInstanceId =  camundaHelper.startFinancialsProcessInstance(Entitydata.dataValues.losid,Entitydata.dataValues.pan,user);
                processInstanceId.then(function(InstanceId){
                  Entities.update({ "process_instance_id":InstanceId },{ where: { id: entityid }}).then(processInstanceIdData =>{
                   // resolve here
                   if (key === array.length -1) {   resolve();  } }).catch(err=>{  logMsg.info("camunda process_instance_id creation for entity_id= "+entityid); });  
                });
            
                // call and get financial year wise data for entity (probe42 api call)
                  financials_data_api_response = ApiCallHelper.probe42ApiCall(cin);
                  financials_data_api_response.then(function(result){
                  if(result == false)
                  {
                    // create skeleton here
                    financialControllerHelper.createSkeleton(losid,entityid);
                  }
                  else
                  {
                  //filter lineitems from api responce and construct to needed format  
                  lineItem_data = Probe42DataMappingHelper.lineItemFetching(result);
                  lineItem_data.then(function(lineItem_result){ 
                    
                    //create financial table and entityfinyear table record here
                    lineItem_result.forEach((value, key)=>{ 
                  
                            Entityfinyear.create({ "losid":Entitydata.dataValues.losid,"entityid":Entitydata.dataValues.id,"FY":value.year,"st_type": 0 })
                            .then(Entityfinyearsdata => {
                                
                                  // create skeletion with itemmaster data (itemmaster(id) == financial(itemid))
                                  Itemsmaster.findAll({ where :{pvtltd:1,is_active : 1},attributes: ['id'] , order: [['statement', 'ASC'],['section', 'ASC'],['pvtltd_itemorder', 'ASC']] })
                                  .then(ItemsmasterData => {
                                    //construct lineitem array for bulk insert using itemmaster primarykey (id)
                                    var SkeletonInsert = new Promise((resolve, reject) => {
                                      let fin_data_array = [];
                                      ItemsmasterData.forEach( (itemvalue , index , itemvaluearray) => {
                                        fin_data_obj = { "entity_id":Entityfinyearsdata.dataValues.entityid,"st_type":Entityfinyearsdata.dataValues.st_type,"itemid":itemvalue.dataValues.id,"fy":Entityfinyearsdata.dataValues.FY,"value":0,"is_active":1,"createdby":4}
                                        fin_data_array.push(fin_data_obj);
                                        if (index === itemvaluearray.length -1) {   resolve(fin_data_array);  }
                                      })
                                    });
                                    SkeletonInsert.then((Query_data)=>{
                                      Financials.bulkCreate(Query_data).then(data=>{
                                        //line item data update
                                        //  mapping of probe42 api lineItem with itemMaster primary key
                                        final_array = Probe42DataMappingHelper.lineItemMapping(value,entityid);
                                        final_array.then(function(update_array_data){ 
                                          // update mapped lineitem value here
                                          Probe42DataMappingHelper.lineItemUpdate(update_array_data);
                                        });//end of lineItemUpdate helper call
                                      }).catch(err=>(console.log(err))); //skeleton bulk create query
                                    });
                                  }).catch(err=>(console.log(err))); //itemmaster query


                                
                                }).catch(err => {
                                res.status(404).send({'status':404,
                                  message: err.message || "Some error occurred while insertinging statements." ,'data': "No data" });
                              }); // end of entity fin year table data insert
                
                          });//end of line item foreach
                       });//end of lineItem filter from api response helper call
                     } // end of probe42 api responce success else condition
                    });//end of probe42 api call
                   } else {
                     // resolve here
                    if (key === array.length -1) {   resolve();  }
                    logMsg.info("CIN no not exist for losid  = "+losid+" and entity name ="+Entitydata.dataValues.bizname);
                   }

                   
                    
                  }).catch(err => {
                    res.status(404).send({'status':404,
                      message: err.message || "Some error occurred while insertinging statements." ,'data': "No data" });
                  }); // end of entity insert

                } //end of else condition
            
            }); // end of (losid,pan,cin) combination checking entity data exist or not 
     
          }); // end of Redirection api data foreach
        } // end of redirectionApiData else condition
      }) // end of Redirection api call      
     } else { 
      resolve(); 
      logMsg.info("redirection api call not allowed because redirection_api_call = 0 ");
      }
    });  // end of promise
    


    

    EntityInsert.then(async() => {
      
      Statementsmaster.findAll({ where :{is_active : 1} , include: { all: true, nested: true , where :{is_active : 1} , required:false}})
        .then(MasterData => {
      Entities.findAll({where : { losid } , attributes: [ 'id', 'losid' , 'pan' , 'cin' , 'bizname' , 'entity_type' , 'activity_group' , 'biz_act_desc' , 'process_instance_id' , 'probe42_data_status' ,'is_verified' ], order: [['id', 'ASC']] })
        .then(async(EntitiesData) => {
         
      
      let Time = 2000;
      let data = [];
        
      //EntitiesData.forEach((value, key)=>{ 
            // Time = Time + 500;
            // instancesid = value.dataValues.process_instance_id;
            // taskid = camundaHelper.financialsListOfProcessInstances(instancesid); 
            // taskid.then(function(result){ 
            //       Time = Time + 500; 
            //       formVariable = camundaHelper.financialsFormVariables(result); 
            //       formVariable.then(function(form_result){  
            //             formVariableResult = JSON.parse(form_result);
            //             if(formVariableResult.type){ camunda_status = {"process_status":"NULL"}; } else { camunda_status =JSON.parse(form_result); }
            //             obj_data = { ...value.dataValues ,status:camunda_status , statementsmasterrData:MasterData }
            //             data.push(obj_data);
            //       }); 
            // });
      //}); // end of EntitiesData foreach   
      
      for(const value of EntitiesData)
           {
             Time = Time + 500;
             instancesid = value.dataValues.process_instance_id;
             taskid = await camundaHelper.financialsListOfProcessInstances(instancesid); 
                  Time = Time + 500; 
                  formVariable = await camundaHelper.financialsFormVariables(taskid); 
                  formVariableResult = JSON.parse(formVariable);
                  if(formVariableResult.type){ camunda_status = {"process_status":"NULL"}; } else { camunda_status =JSON.parse(formVariable); }
                  obj_data = { ...value.dataValues ,status:camunda_status , statementsmasterrData:MasterData }
                  data.push(obj_data);      
           } // end of for loop
  
      setTimeout(()=>{
        if(data.length > 1)
        {
          // below code for overall sum object push process 
          sum_data = { ...{'losid':losid,'bizname':"Consolidated Financials Summary",'is_consolidated_api_call':1} , statementsmasterrData:MasterData }; 
          data.push(sum_data);
          // send respose to FE
          res.send({'status':200,'message':"success",'data':data});
        }
        else if(data.length == 1)
        {
          // send respose to FE
          res.send({'status':200,'message':"success",'data':data});
        }
        else
        {
          res.send({'status':404,'message':"failed",'data':"data not found"});
        }
      
      },Time)
      });  // end of findall EntitiesData 
      }); // end of findall masters datas
    
    });

    } catch(err) {
    res.status(500).send({'status':404,
      message: err.message || "Some error occurred while inserting statements." ,'data': "No data"
     }); } // end of try catch

  }); // end of sync function

  };














// FinantialsSummary
// exports.FinantialsSummary = async (req, res) => {
//   try
//   { 
// const id = req.query.entityid
// Entities.findOne({where : { id } , attributes: [ 'id','losid','process_instance_id' , 'entity_type' , 'probe42_data_status' ] })
// .then(EntitiesData => { 
//   const instancesid = EntitiesData.dataValues.process_instance_id; 
//   const probe42_data_status = EntitiesData.dataValues.probe42_data_status; 
//   if(probe42_data_status == 0){order_by_key = "pvtltd_itemorder"}else if(probe42_data_status == 2){order_by_key = "itemorder"}else{order_by_key = "NULL"}
// sequelize.query("select a.losid,a.entityid,a.FY,a.st_type as st_type_id, b.statement_name as st_type_name,a.additional_year_type, a.probe42_data_status FROM entity_fin_year a LEFT JOIN (SELECT id,statement_name FROM statement_type) b ON a.st_type = b.id WHERE entityid ="+id+" order by a.fy desc")
// .then(EntityfinyearData => { 
//   if(EntityfinyearData[0].length != 0)
//   {
//     const data = EntityfinyearData[0];
//     const year_1 = EntityfinyearData[0][0].FY;
//     const additional_year_type = EntityfinyearData[0][0].additional_year_type;
//     if(EntitiesData.dataValues.entity_type == "COMPANY"){entity_type_key = "pvtltd"} else if(EntitiesData.dataValues.entity_type == "INDIVIDUAL"){entity_type_key = "llp"} else {entity_type_key = "others"}
//     const years = {losid:EntitiesData.dataValues.losid,entityid:EntitiesData.dataValues.id,order_by_key:order_by_key,probe42_data_status:probe42_data_status,additional_year_type:additional_year_type};
//     formula_obj = {};
//     // below  function call is for calculation of additional new year flag generation 
//     add_new_FY_flag =  financialControllerHelper.addNewFyFlag(year_1);
//     // below code for constructing query for dynamic financial years
//     var select_items = "";
//     var sub_query = "";
//     data.forEach((value, key)=>{
//       const words = ['','one','two','three','four', 'five','six','seven','eight','nine','ten'];
//       // years object construction
//       year_count = key + 1;
//       years["Y"+year_count] = value.FY;
//       years["Y"+year_count+"_st_type_id"] = value.st_type_id;
//       years["Y"+year_count+"_st_type_name"] = value.st_type_name;
//       years["Y"+year_count+"_probe42_data_fetched"] = value.probe42_data_status;
//       // formula helper obj construct here for FE calculation 
//       formula_obj[value.FY] = {"value_key" : "Y"+year_count+"_value"};
//       // fin_query construction
//         if(key != 0)
//         {
//           key_addition = key + 1;
//           alisename = words[key_addition];
//           select_items += ","+alisename+".st_type as Y"+key_addition+"_st_type,"+alisename+".fy as Y"+key_addition+","+alisename+".id  Y"+key_addition+"_id ,"+alisename+".is_editable as Y"+key_addition+"_is_editable ,"+alisename+".value Y"+key_addition+"_value,"+alisename+".sub_value Y"+key_addition+"_sub_value";
//           sub_query += "LEFT JOIN ( SELECT id,entity_id, itemid, value,sub_value,fy,is_active,st_type,is_editable FROM financials WHERE fy = '"+value.FY+"' and entity_id = "+id+" and is_active = 1) "+alisename+" ON a.entity_id = "+alisename+".entity_id and a.itemid = "+alisename+".itemid ";
//         }
//         });  // end of foreach
       
//   var query = "Select a.entity_id,x.id as statement_id,x.statement as statement,y.id as section_id,y.section as section,w.subsection as subsection_id,w.itemkey,w.itemorder,w.pvtltd_itemorder,z.subsection as subsection , z.itemtext as subsection_itemtext ,a.itemid,w.itemtext as items,w.formula,w.is_calculated,w.is_summary,w.pvtltd,w.llp,w.others,a.st_type as Y1_st_type,a.fy as Y1,a.id as Y1_id ,a.is_editable as Y1_is_editable,a.value  as Y1_value,a.sub_value  as Y1_sub_value "+ select_items +",v.remarks , v.id as remarks_id FROM financials a LEFT JOIN ( SELECT id,entity_id,itemid,remarks FROM fin_remarks WHERE is_active = 1) v ON a.entity_id = v.entity_id and a.itemid = v.itemid JOIN ( SELECT id,itemtext,statement,section,subsection,itemkey,itemorder,pvtltd_itemorder,formula,is_calculated,is_summary,pvtltd,llp,others FROM itemsmaster ) w ON a.itemid = w.id JOIN ( SELECT id, statement FROM statementsmaster ) x ON w.statement = x.id JOIN ( SELECT id, section FROM sectionsmaster ) y ON w.section = y.id LEFT JOIN ( SELECT id, subsection , itemtext FROM subsectionsmaster ) z ON w.subsection = z.id "+ sub_query +"WHERE a.fy = '"+year_1+"' and a.entity_id = "+id+" and a.is_active = 1 order by w.statement asc , w.section asc , w.subsection ";
//     setTimeout(function(){ 
//       sequelize.query(query).then(summary => { console.log(query)
//           // is_summary filter from fin_summary data
//           let is_summary_item_array = [];
//           const fin_summary = summary[0];
//           fin_summary.forEach((value, key)=>{ 
//             if(value.is_summary == 1)
//             { 
//               obj_data = value;
//               is_summary_item_array.push(obj_data);
//             }
//           });
//           // get camunda status
//           taskid = camundaHelper.financialsListOfProcessInstances(instancesid); 
//           taskid.then(function(result){
//           formVariable = camundaHelper.financialsFormVariables(result);
//           formVariable.then(function(form_result){  
//           formVariableResult = JSON.parse(form_result);
//           if(formVariableResult.type){ camunda_status = {"process_status":"NULL"}; } else { camunda_status =JSON.parse(form_result); }
//           setTimeout(function()
//           { 
//           res.send({'status':200,'message':"success",'data':{"years" : years , formula_obj : formula_obj ,"add_new_finyear_flag" : add_new_FY_flag , status:camunda_status,"summary" : is_summary_item_array , "fin_summary" : summary } });
//           }, 3000);
//           }); // enf of formVariable api call
//           }); // enf of taskid api call
          
         
//         }).catch(err => {
//         res.status(500).send({'status':404,
//           message: err.message || "Some error occurred while retrieving tutorials." ,'data': "No data" });
//         }); 

//       }, 2000);// end of set time out
//     } else {  res.status(404).send({'status':404,message: "Financials data not found." ,'data': "No data" });}

//     }) // end of EntityfinyearData query
  
//   }) // end of EntitiesData query
//   } catch(err) {
//   res.status(500).send({'status':404,
//     message: err.message || "Some error occurred while inserting statements." ,'data': "No data"
//    }); }
// };




exports.FinantialsSummary = async (req, res) => {
  try
  { 
const id = req.query.entityid
Entities.findOne({where : { id } , attributes: [ 'id','losid','process_instance_id' , 'entity_type' , 'probe42_data_status' ] })
.then(async(EntitiesData) => { 
  const instancesid = EntitiesData.dataValues.process_instance_id; 
  const probe42_data_status = EntitiesData.dataValues.probe42_data_status; 
  if(probe42_data_status == 0){order_by_key = "pvtltd_itemorder"}else if(probe42_data_status == 2){order_by_key = "itemorder"}else{order_by_key = "NULL"}
sequelize.query("select a.losid,a.entityid,a.FY,a.st_type as st_type_id, b.statement_name as st_type_name,a.additional_year_type, a.probe42_data_status FROM entity_fin_year a LEFT JOIN (SELECT id,statement_name FROM statement_type) b ON a.st_type = b.id WHERE entityid ="+id+" order by a.fy desc")
.then(async(EntityfinyearData) => { 
  if(EntityfinyearData[0].length != 0)
  {
    const data = EntityfinyearData[0];
    const year_1 = EntityfinyearData[0][0].FY;
    const additional_year_type = EntityfinyearData[0][0].additional_year_type;
    if(EntitiesData.dataValues.entity_type == "COMPANY"){entity_type_key = "pvtltd"} else if(EntitiesData.dataValues.entity_type == "INDIVIDUAL"){entity_type_key = "llp"} else {entity_type_key = "others"}
    const years = {losid:EntitiesData.dataValues.losid,entityid:EntitiesData.dataValues.id,order_by_key:order_by_key,probe42_data_status:probe42_data_status,additional_year_type:additional_year_type};
    formula_obj = {};
    // below  function call is for calculation of additional new year flag generation 
    add_new_FY_flag =  financialControllerHelper.addNewFyFlag(year_1);
    // below code for constructing query for dynamic financial years
    var select_items = "";
    var sub_query = "";
    data.forEach((value, key)=>{
      const words = ['','one','two','three','four', 'five','six','seven','eight','nine','ten'];
      // years object construction
      year_count = key + 1;
      years["Y"+year_count] = value.FY;
      years["Y"+year_count+"_st_type_id"] = value.st_type_id;
      years["Y"+year_count+"_st_type_name"] = value.st_type_name;
      years["Y"+year_count+"_probe42_data_fetched"] = value.probe42_data_status;
      // formula helper obj construct here for FE calculation 
      formula_obj[value.FY] = {"value_key" : "Y"+year_count+"_value"};
      // fin_query construction
        if(key != 0)
        {
          key_addition = key + 1;
          alisename = words[key_addition];
          select_items += ","+alisename+".st_type as Y"+key_addition+"_st_type,"+alisename+".fy as Y"+key_addition+","+alisename+".id  Y"+key_addition+"_id ,"+alisename+".is_editable as Y"+key_addition+"_is_editable ,"+alisename+".value Y"+key_addition+"_value,"+alisename+".sub_value Y"+key_addition+"_sub_value";
          sub_query += "LEFT JOIN ( SELECT id,entity_id, itemid, value,sub_value,fy,is_active,st_type,is_editable FROM financials WHERE fy = '"+value.FY+"' and entity_id = "+id+" and is_active = 1) "+alisename+" ON a.entity_id = "+alisename+".entity_id and a.itemid = "+alisename+".itemid ";
        }
        });  // end of foreach
       
  var query = "Select a.entity_id,x.id as statement_id,x.statement as statement,y.id as section_id,y.section as section,w.subsection as subsection_id,w.itemkey,w.itemorder,w.pvtltd_itemorder,z.subsection as subsection , z.itemtext as subsection_itemtext ,a.itemid,w.itemtext as items,w.formula,w.is_calculated,w.is_summary,w.pvtltd,w.llp,w.others,a.st_type as Y1_st_type,a.fy as Y1,a.id as Y1_id ,a.is_editable as Y1_is_editable,a.value  as Y1_value,a.sub_value  as Y1_sub_value "+ select_items +",v.remarks , v.id as remarks_id FROM financials a LEFT JOIN ( SELECT id,entity_id,itemid,remarks FROM fin_remarks WHERE is_active = 1) v ON a.entity_id = v.entity_id and a.itemid = v.itemid JOIN ( SELECT id,itemtext,statement,section,subsection,itemkey,itemorder,pvtltd_itemorder,formula,is_calculated,is_summary,pvtltd,llp,others FROM itemsmaster ) w ON a.itemid = w.id JOIN ( SELECT id, statement FROM statementsmaster ) x ON w.statement = x.id JOIN ( SELECT id, section FROM sectionsmaster ) y ON w.section = y.id LEFT JOIN ( SELECT id, subsection , itemtext FROM subsectionsmaster ) z ON w.subsection = z.id "+ sub_query +"WHERE a.fy = '"+year_1+"' and a.entity_id = "+id+" and a.is_active = 1 order by w.statement asc , w.section asc , w.subsection ";
   // get camunda status
   taskid = await  camundaHelper.financialsListOfProcessInstances(instancesid); 
   formVariable = await camundaHelper.financialsFormVariables(taskid);
   formVariableResult = JSON.parse(formVariable);
   if(formVariableResult.type){ camunda_status = {"process_status":"NULL"}; } else { camunda_status =JSON.parse(formVariable); }
  
   // execute constructed financial summary query
      sequelize.query(query).then(async(summary) => { 
          // is_summary filter from fin_summary data
          let is_summary_item_array = [];
          const fin_summary = summary[0];
          fin_summary.forEach((value, key)=>{ 
            if(value.is_summary == 1)
            { 
              obj_data = value;
              is_summary_item_array.push(obj_data);
            }
          });
         
          
          res.send({'status':200,'message':"success",'data':{"years" : years , formula_obj : formula_obj ,"add_new_finyear_flag" : add_new_FY_flag , status:camunda_status,"summary" : is_summary_item_array , "fin_summary" : summary } });
        
        }).catch(err => {
        res.status(500).send({'status':404,
          message: err.message || "Some error occurred while retrieving tutorials." ,'data': "No data" });
        }); 

      
    } else {  res.status(404).send({'status':404,message: "Financials data not found." ,'data': "No data" });}

    }) // end of EntityfinyearData query
  
  }) // end of EntitiesData query
  } catch(err) {
  res.status(500).send({'status':404,
    message: err.message || "Some error occurred while inserting statements." ,'data': "No data"
   }); }
};










//ConsolidatedSummary
exports.ConsolidatedSummary = async (req, res) => { 

  try
  { 
    const losid = req.query.losid
    Entityfinyear.findAll({where : { losid  } , attributes: [ [sequelize.fn('DISTINCT', sequelize.col('FY')), 'FY'] ] , order: [['FY', 'DESC']] })
  .then(EntityfinyearData => {
      const year_1 = EntityfinyearData[0].FY;
      var select_items = "";
      var sub_query = "";
      var group = "";
      EntityfinyearData.forEach((value,key)=>{
      const words = ['','one','two','three','four', 'five','six','seven','eight','nine','ten'];
        if(key != 0)
        {
          key_addition = key + 1;
          alisename = words[key_addition];
          select_items += ","+alisename+".fy as Y"+key_addition+" ,SUM("+alisename+".value) as Y"+key_addition+"_value",
          sub_query += " JOIN(SELECT  entity_id,itemid,fy,value FROM financials WHERE fy = '"+value.FY+"' and is_active = 1 GROUP BY itemid,fy,is_active,value,entity_id) "+alisename+ " ON z.id = " + alisename + ".entity_id and a.itemid = "+ alisename +".itemid "
          group += ","+alisename+".fy";
        }
        });  // end of foreach
      
        var query = "Select x.id as statement_id,x.statement as statement,y.id as section_id,y.section as section,e.subsection as subsection_id,g.subsection as subsection , g.itemtext as subsection_itemtext, a.itemid,e.itemtext as items,e.is_summary,e.itemorder,e.pvtltd_itemorder,a.fy as Y1 ,  SUM(a.value) as Y1_value"+ select_items +" from entities z  JOIN(SELECT  entity_id,itemid,fy,value FROM financials WHERE fy = '"+year_1+"'and is_active = 1 GROUP BY itemid,fy,is_active,value,entity_id) a ON z.id = a.entity_id" + sub_query + " JOIN ( SELECT id,itemtext,statement,section,subsection,formula,is_calculated,is_summary,itemorder,pvtltd_itemorder FROM itemsmaster ) e ON a.itemid = e.id JOIN ( SELECT id, statement FROM statementsmaster ) x ON e.statement = x.id JOIN ( SELECT id, section FROM sectionsmaster ) y ON e.section = y.id LEFT JOIN ( SELECT id, subsection , itemtext FROM subsectionsmaster ) g ON e.subsection = g.id where losid = '"+losid+"' GROUP BY a.itemid,x.id,x.statement,y.id,y.section,e.subsection,g.subsection,g.itemtext,e.itemtext,e.statement,e.section,e.id,a.fy,e.is_summary,e.itemorder,e.pvtltd_itemorder " + group + " ORDER BY e.statement asc , e.section asc "
        
    setTimeout(function(){ 
      sequelize.query(query)
        .then(summary => {
          let is_summary_item_array = [];
          const summary_data = summary[0];
          summary_data.forEach((value, key)=>{ 
            if(value.is_summary == 1)
            { 
              obj_data = value;
              is_summary_item_array.push(obj_data);
            }
          }); 
          setTimeout(function(){  
          res.send({'status':200,'message':"success","data":{"summary": is_summary_item_array,'consolidated_summary' : summary }} );
        }, 2000);// end of set time out
        }).catch(err => {
        res.status(500).send({'status':404,
          message: err.message || "Some error occurred while retrieving tutorials." ,'data': "No data" });
        }); 

      }, 2000);// end of set time out 

    //});

  });
} catch(err) {
  res.status(500).send({'status':404,
    message: err.message || "Some error occurred while inserting statements." ,'data': "No data"
   }); }
  
};












// Broken period list api 
exports.BrokenPeriodsList = (req, res) => {
  const entityid =  req.query.entityid
  try {
  Brokenperiods.findOne({where : { entity_id : entityid } }) 
    .then(data => {
     
      if(data)
      {res.send({'status':200,'message':"success",'data':data});}
      else{res.send({'status':404,'message':"failed",'data':"nodata"});}
    })
  }catch(err) {
    res.status(500).send({'status':404,
      message: err.message || "Some error occurred while inserting statements." ,'data': "No data"
     }); }
};









// Broken period Insert OR Update
exports.BrokenPeriods = (req, res) => {
  const obj = req.body
  if (obj.hasOwnProperty("id")) 
  {
    // update broken periods  
    const Id = req.body.id
    Brokenperiods.update( {
      entity_id: req.body.entity_id,
      from: req.body.from,
      to: req.body.to,
      months: req.body.months,
      cy: req.body.cy,
      py: req.body.py,
      variation: req.body.variation,
      updatedby: req.body.updatedby,
    } , { where: { id: Id } })
    .then(data => {
      res.send({'status':200,'message':"success",'data':data});
    })
    .catch(err => {
      res.status(404).send({'status':404,
        message: err.message || "Some error occurred while insertinging statements." ,'data': "No data"
       });
    });
    
  } 
  else 
  {
    // create broken period 
      Brokenperiods.create({
      entity_id: req.body.entity_id,
      from: req.body.from,
      to: req.body.to,
      months: req.body.months,
      cy: req.body.cy,
      py: req.body.py,
      variation: req.body.variation,
      createdby: req.body.createdby,
    })
    .then(data => {
      res.send({'status':200,'message':"success",'data':data});
    })
    .catch(err => {
      res.status(404).send({'status':404,
        message: err.message || "Some error occurred while insertinging statements." ,'data': "No data"
       });
    });
  }
  
};









exports.createFinancials = async (req, res) => {
  
  const req_array_data = req.body;
  try 
 {
  req_array_data.forEach(async(value, key)=>{ 
    
    await Financials.findOne({ where: { id : value.id  , is_active : 1} }).then(async function(result) {
      
      if(result.value == value.value)
      {
        logMsg.info("Value not updated for - EntityId : "+value.entity_id+" , FY : "+value.fy+",ItemId : "+value.itemid+",primary key :"+value.id);
      }
      else
      {
        await Financials.update({ is_active : 0 },{ where: { id:  value.id }});
        logMsg.info("updated is_active = 0 for primary key : "+value.id);
        await Financials.create({ "entity_id":value.entity_id,"st_type":value.st_type,"itemid":value.itemid,"fy":value.fy,"value":value.value,"sub_value":value.sub_value,"is_active":value.is_active,"createdby":value.createdby })
        logMsg.info("Created new financials data - EntityId : "+value.entity_id+" , FY : "+value.fy+",ItemId : "+value.itemid);
      }
   })
    
    });
    res.send({'status':200,'message':"success",'data':"No data"});
  }catch(err) {
    res.status(500).send({'status':404,
      message: err.message || "Some error occurred while inserting statements." ,'data': "No data"
     }); }
};





// this api not in use
exports.updateFinancials =  (req, res) => {
  const req_array_data = req.body;
 try 
 {
  req_array_data.forEach((value, key)=>{ 
    const id = value.id;
    Financials.update({ "entity_id":value.entity_id,"st_type":value.sy_type,"itemid":value.itemid,"fy":value.fy,"value":value.value,"is_active":value.is_active,"updatedby":value.updatedby    },{ where: { id:id }})
   });
  res.send({'status':200,'message':"success",'data':"No data"});
  logMsg.info("multiple financials update Success");
  }catch(err) {
      res.send({'status':404,
        message: err.message || "Some error occurred while inserting statements." ,'data': "No data"
       }); }
    
}; 








// create financial skeletion for given year
exports.createFinancialYear = async (req, res) => { 
  losid = req.body.losid;
  entityid = req.body.entityid;
  probe42_data_status = req.body.probe42_data_status;
  
  Entityfinyear.findOne({ where: { losid : losid , entityid : entityid  } })
  .then(async(Entityfinyearsdata) => { 
   
    const fy = req.body.fy;
    const st_type = req.body.st_type;
    const additional_year_type = req.body.additional_year_type;
   
    if(req.body.fy  != 0)
    {
    Entityfinyear.create({ losid:Entityfinyearsdata.dataValues.losid,entityid:Entityfinyearsdata.dataValues.entityid,FY:fy,st_type:st_type,additional_year_type:additional_year_type })
    .then(async(Entityfinyearsdata) => {
    
      // generate new financials data array for given FY with entity_id

      // Itemsmaster.findAll({ where :{is_active : 1},attributes: ['id'] , order: [['id', 'ASC']] })
      // .then(ItemsmasterData => {
      if(probe42_data_status == 0)
      {
        ItemsmasterData = await  Itemsmaster.findAll({ where :{pvtltd:1,is_active : 1},attributes: ['id'] , order: [['statement', 'ASC'],['section', 'ASC'],['pvtltd_itemorder', 'ASC']] });
      }
      else if(probe42_data_status == 2)
      {
        ItemsmasterData = await  Itemsmaster.findAll({ where :{manual:1,is_active : 1},attributes: ['id'] , order: [['statement', 'ASC'],['section', 'ASC'],['itemorder', 'ASC']] });
      }
        
          
              //construct lineitem array for bulk insert using itemmaster primarykey (id)
              var SkeletonInsert = new Promise((resolve, reject) => {
                let fin_data_array = [];
                ItemsmasterData.forEach( (itemvalue , index , itemvaluearray) => {
                  fin_data_obj = { "entity_id":Entityfinyearsdata.dataValues.entityid,"st_type":Entityfinyearsdata.dataValues.st_type,"itemid":itemvalue.dataValues.id,"fy":Entityfinyearsdata.dataValues.FY,"value":0,"is_active":1,"createdby":4}
                  fin_data_array.push(fin_data_obj);
                  if (index === itemvaluearray.length -1) {   resolve(fin_data_array);  }
                })
              });
              SkeletonInsert.then((Query_data)=>{
                Financials.bulkCreate(Query_data).then(data=>{
                  logMsg.info("Skeleton created for LosId ="+losid+" - EntityId = "+entityid);

                  //response
                  res.status(200).send({'status':200,'message':"success",'data':"No data"});
                }).catch(err=>(console.log(err))); //skeleton bulk create query
              });
           
            
         
      //}).catch(err=>(console.log(err))); //itemmaster query
      
    }).catch(err => {
      res.status(404).send({'status':404,
        message: err.message || "Some error occurred while insertinging statements." ,'data': "No data" });
    }); // end of update Entityfinyears
    }
    else
    {
      res.status(405).send({'status':405, 'message': "The year creation not allowed" ,'data': "No data" });
    } // end of else part

  }).catch(err => {
    res.status(404).send({'status':404,
      message: err.message || "Some error occurred while insertinging statements." ,'data': "No data" });
  }); // end of findOne Entityfinyears

  
};






exports.refreshApi = async (req, res) => { 
   
  try
  {
    const cin = req.body.cin;
    const losid = req.body.losid;
    const entityid = req.body.entityid;
    
     // call and get financial year wise data for entity (probe42 api call)
     financials_data_api_response = ApiCallHelper.probe42ApiCall(cin);
     financials_data_api_response.then(function(result){
       // check 
     if(result == false)
     {
      res.send({'status':404,message:  "Failed" ,'data': "No data for this cin number from probe42" });
     }
     else
     {
     //filter lineitems from api responce and construct to needed format  
     lineItem_data = Probe42DataMappingHelper.lineItemFetching(result);
     lineItem_data.then(function(lineItem_result){     
      var SkeletonUpdate = new Promise((resolve, reject) => {
       //create financial table and entityfinyear table record here 
       lineItem_result.forEach((value, index , lineItem_result_array)=>{ 
        
        Entityfinyear.findOne({raw : true , where:{losid : losid , entityid : entityid , FY : value.year}})
        .then((data)=>{
         if(data)
          { console.log(value.year + "exist");
             //  mapping of probe42 api lineItem with itemMaster primary key
             final_array = Probe42DataMappingHelper.lineItemMapping(value,entityid);
             final_array.then(function(update_array_data){          
             // update mapped lineitem value here
             Probe42DataMappingHelper.lineItemUpdate(update_array_data);
             // promise resolve check 
             if (index === lineItem_result_array.length -1) {   resolve();  }
             });//end of lineItemUpdate helper call  
          
          } 
          else 
          {  console.log(value.year + " not exist");
           // create skeleton for missing year and update financials values
           
           Entityfinyear.create({ "losid":losid,"entityid":entityid,"FY":value.year,"st_type": 0 })
           .then(Entityfinyearsdata => {
               
                 // create skeletion with itemmaster data (itemmaster(id) == financial(itemid))
                 Itemsmaster.findAll({ where :{pvtltd : 1 ,is_active : 1},attributes: ['id'] , order: [['statement', 'ASC'],['section', 'ASC']] })
                 .then(ItemsmasterData => {
                   //construct lineitem array for bulk insert using itemmaster primarykey (id)
                   var SkeletonInsert = new Promise((resolve, reject) => {
                     let fin_data_array = [];
                     ItemsmasterData.forEach( (itemvalue , index , itemvaluearray) => {
                       fin_data_obj = { "entity_id":Entityfinyearsdata.dataValues.entityid,"st_type":Entityfinyearsdata.dataValues.st_type,"itemid":itemvalue.dataValues.id,"fy":Entityfinyearsdata.dataValues.FY,"value":0,"is_active":1,"createdby":4}
                       fin_data_array.push(fin_data_obj);
                       if (index === itemvaluearray.length -1) {   resolve(fin_data_array);  }
                     })
                   });
                   SkeletonInsert.then((Query_data)=>{
                     Financials.bulkCreate(Query_data).then(data=>{ 
                           logMsg.info("Skeleton created successfully for entityid : "+ entityid +" year : " +value.year);
                           //  mapping of probe42 api lineItem with itemMaster primary key
                           final_array = Probe42DataMappingHelper.lineItemMapping(value,entityid);
                           final_array.then(function(update_array_data){           
                           // update mapped lineitem value here
                           Probe42DataMappingHelper.lineItemUpdate(update_array_data);
                           // promise resolve check 
                           if (index === lineItem_result_array.length -1) {   resolve();  }
                            });//end of lineItemUpdate helper call  
                     }).catch(err=>(console.log(err))); //skeleton bulk create query
                   });
                 }).catch(err=>(console.log(err))); //itemmaster query


               
               }).catch(err => {
               res.status(404).send({'status':404,
                 message: err.message || "Some error occurred while insertinging statements." ,'data': "No data" });
             }); // end of entity fin year table data insert


          }
         
        });
            
        });//end of line item foreach
 
      });
      SkeletonUpdate.then((Query_data)=>{
        setTimeout(() => {
          res.status(200).send({'status':200,message:  "success" ,'data': "No data" });
         }, 2000);
      });
                  
      });//end of lineItem filter from api response helper call
    } // end of probe42 api responce success else condition

    });//end of probe42 api call


  }catch(err) {
    res.send({'status':404,message: err.message || "Some error occurred while inserting statements." ,'data': "No data"}); 
    }

  };



// check ProbeStatus changed or not
exports.checkProbeStatus =  (req, res) => {
  const entityid = req.query.entityid;
  const probe42_data_status = req.query.probe42_data_status;
 try 
 {
  Entities.findOne({ where: { id: entityid } , raw:true}).then(EntitiesData=>{
   if(EntitiesData.probe42_data_status == probe42_data_status)
   {
    res.send({'status':200,'message':"success",'data':{MenuListApicall : false}});
   }
   else{
    res.send({'status':200,'message':"success",'data':{MenuListApicall : true}});
   }
  }).catch(err => {   logMsg.info("Probe42 data status checking failed EntityId = "+entityid); }); 
 
  logMsg.info("multiple financials update Success");
  }catch(err) {
      res.send({'status':404,
        message: err.message || "Some error occurred while inserting statements." ,'data': "No data"
       }); }
    
};



// deActivate Financials Year
exports.deActivateFinancialsYear = async (req, res) => { 
  try
  {
      Entityfinyear.destroy({where : {losid: req.query.losid , entityid : req.query.entityid,FY: req.query.FY }})
                    .then(async()=>{
                          await Financials.update({is_active : 0},{where : { entity_id : req.query.entityid,fy: req.query.FY }})
                                          .catch(err=>{logMsg.info("Financials data deletion error = "+err);});
                          res.send({'status':200,message: "Success." ,'data': "No data"});
                         }).catch(err=>{logMsg.info("Entityfinyear deletion error = "+err);});
    
   
  } catch(err) {
  res.send({'status':404,message: err.message || "Some error occurred while inserting statements." ,'data': "No data"}); 
  }

 };



// this api for other income module
exports.getFinancialsYears = async (req, res) => { 
  try
  {
    data = await Entityfinyear.findAll({where : { losid : req.query.losid } , attributes: [ [sequelize.fn('DISTINCT', sequelize.col('FY')), 'FY'] ] , order: [['FY', 'DESC']] })
    res.send({'status':200,"message": "success." ,'data': data}); 
  } catch(err) {
  res.send({'status':404,message: err.message || "Some error occurred while inserting statements." ,'data': "No data"}); 
  }

 };








exports.test = async (req, res) => { 
  try
  {
    res.send({'status':200,"message": "success." ,'data': "No data"}); 
  } catch(err) {
  res.send({'status':404,message: err.message || "Some error occurred while inserting statements." ,'data': "No data"}); 
  }

 };



  // exports.redirectionApitest = async (req, res) => { 
  //   try
  //   {
      


    //   redirectionApi = ApiCallHelper.redirectionApiCall(losid);
    //  redirectionApi.then(function(redirectionApiData){
      
    //    cin = redirectionApiData[0].ciN_LLPIN;
    //    cin_no = cin.toUpperCase();
    //    console.log(cin_no)
    //    financials_data_api_response = ApiCallHelper.probe42ApiCall(cin_no);
    //        financials_data_api_response.then(function(result){ console.log(result)
    //         //filter lineitems from api responce and construct to needed format  
    //         lineItem_data = Probe42DataMappingHelper.lineItemFetching(result);
    //         lineItem_data.then(function(lineItem_result){ 
    //           lineItem_result.forEach((value,index,array) => { 
    //             //  mapping of probe42 api lineItem with itemMaster primary key
    //             entityid = 00000;
    //             final_array = Probe42DataMappingHelper.lineItemMapping(value,entityid);
    //             final_array.then(function(update_array_data){ 
    //               console.log(update_array_data) 
    //               console.log(update_array_data.length)  
    //               // update mapped lineitem value here
    //               //Probe42DataMappingHelper.lineItemUpdate(update_array_data);

    //             });//end of lineItemUpdate helper call
    //           });//end of lineItem mapping with itemMaster primary key helper call
    //          });//end of lineItem filter from api response helper call
    //        });//end of probe42 api call
    // });
  
    // }catch(err) {
    //   res.send({'status':404,message: err.message || "Some error occurred while inserting statements." ,'data': "No data"}); 
    //   }
  
    //  };



    // exports.probedatatest =  (req, res) => { 
    //   Sync(function()
    //   {
    //     try
    //     {
    //        cin = "L74120MH1985PLC035308";
    //       //  //probe42 api call
    //        financials_data_api_response = ApiCallHelper.probe42ApiCall(cin);
    //        financials_data_api_response.then(function(result){
    //         //filter lineitems from api responce and construct to needed format  
    //         lineItem_data = Probe42DataMappingHelper.lineItemFetching(result);
    //         lineItem_data.then(function(lineItem_result){  console.log(lineItem_result);
    //           lineItem_result.forEach((value,index,array) => { 
    //             //  mapping of probe42 api lineItem with itemMaster primary key
    //             final_array = Probe42DataMappingHelper.lineItemMapping(value,entityid);
    //             final_array.then(function(update_array_data){ console.log(update_array_data) 
    //               console.log(update_array_data.length)  
    //               // update mapped lineitem value here
    //               //Probe42DataMappingHelper.lineItemUpdate(update_array_data);
    //             });//end of lineItemUpdate helper call
    //           });//end of lineItem mapping with itemMaster primary key helper call
    //          });//end of lineItem filter from api response helper call
    //        });//end of probe42 api call
       
     
    //   } catch(err) {
    //     res.status(500).send({'status':404,
    //       message: err.message || "Some error occurred while inserting statements." ,'data': "No data"
    //      }); } // end of try catch
    
    //   }); // end of sync function
    
    //   };