<?php

namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Log;
use App\Http\Controllers\Controller;
use App\Product;
use DB;
use Illuminate\Support\Facades\Auth;
use App\User;
use Illuminate\Http\Response;
use App\Notifications;
use App\Affiliation;
use App\Core_areas;
use Carbon;
use App\Imports\ExcelsImport;
use App\Exports\ExcelsExport;


class ExcelController extends Controller
{
    public function importExportExcelORCSV(){
      return view('file_import_export');
    }

    public function importFileIntoDB(Request $request, $bankName){
      if($request->hasFile('incoming')){
        $nameCurrentMonth = date('F');
        $filename = $request->file('incoming')->getClientOriginalName();
        $newName = $bankName.'_'.$nameCurrentMonth.'_'.$filename;
        $request->session()->put('filename', $newName);
        $request->file('incoming')->storeAs('public/upload', $newName);

        $path = $request->file('incoming');

        //new migration code for Incoming
        $import = new ExcelsImport;
        $import->onlySheets(0,1);
        $excelDataImport = \Excel::toArray($import, $path);
        $excelDataIncoming = $excelDataImport[0];

        //new migration code for Outgoing
        // $importOut = new ExcelsImport;
        // $importOut->onlySheets(1);
        // $excelDataImportOut = \Excel::toArray($importOut, $path);
        $excelDataOut = $excelDataImport[1];

        //Declaration for Incoming
        $arr=[];
        $total=0;
        $female=0;
        $male=0;

        //Declaration for Outgoing
        $arrOut = [];
        $totalOut = 0;
        $femaleOut = 0;
        $maleOut = 0;
       
        //Data for Incoming
        if(isset($excelDataIncoming) && count($excelDataIncoming)){
          foreach ($excelDataIncoming as $singleData) {

            if($singleData['name_in_english']!= null && !empty($singleData) && $singleData['no'])
            {
              $singleDatas=array('name' => $this->handleName(trim($singleData['name_in_english'])),
                                'years_of_services' => 2,
                                'titleposition' => trim($singleData['position']),
                                'date_of_commencement' => ($singleData['date_of_commencement_ddmmyyyy']) ? $this->transformDateTime($singleData['date_of_commencement_ddmmyyyy']) : null,
                                'id_number' => $this->handleId(trim($singleData['id_number'])),
                                'montly_gross_salary' => trim($singleData['total_monthly_salary']),
                                'sex' => $this->handleSex(trim($singleData['gender'])),
                                'previous_working_place' => trim($singleData['previous_working_place']),
                                'date_of_birth' => $this->handleDOB($singleData['date_of_birth_ddmmyyyy']),
                                'core_area'=> trim($singleData['core_areas']),
                                'level'=> $this->handleLevel(trim($singleData['level'])),
                                'khemer_name'=> $this->handleKhmerName(trim($singleData['name_in_khmer']))
                                );
              $arr[]=$singleDatas;
            }
          }
        }

        //Data for Outgoing
        if (isset($excelDataOut) && count($excelDataOut)) {
          foreach ($excelDataOut as $singleData) {

            if ($singleData['name_in_english']!= null && !empty($singleData) && $singleData['no']) {
              $singleDatas=array('name' => $this->handleName(trim($singleData['name_in_english'])),
                                'years_of_services' => 1,
                                'titleposition' => trim($singleData['position']),
                                'date_of_commencement' => ($singleData['date_of_commencement_ddmmyyyy']) ? $this->transformDateTime($singleData['date_of_commencement_ddmmyyyy']) : null ,
                                'id_number' => $this->handleId(trim($singleData['id_number'])),
                                'monthly_gross_salary' => trim($singleData['total_monthly_salary']),
                                'sex' => $this->handleSex(trim($singleData['gender'])),
                                'date_of_birth' => $this->handleDOB($singleData['date_of_birth_ddmmyyyy']),
                                'effective_date_of_resignation'=> ($singleData['effective_date_of_resignation_ddmmyyyy']) ? $this->transformDateTime($singleData['effective_date_of_resignation_ddmmyyyy']) : null ,
                                'reason_for_resignation'=> trim($singleData['reason_for_resignation']),
                                'core_area'=> trim($singleData['core_areas']),
                                'level'=> $this->handleLevel(trim($singleData['level'])),
                                'khemer_name'=> $this->handleKhmerName(trim($singleData['name_in_khmer'])) );
                            //    'khemer_name'=> trim($singleData['name_in_khmer']) );
              $arrOut[]=$singleDatas;
            }
          }
        }
      }

      $allGoing['incoming']=$arr;
      $allGoing['outgoing']= $arrOut;

      echo json_encode($allGoing);
    }
    
    public function handleSex ( $bankUserSex ) {
      $bankUserSex = strtolower($bankUserSex);
      if ( $bankUserSex == "m" || $bankUserSex == "male" ) {
        return 'Male';
      } else if ( $bankUserSex == "f" || $bankUserSex == "female" ) {
        return 'Female';
      } else {
        return '';
      }
    }
    public function handleLevel ( $bankUserLevel ) {
      $level = array("Level 1", "Level 2", "Level 3", "Level 4");
      if (!in_array($bankUserLevel, $level)) {
        return '';
      }
      return $bankUserLevel;
    }

    public function handleName ( $bankUserName) {
      //Checking For Mr.Mrs.Ms.
      $pattern = "/\bM(r|s|rs)(\.|\s)/i";
      $bankUserName = preg_replace($pattern, "", $bankUserName);
      //if matches 2 white spaces
      if ( (bool) preg_match("/\w+\s{2,}(?=\w+)/", $bankUserName) ){
        return '';
      }
      //if not aphabet
      if ( (bool) preg_match( "/[^a-zA-Z\s]/", $bankUserName) ) {
        return '';
      }
      return $bankUserName;
    }

    public function handleKhmerName($userKhmerName){

      //replace any consecutive spaces with one space
      $userKhmerName = preg_replace('/\s+/', ' ', $userKhmerName);
      
      //replace zero width character with nothing
      $userKhmerName = preg_replace('/\x{200B}/u',"",$userKhmerName);

      //check khmer language exists in the name
      $khmerLanguage = preg_match('/[\x{1780}-\x{17FF}]/u', $userKhmerName);

      //check if other language exists in the name
      $otherLanguage = preg_match('/[^\x{1780}-\x{17FF}\s]/u', $userKhmerName);

      //make sure the name is not a mixed language
      if($khmerLanguage && !$otherLanguage){
        return $userKhmerName;
      }else{
        return '';
      }
      
    }

    public function handleId ( $userIdNumber ) {
      
      if(empty($userIdNumber)){
        return '';
      }
      //Passport_id have letters and number
      if ( preg_match("/[A-Za-z]/", $userIdNumber[0]) ) {

        if ( (strlen($userIdNumber) < 5) || (strlen($userIdNumber) >20) ) return '';
      
        if ( !preg_match("/[^0-9A-Za-z]/", $userIdNumber) ) {
          return $userIdNumber;
        } else { 
          return '';
        }
      } else {

        //National ID length is 9
        if ( strlen($userIdNumber) != 9 ) return '';
        
        if ( !preg_match("/[^0-9]/", $userIdNumber) ) {
          return $userIdNumber;
        } else {
          return '';
        }
      }

    }

    public function handleDOB ( $userDOB ) {
      if( empty($userDOB) ){
        return '';
      }

      if ( $userDOB ) $convertedDOB = $this->transformDateTime($userDOB);
      else '';

      $userDOBnew = $this->convertDateToCompare($convertedDOB);
      $currentDate = date_create(date("d-m-Y"));
      $userDate = date_create($userDOBnew);
      
      $interval = date_diff($userDate, $currentDate);
      $daysDiff = (int) $interval->format("%R%a");
      
      if ( floor( $daysDiff/(365) ) < 18 ) {
        return '';
      } else {
        return $convertedDOB;
      }
    }

    public function getDetails($id){
      try {
        
        $data = false;
        //select duplicate user
        $selectDuplicate = DB::table('duplicate')->select('*')->where('bankUserIdOld', $id)->get();
        $selectDuplicate = json_decode($selectDuplicate, true);
        $bankUsers= [];
        

        array_push($bankUsers, $id);

        if($selectDuplicate) {
          foreach( $selectDuplicate as $dup){
            array_push($bankUsers, $dup['bankUserId']);
          }
        }
        $selectDecor = DB::table('bankuser')
        ->join('decoration_form', 'bankuser.bankUserId', '=', 'decoration_form.bankUserId' )
        ->select("bankuser.*", "decoration_form.*")
        ->whereIn('bankuser.bankUserId',$bankUsers)->get();
        $selectDecor = json_decode($selectDecor, true);
        $data = $selectDecor;
        if($data){
          return response()->json(['message'=> 'Bank details get successfully.', 'data' => $data], Response::HTTP_OK);
        }else{
          return response()->json(['message' => 'Data Not Found'], Response::HTTP_OK );
        }
      } catch (Exception $e) {
        return response()->json(['message'=>$e->getMessage()], Response::HTTP_INTERNAL_SERVER_ERROR);
      }
    }


    public function editUser($id, Request $request) {
      try {
        DB::enableQueryLog();
        
        try {
          $userSql = DB::table('bankuser')->where('bankUserId', $request->bankUserId)->update(
            array(
              'name' => $request->firstName,
              'id_number' => $request->idNumber,
              'date_of_birth' => $request->dob,
              'sex' => $request->sex,
              'khemer_name' => $request->khmerName,
            )
          );
        } catch (Exception $e) {
            return response()->json(['message'=>$e->getMessage(),'status'=>500], Response::HTTP_INTERNAL_SERVER_ERROR);
        }

        try {
          $detailSql = DB::table('decoration_form')->where('pId', $request->pId)->update(
            array(
              'years_of_services' => $request->yearService,
              'titleposition' => $request->titlePosition,
              'date_of_commencement' => $request->dateOfCommencement,
              'montly_gross_salary' => $request->monthlyGrossSalary,
              'previous_working_place' => $request->prevWorkingPlace,
              'level' => $request->level,
              'userType' => $request->userType,
              'core_area' => $request->coreArea,
              'effective_resignation' => $request->effectiveResignation,
              'resignation_reason' => $request->resignationReason 
            )
          );
          } catch (Exception $e) {
            return response()->json(['message'=>$e->getMessage(),'status'=>500], Response::HTTP_INTERNAL_SERVER_ERROR);
          }  
      
        $res = true;
        if($res){
          return response()->json(['message'=> 'Bank user updation successfully.', 'data' => true], Response::HTTP_OK);
        }else{
          return response()->json(['message' => 'Nothing will be updated!'], Response::HTTP_OK );
        }
      } catch (Exception $e) {
        return response()->json(['message'=>$e->getMessage()], Response::HTTP_INTERNAL_SERVER_ERROR);
      }
    }

    public function editBankUser($id, Request $request){
      $datas = $request->all();
      try {
        
        DB::enableQueryLog();
        foreach( $datas as $data ) {
          try {
            $userSql = DB::table('bankuser')->where('bankUserId', $data['bankUserId'])->update(
              array(
                'name' => $data['name'],
                'khemer_name' => $data['khemer_name'],
                'id_number' => $data['id_number'],
                'date_of_birth' => $data['date_of_birth'],
                'sex' => $data['sex']
              )
            );
          } catch (Exception $e) {
              return response()->json(['message'=>$e->getMessage(),'status'=>500], Response::HTTP_INTERNAL_SERVER_ERROR);
          }
          try {
            $detailSql = DB::table('decoration_form')->where('pid', $data['pid'])->update(
              array(
                'titleposition' => $data['titleposition'],
                'date_of_commencement' => $data['date_of_commencement'],
                'montly_gross_salary' => $data['montly_gross_salary'],
                'previous_working_place' => (isset($data['previous_working_place'])) ? $data['previous_working_place'] : null,
                'core_area' => $data['core_area'][0],
                'effective_resignation' => (isset($data['effective_resignation'])) ? $data['effective_resignation'] : null,
                'resignation_reason' =>  (isset($data['resignation_reason'])) ? $data['resignation_reason'] : null,
                'level' => $data['level']
              )
            );
            } catch (Exception $e) {
              return response()->json(['message'=>$e->getMessage(),'status'=>500], Response::HTTP_INTERNAL_SERVER_ERROR);
            }  
          }
        $res = true;
        if($res){
          return response()->json(['message'=> 'Bank user updated successfully.', 'data' => true], Response::HTTP_OK);
        }else{
          return response()->json(['message' => 'Nothing will be updated!'], Response::HTTP_OK );
        }
      } catch (Exception $e) {
        return response()->json(['message'=>$e->getMessage()], Response::HTTP_INTERNAL_SERVER_ERROR);
      }
    }

     public function editUserBank($id,$notificationId=0, Request $request){
        try {
            DB::enableQueryLog();
            $res = DB::table('bankuser')->where('bankUserId',$id)->update(
                array('name' => $request->name,
                      'khemer_name' => $request->khemer_name,
                      'titleposition' => $request->titleposition,
                      'date_of_commencement' => $request->date_of_commencement,
                      'id_number' => $request->id_number,
                      'montly_gross_salary' => $request->montly_gross_salary,
                      'sex' => $request->sex,
                      'previous_working_place' => ($request->previous_working_place) ? $request->previous_working_place : null,
                      'date_of_birth' => $request->date_of_birth,
                      'core_area'=>$request->core_area,
                      'level'=>$request->level,
                      'effective_resignation'=>(isset($request->effective_resignation)) ? $request->effective_resignation : null,
                      'resignation_reason'=>(isset($request->resignation_reason)) ? $request->resignation_reason : null )
            );
            DB::table('notifications')->where('id',$notificationId)->update(['is_read'=> 1]);
            if($res){
                return response()->json(['message'=> 'Bank user updation successfully.', 'data' => true,'status'=>200], Response::HTTP_OK);
            }else{
                return response()->json(['message' => 'Nothing will be updated!','status'=>400], Response::HTTP_OK );
            }
        } catch (Exception $e) {
            return response()->json(['message'=>$e->getMessage(),'status'=>500], Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }

    public function secondExcelDowFile($monthYear){
      try {
        DB::enableQueryLog();
        $pre =$preMonth;
        $next= $nextMonth;
        if($preMonth < 10){
          $pre = '0'.$preMonth;
        }
        if($nextMonth < 10){
          $next = '0'.$nextMonth;
        }
        $preDate = $preYear.'-'. $pre . '-01';
        $nextDate = $nextYear.'-'. $next . '-31';
        $newprod = $this->StaffInTurnover($preDate, $nextDate);
        $newprod1 = $this->StaffOutTurnover($preDate, $nextDate);
        $newprod2 = $this->InflationInPay($preDate, $nextDate);
        $newprod3 = $this->LevelandAreasofStaffAffect($preDate, $nextDate);
        $newprod4 = $this->DurationofService($preDate, $nextDate);
        $newprod5 = $this->SalaryScale($preDate, $nextDate);
        $fields = ['Association', 'Company', 'Total Staff', 'Staff In', 'Staff In Turnover %',  'Staff In Turnover Level 1', 'Staff In Turnover Level 1 %', 'Staff In Turnover Level 2', 'Staff In Turnover Level 2 %', 'Staff In Turnover Level 3','Staff In Turnover Level 3 %','Staff Turnover Credit','Staff Turnover Credit %','Staff Turnover Risk', 'Staff Turnover Risk %', 'Staff Turnover Compliance', 'Staff Turnover Compliance %', 'Staff Turnover Trade Finance', 'Staff Turnover Trade Finance %', 'Staff Turnover IT', 'Staff Turnover IT %', 'Staff Turnover HR', 'Staff Turnover HR %', 'Staff Turnover Internal Audi', 'Staff Turnover Internal Audit %', 'Staff Turnover Treasury', 'Staff Turnover Treasury %', 'Staff Turnover Operation', 'Staff Turnover Operation %', '  Staff Turnover Other', 'Staff Turnover Other %'];
        return \Excel::create('Statistics Report', function($excel) use ($newprod, $newprod1,$newprod2, $newprod3, $newprod4, $newprod5, $fields) {
          $excel->setTitle('Statistics Report');
          $excel->setDescription('Statistics Report');
          $excel->sheet('Staff In Turnover', function($sheet) use ($newprod, $fields)
          {
            $sheet->fromArray($newprod, NULL, 'A0');
          });
          $excel->sheet('Staff Out Turnover', function($sheet) use ($newprod1)
          {
            $sheet->fromArray($newprod1, NULL, 'A0');
          });
          $excel->sheet('Inflation in Pay', function($sheet) use ($newprod2)
          {
            $sheet->fromArray($newprod2, NULL, 'A0');
          });
          $excel->sheet('Level and Areas of Staff Affect', function($sheet) use ($newprod3)
          {
            $sheet->fromArray($newprod3, NULL, 'A0');
          });
          $excel->sheet('Duration of Service', function($sheet) use ($newprod4)
          {
            $sheet->fromArray($newprod4, NULL, 'A0');
          });
          $excel->sheet('Salary Scale', function($sheet) use ($newprod5)
          {
            $sheet->fromArray($newprod5, NULL, 'A0');
          });
        })->download('xls');
        die;
      } catch (Exception $e) {
        echo $e->getMessage();
      }
    }

    public static function StaffInTurnover($preDate, $nextDate){
    	$products = DB::select("SELECT  bankuser.associateId, (CASE WHEN af.parent_id > 0 THEN (SELECT name FROM affiliations WHERE id = af.parent_id) ELSE af.name END) as associate,
                                af.name as companyName, statcounts.total as total,COUNT(*) as StaffIn , ((COUNT(*)*100)/total) as stafftopercent, (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1') as level1_count, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1')*100)/(COUNT(*))) as level1_count_parcent, (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2') as level2_count, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2')*100)/(COUNT(*))) as level2_count_parcent, (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3') as level3_count, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3')*100)/(COUNT(*))) as level3_count_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Legal') as core_area_legal_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Legal')*100)/(COUNT(*))) as core_area_legal_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Compliance/AML') as core_area_aml_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Compliance/AML')*100)/(COUNT(*))) as core_area_aml_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Risk') as core_area_risk_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Risk')*100)/(COUNT(*))) as core_area_risk_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Credit') as core_area_credit_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Credit')*100)/(COUNT(*))) as core_area_credit_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Treasury') as core_area_Treasury_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Treasury')*100)/(COUNT(*))) as core_area_Treasury_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Trade Finance') as core_area_Trade_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Trade Finance')*100)/(COUNT(*))) as core_Trade_Treasury_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Internal Audit') as core_area_Internal_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Internal Audit')*100)/(COUNT(*))) as core_area_Internal_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'IT') as core_area_IT_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'IT')*100)/(COUNT(*))) as core_area_IT_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Operation') as core_area_Operation_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Operation')*100)/(COUNT(*))) as core_area_Operation_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'HR') as core_area_Operation_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'HR')*100)/(COUNT(*))) as core_area_Operation_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Others') as core_area_Others_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Others')*100)/(COUNT(*))) as core_area_Others_parcent
                                FROM `bankuser` INNER JOIN users ON users.id = bankuser.associateId INNER JOIN affiliations as af ON af.id = users.affiliation_id LEFT JOIN statcounts ON bankuser.associateId = statcounts.associateId WHERE userType = 1  AND  (date_format(bankuser.submission_date,'%Y-%m-%d')  BETWEEN "."'".$preDate."'"." AND "."'".$nextDate."'".") GROUP BY associateId");
      $newprod = [];
      $newprod[] = ['Association', 'Company', 'Total Staff',	'Staff In',	'Staff In Turnover %',	'Staff In Turnover Level 1', 'Staff In Turnover Level 1 %', 'Staff In Turnover Level 2', 'Staff In Turnover Level 2 %', 'Staff In Turnover Level 3','Staff In Turnover Level 3 %','Staff Turnover Credit','Staff Turnover Credit %','Staff Turnover Risk', 'Staff Turnover Risk %', 'Staff Turnover Compliance', 'Staff Turnover Compliance %', 'Staff Turnover Trade Finance', 'Staff Turnover Trade Finance %', 'Staff Turnover IT', 'Staff Turnover IT %', 'Staff Turnover HR', 'Staff Turnover HR %', 'Staff Turnover Internal Audi', 'Staff Turnover Internal Audit %', 'Staff Turnover Treasury', 'Staff Turnover Treasury %', 'Staff Turnover Operation', 'Staff Turnover Operation %', '	Staff Turnover Other', 'Staff Turnover Other %'];
      $totalStaff = 0;
      $totalInStaff = 0;
      foreach($products as $prod){
        unset($prod->associateId);
        $newprod[] = (array)$prod;
        $totalStaff += $prod->total;
        $totalInStaff += $prod->StaffIn;
      }
      if(count($products) > 0){
        $newprod[] = ['','TOTAL', $totalStaff, $totalInStaff];
      }
      return $newprod;
    }

    public static function StaffOutTurnover($preDate, $nextDate){
    	$products = DB::select("SELECT  bankuser.associateId, (CASE WHEN af.parent_id > 0 THEN (SELECT name FROM affiliations WHERE id = af.parent_id) ELSE af.name END) as associate,
                                af.name as companyName, statcounts.total as total,COUNT(*) as StaffIn , ((COUNT(*)*100)/total) as stafftopercent, (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1') as level1_count, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1')*100)/(COUNT(*))) as level1_count_parcent, (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2') as level2_count, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2')*100)/(COUNT(*))) as level2_count_parcent, (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3') as level3_count, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3')*100)/(COUNT(*))) as level3_count_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Legal') as core_area_legal_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Legal')*100)/(COUNT(*))) as core_area_legal_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Compliance/AML') as core_area_aml_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Compliance/AML')*100)/(COUNT(*))) as core_area_aml_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Risk') as core_area_risk_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Risk')*100)/(COUNT(*))) as core_area_risk_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Credit') as core_area_credit_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Credit')*100)/(COUNT(*))) as core_area_credit_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Treasury') as core_area_Treasury_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Treasury')*100)/(COUNT(*))) as core_area_Treasury_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Trade Finance') as core_area_Trade_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Trade Finance')*100)/(COUNT(*))) as core_Trade_Treasury_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Internal Audit') as core_area_Internal_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Internal Audit')*100)/(COUNT(*))) as core_area_Internal_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'IT') as core_area_IT_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'IT')*100)/(COUNT(*))) as core_area_IT_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Operation') as core_area_Operation_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Operation')*100)/(COUNT(*))) as core_area_Operation_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'HR') as core_area_Operation_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'HR')*100)/(COUNT(*))) as core_area_Operation_parcent,
                                (SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Others') as core_area_Others_count , (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Others')*100)/(COUNT(*))) as core_area_Others_parcent
                                FROM `bankuser` INNER JOIN users ON users.id = bankuser.associateId INNER JOIN affiliations as af ON af.id = users.affiliation_id LEFT JOIN statcounts ON bankuser.associateId = statcounts.associateId WHERE userType = 2  AND  (date_format(bankuser.submission_date,'%Y-%m-%d')  BETWEEN "."'".$preDate."'"." AND "."'".$nextDate."'".") GROUP BY associateId");
      $newprod = [];
      $newprod[] = ['Association', 'Company', 'Total Staff',	'Staff Out',	'Staff Out Turnover %',	'Staff Out Turnover Level 1', 'Staff Out Turnover Level 1 %', 'Staff Out Turnover Level 2', 'Staff Out Turnover Level 2 %', 'Staff Out Turnover Level 3','Staff Out Turnover Level 3 %','Staff Turnover Credit','Staff Turnover Credit %','Staff Turnover Risk', 'Staff Turnover Risk %', 'Staff Turnover Compliance', 'Staff Turnover Compliance %', 'Staff Turnover Trade Finance', 'Staff Turnover Trade Finance %', 'Staff Turnover IT', 'Staff Turnover IT %', 'Staff Turnover HR', 'Staff Turnover HR %', 'Staff Turnover Internal Audi', 'Staff Turnover Internal Audit %', 'Staff Turnover Treasury', 'Staff Turnover Treasury %', 'Staff Turnover Operation', 'Staff Turnover Operation %', '	Staff Turnover Other', 'Staff Turnover Other %'];
      $totalStaff = 0;
      $totalInStaff = 0;
      foreach($products as $prod){
        unset($prod->associateId);
        $newprod[] = (array)$prod;
        $totalStaff += $prod->total;
        $totalInStaff += $prod->StaffIn;
      }
      if(count($products) > 0){
        $newprod[] = ['','TOTAL', $totalStaff, $totalInStaff];
      }
      return $newprod;
    }

    public static function InflationInPay($preDate, $nextDate){
      DB:: enableQueryLog();
      // (CASE WHEN af.parent_id >0 THEN (SELECT name FROM affiliations WHERE id = af.parent_id) ELSE af.name END) as associate,af.name as companyName, statcounts.total as total, ((COUNT(*)*100)/total) as stafftopercent,
    	$products = DB::select("SELECT  montly_gross_salary, bankuser.name, bankuser.id_number, bankuser.date_of_birth from bankuser INNER JOIN users ON users.id=bankuser.associateId INNER JOIN affiliations as af ON af.id=users.affiliation_id LEFT JOIN statcounts ON bankuser.associateId = statcounts.associateId where userType=1");
      $newprod = [];
      foreach($products as $prod){
        $name = $prod->name;
        $idNumber = $prod->id_number;
        $DOB = $prod->date_of_birth;
        $productsNew = DB::select("SELECT montly_gross_salary from bankuser where userType=2 AND name = "."'".$name."'"."  AND id_number = "."'".$idNumber."'"." AND date_of_birth = "."'".$DOB."'"." GROUP BY bankuser.associateId ORDER BY created_at DESC");

        $max_salary = $prod->montly_gross_salary;
        if(count($productsNew) > 0){
          $min_salary = $productsNew[0]->montly_gross_salary;
        }else{
          $min_salary = $prod->montly_gross_salary;
        }
        echo $min_salary."</br>";
        echo $max_salary."</br>";

        $newprod[] = (array)$prod;
      }
      dd(DB::getQueryLog());

      if(count($products) > 0){
      }
      return $newprod;
    }

    public static function LevelandAreasofStaffAffect($preDate, $nextDate){
     	$products = DB::select("SELECT  bankuser.associateId, (CASE WHEN af.parent_id > 0 THEN (SELECT name FROM affiliations WHERE id = af.parent_id) ELSE af.name END) as associate,
                                af.name as companyName, statcounts.total as total, ((COUNT(*)*100)/total) as stafftopercent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1')*100)/(COUNT(*))) as level1_count_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2')*100)/(COUNT(*))) as level2_count_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3')*100)/(COUNT(*))) as level3_count_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Legal')*100)/(COUNT(*))) as core_area_legal_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Compliance/AML')*100)/(COUNT(*))) as core_area_aml_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Risk')*100)/(COUNT(*))) as core_area_risk_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Credit')*100)/(COUNT(*))) as core_area_credit_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Treasury')*100)/(COUNT(*))) as core_area_Treasury_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Trade Finance')*100)/(COUNT(*))) as core_Trade_Treasury_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Internal Audit')*100)/(COUNT(*))) as core_area_Internal_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'IT')*100)/(COUNT(*))) as core_area_IT_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Operation')*100)/(COUNT(*))) as core_area_Operation_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'HR')*100)/(COUNT(*))) as core_area_Operation_parcent, (((SELECT COUNT(*) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.core_area = 'Others')*100)/(COUNT(*))) as core_area_Others_parcent
                                FROM `bankuser` INNER JOIN users ON users.id = bankuser.associateId INNER JOIN affiliations as af ON af.id = users.affiliation_id LEFT JOIN statcounts ON bankuser.associateId = statcounts.associateId WHERE (date_format(bankuser.submission_date,'%Y-%m-%d')  BETWEEN "."'".$preDate."'"." AND "."'".$nextDate."'".")  GROUP BY bankuser.associateId");
		  $newprod = [];
      $newprod[] = ['Association',	'Company',	'Total Staff','Staff Movement - Total','	Staff Movement - Level 1','	Staff Movement - Level 2','	Staff Movement - Level 3', 'Staff Movement - Credit', 'Staff Movement - Risk', '	Staff Movement - Compliance	', 'Staff Movement - Trade Finance', 'Staff Movement - IT', '	Staff Movement - HT', '	Staff Movement - Internal Audit', '	Staff Movement - Treasury', '	Staff Movement - Operation	', 'Staff Movement - Other'];
    	$totalStaff = 0;
      foreach($products as $prod){
        unset($prod->associateId);
        $newprod[] = (array)$prod;
        $totalStaff += $prod->total;
      }
      if(count($products) > 0){
        $newprod[] = ['','TOTAL', $totalStaff];
      }
      return $newprod;
    }

    public static function DurationofService($preDate, $nextDate){
    	$products = [];
    	$totalStaff = 0;
      $totalInStaff = 0;
      $newprod = [];
      foreach($products as $prod){
        unset($prod->associateId);
        $newprod[] = (array)$prod;
        $totalStaff += $prod->total;
        $totalInStaff += $prod->StaffIn;
      }
      if(count($products) > 0){
        $newprod[] = ['','TOTAL', $totalStaff, $totalInStaff];
      }
      return $newprod;
    }

    public function SalaryScale($preDate, $nextDate){
    	$products = DB::select("SELECT  bankuser.associateId, (CASE WHEN af.parent_id > 0 THEN (SELECT name FROM affiliations WHERE id = af.parent_id) ELSE af.name END) as associate,
                                af.name as companyName, (SELECT CONCAT(MIN(montly_gross_salary), '-', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1') as level1_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2') as level2_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3') as level3_scale,
			    (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1' AND bu.core_area = 'Legal') as level1_Legal_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2' AND bu.core_area = 'Legal') as level2_Legal_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3' AND bu.core_area = 'Legal') as level3_Legal_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1' AND bu.core_area = 'Risk') as level1_Risk_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2' AND bu.core_area = 'Risk') as level2_Risk_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3' AND bu.core_area = 'Risk') as level3_Risk_scale,
		   
			 (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1' AND bu.core_area = 'Treasury') as level1_Treasury_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2' AND bu.core_area = 'Treasury') as level2_Treasury_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3' AND bu.core_area = 'Treasury') as level3_Treasury_scale,
			
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1' AND bu.core_area = 'Internal Audit') as level1_Audit_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2' AND bu.core_area = 'Internal Audit') as level2_Audit_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3' AND bu.core_area = 'Internal Audit') as level3_Audit_scale,
        (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1' AND bu.core_area = 'Credit') as level1_Credit_scale,
      (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2' AND bu.core_area = 'Credit') as level2_Credit_scale,
      (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3' AND bu.core_area = 'Credit') as level3_Credit_scale,
      (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1' AND bu.core_area = 'Trade Finance') as level1_Trade_scale,
      (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2' AND bu.core_area = 'Trade Finance') as level2_Trade_scale,
      (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3' AND bu.core_area = 'Trade Finance') as level3_Trade_scale,			     
			 (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1' AND bu.core_area = 'Operation') as level1_Operation_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2' AND bu.core_area = 'Operation') as level2_Operation_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3' AND bu.core_area = 'Operation') as level3_Operation_scale,
			 (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1' AND bu.core_area = 'HR') as level1_HR_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2' AND bu.core_area = 'HR') as level2_HR_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3' AND bu.core_area = 'HR') as level3_HR_scale,
      (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1' AND bu.core_area = 'IT') as level1_IT_scale,
      (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2' AND bu.core_area = 'IT') as level2_IT_scale,
      (SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3' AND bu.core_area = 'IT') as level3_IT_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 1' AND bu.core_area = 'Others') as level1_Others_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 2' AND bu.core_area = 'Others') as level2_Others_scale,
			(SELECT CONCAT(MIN(montly_gross_salary), ' - ', MAX(montly_gross_salary)) from bankuser as bu WHERE bu.associateId = bankuser.associateId AND bu.level = 'Level 3' AND bu.core_area = 'Others') as level3_Others_scale FROM `bankuser` INNER JOIN users ON users.id = bankuser.associateId INNER JOIN affiliations as af ON af.id = users.affiliation_id LEFT JOIN statcounts ON bankuser.associateId = statcounts.associateId  WHERE (date_format(bankuser.submission_date,'%Y-%m-%d')  BETWEEN "."'".$preDate."'"." AND "."'".$nextDate."'".") GROUP BY bankuser.associateId");

      $newprod = [];
      $newprod[] = ['Association', 'Company', 'Compliance - Level 1','	Compliance - Level 2','	Compliance - Level 3', '	Legal - Level 1', '	Legal - Level 2', '	Legal - Level 3', '	Risk - Level 1', '	Risk - Level 2', '	Risk - Level 3', '	Treasury - Level 1', '	Treasury - Level 2', 'Treasury - Level 3', '	Internal Audit - Level 1', '	Internal Audit - Level 2', '	Internal Audit - Level 3', 'Credit - Level 1',	'Credit - Level 2',	'Credit - Level 3',	'Trade Finance - Level 1',	'Trade Finance - Level 2',	'Trade Finance - Level 3',	'Operation - Level 1',	'Operation - Level 2','	Operation - Level 3','	Human Resources - Level 1', '	Human Resources - Level 2', '	Human Resources - Level 3',	'IT - Level 1',	'IT - Level 2',	'IT - Level 3', '	Other - Level 1', '	Other - Level 2', '	Other - Level 3'];

  	  $ComplianceLevel1 = 0;
      $ComplianceLevel2 = 0;
      $ComplianceLevel3 = 0;
      $LegalLevel1 = 0;
      $LegalLevel2 = 0;
      $LegalLevel3 = 0;
      $RiskLevel1 = 0;
      $RiskLevel2 = 0;
      $RiskLevel3 = 0;
      $TreasuryLevel1 = 0;
      $TreasuryLevel2 = 0;
      $TreasuryLevel3 = 0;
      $InternalAuditLevel1 = 0;
      $InternalAuditLevel2 = 0;
      $InternalAuditLevel3 = 0;
      $CreditLevel1 = 0;
      $CreditLevel2 = 0;
      $CreditLevel3 = 0;
      $TradeFinanceLevel1 = 0;
      $TradeFinanceLevel2 = 0;
      $TradeFinanceLevel3 = 0;
      $OperationLevel1 = 0;
      $OperationLevel2 = 0;
      $OperationLevel3 = 0;
      $HumanResourcesLevel1 = 0;
      $HumanResourcesLevel2 = 0;
      $HumanResourcesLevel3 = 0;
      $ITLevel1 = 0;
      $ITLevel2 = 0;
      $ITLevel3 = 0;
      $OtherLevel1 = 0;
      $OtherLevel2 = 0;
      $OtherLevel3 = 0;

      $level1_scale = 0;
      $level2_scale = 0;
      $level3_scale = 0;
      $level1_Legal_scale = 0;
      $level2_Legal_scale = 0;
      $level3_Legal_scale = 0;
      $level1_Risk_scale = 0;
      $level2_Risk_scale = 0;
      $level3_Risk_scale = 0;
      $level1_Credit_scale = 0;
      $level2_Credit_scale = 0;
      $level3_Credit_scale = 0;
      $level1_Treasury_scale = 0;
      $level2_Treasury_scale = 0;
      $level3_Treasury_scale = 0;
      $level1_Trade_scale = 0;
      $level2_Trade_scale = 0;
      $level3_Trade_scale = 0;
      $level1_Audit_scale = 0;
      $level2_Audit_scale = 0;
      $level3_Audit_scale = 0;
      $level1_IT_scale = 0;
      $level2_IT_scale = 0;
      $level3_IT_scale = 0;
      $level1_Operation_scale = 0;
      $level2_Operation_scale = 0;
      $level3_Operation_scale = 0;
      $level1_HR_scale = 0;
      $level2_HR_scale = 0;
      $level3_HR_scale = 0;
      $level1_Others_scale = 0;
      $level2_Others_scale = 0;
      $level3_Others_scale = 0;

      $ComplianceLevel1Min = 0;
      $ComplianceLevel1Max = 0;
      $ComplianceLeve2Min = 0;
      $ComplianceLeve2Max = 0;
      $ComplianceLeve3Min = 0;
      $ComplianceLeve3Max = 0;
      $LegalLevel1Min = 0;
      $LegalLevel1Max = 0;
      $LegalLevel2Min = 0;
      $LegalLevel2Max = 0;
      $LegalLevel3Min = 0;
      $LegalLevel3Max = 0;
      $RiskLevel1Min = 0;
      $RiskLevel1Max = 0;
      $RiskLevel2Min = 0;
      $RiskLevel2Max = 0;
      $RiskLevel3Min = 0;
      $RiskLevel3Max = 0;
      $TreasuryLevel1Min = 0;
      $TreasuryLevel1Max = 0;
      $TreasuryLevel2Min = 0;
      $TreasuryLevel2Max = 0;
      $TreasuryLevel3Min = 0;
      $TreasuryLevel3Max = 0;
      $InternalAuditLevel1Min = 0;
      $InternalAuditLevel1Max = 0;
      $InternalAuditLevel2Min = 0;
      $InternalAuditLevel2Max = 0;
      $InternalAuditLevel3Min = 0;
      $InternalAuditLevel3Max = 0;
      $CreditLevel1Min = 0;
      $CreditLevel1Max = 0;
      $CreditLevel2Min = 0;
      $CreditLevel2Max = 0;
      $CreditLevel3Min = 0;
      $CreditLevel3Max = 0;
      $TradeFinanceLevel1Min = 0;
      $TradeFinanceLevel1Max = 0;
      $TradeFinanceLevel2Min = 0;
      $TradeFinanceLevel2Max = 0;
      $TradeFinanceLevel3Min = 0;
      $TradeFinanceLevel3Max = 0;
      $OperationLevel1Min = 0;
      $OperationLevel1Max = 0;
      $OperationLevel2Min = 0;
      $OperationLevel2Max = 0;
      $OperationLevel3Min = 0;
      $OperationLevel3Max = 0;
      $HumanResourcesLevel1Min = 0;
      $HumanResourcesLevel1Max = 0;
      $HumanResourcesLevel2Min = 0;
      $HumanResourcesLevel2Max = 0;
      $HumanResourcesLevel3Min = 0;
      $HumanResourcesLevel3Max = 0;
      $ITLevel1Min = 0;
      $ITLevel1Max = 0;
      $ITLevel2Min = 0;
      $ITLevel2Max = 0;
      $ITLevel3Min = 0;
      $ITLevel3Max = 0;
      $OtherLevel1Min = 0;
      $OtherLevel1Max = 0;
      $OtherLevel2Min = 0;
      $OtherLevel2Max = 0;
      $OtherLevel3Min = 0;
      $OtherLevel3Max = 0;

      $count = count($products);
      foreach($products as $prod){
        unset($prod->associateId);
        $newprod[] = (array)$prod;
        $level1_scale = explode('-',$prod->level1_scale);
        $level2_scale = explode('-',$prod->level2_scale);
        $level3_scale = explode('-',$prod->level3_scale);
        $level1_Legal_scale = explode('-',$prod->level1_Legal_scale);
        $level2_Legal_scale = explode('-',$prod->level2_Legal_scale);
        $level3_Legal_scale = explode('-',$prod->level3_Legal_scale);
        $level1_Risk_scale = explode('-',$prod->level1_Risk_scale);
        $level2_Risk_scale = explode('-',$prod->level2_Risk_scale);
        $level3_Risk_scale = explode('-',$prod->level3_Risk_scale);
        $level1_Credit_scale = explode('-',$prod->level1_Credit_scale);
        $level2_Credit_scale = explode('-',$prod->level2_Credit_scale);
        $level3_Credit_scale = explode('-',$prod->level3_Credit_scale);
        $level1_Treasury_scale = explode('-',$prod->level1_Treasury_scale);
        $level2_Treasury_scale = explode('-',$prod->level2_Treasury_scale);
        $level3_Treasury_scale = explode('-',$prod->level3_Treasury_scale);
        $level1_Trade_scale = explode('-',$prod->level1_Trade_scale);
        $level2_Trade_scale = explode('-',$prod->level2_Trade_scale);
        $level3_Trade_scale = explode('-',$prod->level3_Trade_scale);
        $level1_Audit_scale = explode('-',$prod->level1_Audit_scale);
        $level2_Audit_scale = explode('-',$prod->level2_Audit_scale);
        $level3_Audit_scale = explode('-',$prod->level3_Audit_scale);
        $level1_IT_scale = explode('-',$prod->level1_IT_scale);
        $level2_IT_scale = explode('-',$prod->level2_IT_scale);
        $level3_IT_scale = explode('-',$prod->level3_IT_scale);
        $level1_Operation_scale = explode('-',$prod->level1_Operation_scale);
        $level2_Operation_scale = explode('-',$prod->level2_Operation_scale);
        $level3_Operation_scale = explode('-',$prod->level3_Operation_scale);
        $level1_HR_scale = explode('-',$prod->level1_HR_scale);
        $level2_HR_scale = explode('-',$prod->level2_HR_scale);
        $level3_HR_scale = explode('-',$prod->level3_HR_scale);
        $level1_Others_scale = explode('-',$prod->level1_Others_scale);
        $level2_Others_scale = explode('-',$prod->level2_Others_scale);
        $level3_Others_scale = explode('-',$prod->level3_Others_scale);
        if(count($level1_scale) > 0 && !empty($level1_scale[0]) && !empty($level1_scale[1])){
          $ComplianceLevel1Min += (int)$level1_scale[0];
          $ComplianceLevel1Max += (int)$level1_scale[1];
        }
        if(count($level2_scale) > 0 && !empty($level2_scale[0]) && !empty($level2_scale[1])){
          $ComplianceLeve2Min += (int)$level2_scale[0];
          $ComplianceLeve2Max += (int)$level2_scale[1];
        }
        if(count($level3_scale) > 0 && !empty($level3_scale[0]) && !empty($level3_scale[1])){
          $ComplianceLeve3Min += (int)$level3_scale[0];
          $ComplianceLeve3Max += (int)$level3_scale[1];
        }
        if(count($level1_Legal_scale) > 0 && !empty($level1_Legal_scale[0]) && !empty($level1_Legal_scale[1])){
          $LegalLevel1Min += (int)$level1_Legal_scale[0];
          $LegalLevel1Max += (int)$level1_Legal_scale[1];
        }
        if(count($level2_Legal_scale) > 0 && !empty($level2_Legal_scale[0]) && !empty($level2_Legal_scale[1])){
          $LegalLevel2Min += (int)$level2_Legal_scale[0];
          $LegalLevel2Max += (int)$level2_Legal_scale[1];
        }
        if(count($level3_Legal_scale) > 0 && !empty($level3_Legal_scale[0]) && !empty($level3_Legal_scale[1])){
          $LegalLevel3Min += (int)$level3_Legal_scale[0];
          $LegalLevel3Max += (int)$level3_Legal_scale[1];
        }
        if(count($level1_Risk_scale) > 0 && !empty($level1_Risk_scale[0]) && !empty($level1_Risk_scale[1])){
          $RiskLevel1Min += (int)$level1_Risk_scale[0];
          $RiskLevel1Max += (int)$level1_Risk_scale[1];
        }
        if(count($level2_Risk_scale) > 0 && !empty($level2_Risk_scale[0]) && !empty($level2_Risk_scale[1])){
          $RiskLevel2Min += (int)$level2_Risk_scale[0];
          $RiskLevel2Max += (int)$level2_Risk_scale[1];
        }
        if(count($level3_Risk_scale) > 0 && !empty($level3_Risk_scale[0]) && !empty($level3_Risk_scale[1])){
          $RiskLevel3Min += (int)$level3_Risk_scale[0];
          $RiskLevel3Max += (int)$level3_Risk_scale[1];
        }
        if(count($level1_Credit_scale) > 0 && !empty($level1_Credit_scale[0]) && !empty($level1_Credit_scale[1])){
          $CreditLevel1Min += (int)$level1_Credit_scale[0];
          $CreditLevel1Max += (int)$level1_Credit_scale[1];
        }
        if(count($level2_Credit_scale) > 0 && !empty($level2_Credit_scale[0]) && !empty($level2_Credit_scale[1])){
          $CreditLevel2Min += (int)$level2_Credit_scale[0];
          $CreditLevel2Max += (int)$level2_Credit_scale[1];
        }
        if(count($level3_Credit_scale) > 0 && !empty($level3_Credit_scale[0]) && !empty($level3_Credit_scale[1])){
          $CreditLevel3Min += (int)$level3_Credit_scale[0];
          $CreditLevel3Max += (int)$level3_Credit_scale[1];
        }
        if(count($level1_Treasury_scale) > 0 && !empty($level1_Treasury_scale[0]) && !empty($level1_Treasury_scale[1])){
          $TreasuryLevel1Min += (int)$level1_Treasury_scale[0];
          $TreasuryLevel1Max += (int)$level1_Treasury_scale[1];
        }
        if(count($level2_Treasury_scale) > 0 && !empty($level2_Treasury_scale[0]) && !empty($level2_Treasury_scale[1])){
          $TreasuryLevel2Min += (int)$level2_Treasury_scale[0];
          $TreasuryLevel2Max += (int)$level2_Treasury_scale[1];
        }
        if(count($level3_Treasury_scale) > 0 && !empty($level3_Treasury_scale[0]) && !empty($level3_Treasury_scale[1])){
          $TreasuryLevel3Min += (int)$level3_Treasury_scale[0];
          $TreasuryLevel3Max += (int)$level3_Treasury_scale[1];
        }
        if(count($level1_Trade_scale) > 0 && !empty($level1_Trade_scale[0]) && !empty($level1_Trade_scale[1])){
          $TradeFinanceLevel1Min += (int)$level1_Trade_scale[0];
          $TradeFinanceLevel1Max += (int)$level1_Trade_scale[1];
        }
        if(count($level2_Trade_scale) > 0 && !empty($level2_Trade_scale[0]) && !empty($level2_Trade_scale[1])){
          $TradeFinanceLevel2Min += (int)$level2_Trade_scale[0];
          $TradeFinanceLevel2Max += (int)$level2_Trade_scale[1];
        }
        if(count($level3_Trade_scale) > 0 && !empty($level3_Trade_scale[0]) && !empty($level3_Trade_scale[1])){
          $TradeFinanceLevel3Min += (int)$level3_Trade_scale[0];
          $TradeFinanceLevel3Min += (int)$level3_Trade_scale[1];
        }
        if(count($level1_Audit_scale) > 0 && !empty($level1_Audit_scale[0]) && !empty($level1_Audit_scale[1])){
          $InternalAuditLevel1Min += (int)$level1_Audit_scale[0];
          $InternalAuditLevel1Max += (int)$level1_Audit_scale[1];
        }
        if(count($level2_Audit_scale) > 0 && !empty($level2_Audit_scale[0]) && !empty($level2_Audit_scale[1])){
          $InternalAuditLevel2Min += (int)$level2_Audit_scale[0];
          $InternalAuditLevel2Max += (int)$level2_Audit_scale[1];
        }
        if(count($level3_Audit_scale) > 0 && !empty($level3_Audit_scale[0]) && !empty($level3_Audit_scale[1])){
          $InternalAuditLevel3Min += (int)$level3_Audit_scale[0];
          $InternalAuditLevel3Max += (int)$level3_Audit_scale[1];
        }
        if(count($level1_IT_scale) > 0 && !empty($level1_IT_scale[0]) && !empty($level1_IT_scale[1])){
          $ITLevel1Min += (int)$level1_IT_scale[0];
          $ITLevel1Max += (int)$level1_IT_scale[1];
        }
        if(count($level2_IT_scale) > 0 && !empty($level2_IT_scale[0]) && !empty($level2_IT_scale[1])){
          $ITLevel2Min += (int)$level2_IT_scale[0];
          $ITLevel2Max += (int)$level2_IT_scale[1];
        }
        if(count($level3_IT_scale) > 0 && !empty($level3_IT_scale[0]) && !empty($level3_IT_scale[1])){
          $ITLevel3Min += (int)$level3_IT_scale[0];
          $ITLevel3Max += (int)$level3_IT_scale[1];
        }
        if(count($level1_Operation_scale) > 0 && !empty($level1_Operation_scale[0]) && !empty($level1_Operation_scale[1])){
          $OperationLevel1Min += (int)$level1_Operation_scale[0];
          $OperationLevel1Max += (int)$level1_Operation_scale[1];
        }
        if(count($level2_Operation_scale) > 0 && !empty($level2_Operation_scale[0]) && !empty($level2_Operation_scale[1])){
          $OperationLevel2Min += (int)$level2_Operation_scale[0];
          $OperationLevel2Max += (int)$level2_Operation_scale[1];
        }
        if(count($level3_Operation_scale) > 0 && !empty($level3_Operation_scale[0]) && !empty($level3_Operation_scale[1])){
          $OperationLevel3Min += (int)$level3_Operation_scale[0];
          $OperationLevel3Max += (int)$level3_Operation_scale[1];
        }
        if(count($level1_HR_scale) > 0 && !empty($level1_HR_scale[0]) && !empty($level1_HR_scale[1])){
          $HumanResourcesLevel1Min += (int)$level1_HR_scale[0];
          $HumanResourcesLevel1Max += (int)$level1_HR_scale[1];
        }
        if(count($level2_HR_scale) > 0 && !empty($level2_HR_scale[0]) && !empty($level2_HR_scale[1])){
          $HumanResourcesLevel2Min += (int)$level2_HR_scale[0];
          $HumanResourcesLevel2Max += (int)$level2_HR_scale[1];
        }
        if(count($level3_HR_scale) > 0 && !empty($level3_HR_scale[0]) && !empty($level3_HR_scale[1])){
          $HumanResourcesLevel3Min += (int)$level3_HR_scale[0];
          $HumanResourcesLevel3Max += (int)$level3_HR_scale[1];
        }
          if(count($level1_Others_scale) > 0 && !empty($level1_Others_scale[0]) && !empty($level1_Others_scale[1])){
          $OtherLevel1Min += (int)$level1_Others_scale[0];
          $OtherLevel1Max += (int)$level1_Others_scale[1];
        }
        if(count($level2_Others_scale) > 0 && !empty($level2_Others_scale[0]) && !empty($level2_Others_scale[1])){
          $OtherLevel2Min += (int)$level2_Others_scale[0];
          $OtherLevel2Max += (int)$level2_Others_scale[1];
        }
        if(count($level3_Others_scale) > 0 && !empty($level3_Others_scale[0]) && !empty($level3_Others_scale[1])){
          $OtherLevel3Min += (int)$level3_Others_scale[0];
          $OtherLevel3Max += (int)$level3_Others_scale[1];
        }

      }
      if(count($products) > 0){
        $ComplianceLevel1 = (($ComplianceLevel1Min/$count) + ($ComplianceLevel1Max/$count))/2;
        $ComplianceLevel2 = (($ComplianceLeve2Min/$count) + ($ComplianceLeve2Max/$count))/2;
        $ComplianceLevel3 = (($ComplianceLeve3Min/$count) + ($ComplianceLeve3Max/$count))/2;
        $LegalLevel1 = (($LegalLevel1Min/$count) + ($LegalLevel1Max/$count))/2;
        $LegalLevel2 = (($LegalLevel2Min/$count) + ($LegalLevel2Max/$count))/2;
        $LegalLevel3 = (($LegalLevel3Min/$count) + ($LegalLevel3Max/$count))/2;
        $RiskLevel1 = (($RiskLevel1Min/$count) + ($RiskLevel1Max/$count))/2;
        $RiskLevel2 = (($RiskLevel2Min/$count) + ($RiskLevel2Max/$count))/2;
        $RiskLevel3 = (($RiskLevel3Min/$count) + ($RiskLevel3Max/$count))/2;
        $TreasuryLevel1 = (($TreasuryLevel1Min/$count) + ($TreasuryLevel1Max/$count))/2;
        $TreasuryLevel2 = (($TreasuryLevel2Min/$count) + ($TreasuryLevel2Max/$count))/2;
        $TreasuryLevel3 = (($TreasuryLevel3Min/$count) + ($TreasuryLevel3Max/$count))/2;
        $InternalAuditLevel1 = (($InternalAuditLevel1Min/$count) + ($InternalAuditLevel1Max/$count))/2;
        $InternalAuditLevel2 = (($InternalAuditLevel2Min/$count) + ($InternalAuditLevel2Max/$count))/2;
        $InternalAuditLevel3 = (($InternalAuditLevel3Min/$count) + ($InternalAuditLevel3Max/$count))/2;
        $CreditLevel1 = (($CreditLevel1Min/$count) + ($CreditLevel1Max/$count))/2;
        $CreditLevel2 = (($CreditLevel2Min/$count) + ($CreditLevel2Max/$count))/2;
        $CreditLevel3 = (($CreditLevel3Min/$count) + ($CreditLevel3Max/$count))/2;
        $TradeFinanceLevel1 = (($TradeFinanceLevel1Min/$count) + ($TradeFinanceLevel1Max/$count))/2;
        $TradeFinanceLevel2 =(($TradeFinanceLevel2Min/$count) + ($TradeFinanceLevel2Max/$count))/2;
        $TradeFinanceLevel3 =(($TradeFinanceLevel3Min/$count) + ($TradeFinanceLevel3Max/$count))/2;
        $OperationLevel1 = (($OperationLevel1Min/$count) + ($OperationLevel1Max/$count))/2;
        $OperationLevel2 = (($OperationLevel2Min/$count) + ($OperationLevel2Max/$count))/2;
        $OperationLevel3 = (($OperationLevel3Min/$count) + ($OperationLevel3Max/$count))/2;
        $HumanResourcesLevel1 = (($HumanResourcesLevel1Min/$count) + ($HumanResourcesLevel1Max/$count))/2;
        $HumanResourcesLevel2 = (($HumanResourcesLevel2Min/$count) + ($HumanResourcesLevel2Max/$count))/2;
        $HumanResourcesLevel3 = (($HumanResourcesLevel3Min/$count) + ($HumanResourcesLevel3Max/$count))/2;
        $ITLevel1 = (($ITLevel1Min/$count) + ($ITLevel1Max/$count))/2;
        $ITLevel2 = (($ITLevel2Min/$count) + ($ITLevel2Max/$count))/2;
        $ITLevel3 = (($ITLevel3Min/$count) + ($ITLevel3Max/$count))/2;
        $OtherLevel1 = (($OtherLevel1Min/$count) + ($OtherLevel1Max/$count))/2;
        $OtherLevel2 = (($OtherLevel2Min/$count) + ($OtherLevel2Max/$count))/2;
        $OtherLevel3 = (($OtherLevel3Min/$count) + ($OtherLevel3Max/$count))/2;

        $newprod[] = ['','TOTAL',$ComplianceLevel1, $ComplianceLevel2, $ComplianceLevel3, $LegalLevel1, $LegalLevel2, $LegalLevel3, $RiskLevel1, $RiskLevel2, $RiskLevel3, $TreasuryLevel1, $TreasuryLevel2, $TreasuryLevel3, $InternalAuditLevel1, $InternalAuditLevel2, $InternalAuditLevel3, $CreditLevel1, $CreditLevel2, $CreditLevel3, $TradeFinanceLevel1, $TradeFinanceLevel2, $TradeFinanceLevel3, $OperationLevel1, $OperationLevel2, $OperationLevel3, $HumanResourcesLevel1, $HumanResourcesLevel2, $HumanResourcesLevel3, $ITLevel1, $ITLevel2, $ITLevel3, $OtherLevel1, $OtherLevel2,$OtherLevel3];
      }
      return $newprod;
    }

    public function downloadExcelFile($displayMonth, $displayYear){
      
      $user = Auth::user()->toarray();

      if ( $user['affiliation_id'] === 0 ) {
        $parentSql = 'IN (1,2)';
      } else {
        $parentSql = "=".(string)$user['affiliation_id'];
      }

      //Month in database is from 1 to 12, that's why +1
      $displayMonth += 1;
      $dateQueryString = "('".(string)$displayMonth."-".(string)$displayYear."'";

      //3 months period
      for ( $a=1; $a < 3; ++$a ) {
        $previousMonth = $displayMonth - $a;
        if ($previousMonth === 0) {
          $previousMonth = 12;
          $yearValue = $displayYear - 1;
        } else if ( $previousMonth === -1 ) {
          $previouosMonth = 11;
          $yearValue = $displayYear - 1;
        } else {
          $yearValue = $displayYear;
        }
        $dateQueryString .= ",'" . $previousMonth . "-" . $yearValue . "'";
      }
      $dateQueryString .= ")";

      try {
        DB::enableQueryLog();
        //Query to select users with 2 or more records within 3 months period.
        $multipleDecorations = DB::select("SELECT a.name as bank_name, a.u_name as submitter_name, bankuser.name, bankuser.khemer_name, 
                              bankuser.id_number, bankuser.sex, bankuser.date_of_birth, decoration_form.* FROM `decoration_form`
                              INNER JOIN
                              (Select users.affiliation_id as affiliation_id, affiliations.name, affiliations.id, users.name as u_name, users.id as af_idDF from affiliations 
                              left Join users on affiliation_id = affiliations.id
                              where parent_id ".$parentSql." ) as a on af_idDF = decoration_form.associateId
                              INNER JOIN bankuser on bankuser.bankUserId = decoration_form.bankUserId
                              Where decoration_form.bankuserId in (
                                  select bankuserId from decoration_form
                                  where submission_month_year in ".$dateQueryString." and associateId in 
                                  (
                                    select id from users where affiliation_id in 
                                    (select id from affiliations where parent_id ".$parentSql." ) 
                                  )
                                  group by bankuserId having count(bankuserId) > 1
                              ) ORDER BY `bankuser`.`name` ASC");

        $multipleDecorations = json_decode(json_encode($multipleDecorations), true);

        $newprod = [];
        //excel header
        $newprod[] = ['Bank Name', 'Submitter Name',  'Name (EN)','Name (KH)', 'ID Number', 'Sex', 
        'Date of Birth','Title / Position','Years of Services','Date of Commencement ','Monthly Gross Salary',
        'Previous Working Place', 'effective_resignation', 'resignation_reason', 'userType', 'Level', 'Core Area',
         'Submission M-Y'];
        
        foreach($multipleDecorations as $row){
          unset($row['submission_date'], $row['pid'], $row['bankUserId'], 
            $row['associateId'], $row['timeStamp'], 
            $row['created_at'], $row['updated_at'] 
          );
          array_push($newprod, $row);
        }

        array_push($newprod, [''], ['Duplicate User']);
        
        try {
          DB::enableQueryLog();
          $duplicateUsers = DB::select("SELECT a.name as bank_name, a.u_name as submitter_name, bankuser.name, bankuser.khemer_name, bankuser.id_number, bankuser.sex, bankuser.date_of_birth, decoration_form.* FROM `decoration_form` 
                                        INNER JOIN
                                        (Select users.affiliation_id as affiliation_id, affiliations.name, affiliations.id, users.name as u_name, users.id as af_idDF from affiliations 
                                          left Join users on affiliation_id = affiliations.id
                                          where parent_id ".$parentSql." ) as a on af_idDF = decoration_form.associateId
                                        INNER JOIN duplicate on duplicate.decorationId = decoration_form.pid
                                        INNER JOIN bankuser on bankuser.bankUserId = decoration_form.bankUserId or bankuser.bankUserId = duplicate.bankUserIdOld
                                        WHERE submission_month_year in ".$dateQueryString." ORDER BY `bankuser`.`name`  ASC"
                                      );

          $duplicateUsers = json_decode(json_encode($duplicateUsers), true);

          foreach($duplicateUsers as $row){
            unset($row['submission_date'], $row['pid'], $row['bankUserId'], 
              $row['associateId'], $row['timeStamp'], 
              $row['created_at'], $row['updated_at'] 
            );
            array_push($newprod, $row);
          }
        } catch (Exception $e) {
          echo $e->getMessage();
        }

        return \Excel::download(new ExcelsExport($newprod), 'Matching Report.xls');
        die;

      } catch (Exception $e) {
        echo $e->getMessage();
      }
    }
  
    public function saveBankUser(Request $request)
    {
      $data=$request->all();
      
      $duplicateArray = [];
      $userDatas = $data['totalUsers'];
      $result = $this->setBankIdForDecorForm($userDatas, $duplicateArray );
      $setStat = $this->setDeclarationStat($data['declareStat'], $data['users'][0]['associateId']);
      if($result) {
        return response()->json([
          'success' => true,
          'msg' => 'Data successfully imported.',
          'status'=>200
        ]);
      }
      else
      {
        return response()->json([
          'success' => true,
          'msg' => 'Error while adding details.',
          'status'=>200
        ]);
      }
    }
  
    public function getBankStats($id) {
      $now = \Carbon\Carbon::now();
      $currentYear = $now->year;
      $currentMonth = $now->month;
      $monthData = DB::table('statcounts')->where('associateId', '=', $id)
                                          ->whereYear('createdAt', '=', $currentYear)
                                          ->whereMonth('createdAt', '=', $currentMonth)
                                          ->limit(1);
      $jsonMonth = json_decode($monthData->get(), true);
      return response()->json([ 'success' => true,
                                'msg' => 'Bank Status',
                                'status'=>200,
                                'data'=>$jsonMonth,
      ]);
    }

    public function setStats($data,$userId)
    {
     $now = \Carbon\Carbon::now();
     $currentYear = $now->year;
     $currentMonth = $now->month;
     $monthData = DB::table('statcounts')->where('associateId', '=', $userId)
                                          ->whereYear('createdAt', '=', $currentYear)
                                          ->whereMonth('createdAt', '=', $currentMonth)
                                          ->limit(1);
     $jsonMonth = json_decode($monthData->get(), true);

      $allData[]=array('associateId'=>$userId,'total'=>$data['total'],'male'=>$data['male'],'female'=>$data['female']);

      if(isset($jsonMonth) && count($jsonMonth)){
        try {
          DB::table('statcounts')->where('statCountId', '=', $jsonMonth[0]['statCountId'])->limit(1)->update(['associateId'=>$userId,'total'=>$data['total'],'male'=>$data['male'],'female'=>$data['female']]);
          return response()->json([
            'success' => true,
            'msg' => 'Employee population successfully updated for this month.',
            'status'=>200
          ]);
        } catch (Exception $e) {
          return response()->json(['message'=>$e->getMessage()], Response::HTTP_INTERNAL_SERVER_ERROR);
        }

      }else{
        try {
          DB::table('statcounts')->insert($allData);
          return response()->json([
            'success' => true,
            'msg' => 'Employee population successfully imported.',
            'status'=>200
          ]);
        } catch (Exception $e) {
          return response()->json(['message'=>$e->getMessage()], Response::HTTP_INTERNAL_SERVER_ERROR);
        }
      }

    }

    //Set Declaration Stat
    public function setDeclarationStat($data, $userId) {
      $now = \Carbon\Carbon::now();
      $currentYear = $now->year;
      $currentMonth = $now->month;
      $monthData = DB::table('statcounts')->where('associateId', '=', $userId)
                                          ->whereYear('createdAt', '=', $currentYear)
                                          ->whereMonth('createdAt', '=', $currentMonth)
                                          ->limit(1);
      $jsonMonth = json_decode($monthData->get(), true);

      $allData[] = array('associateId' => $userId, 'declarationStat' => $data['declarationStat']);

      if (isset($jsonMonth) && count($jsonMonth)){

        try {
          DB::table('statcounts')->where('statCountId', '=', $jsonMonth[0]['statCountId'])->limit(1)->update(['associateId' => $userId,'declarationStat' => $data['declarationStat']]);
          return response()->json([
            'success' => true,
            'msg' => 'Declaration Status successfully imported.',
            'status'=>200
          ]);
        } catch (Exception $e) {
          return response()->json(['message'=>$e->getMessage()], Response::HTTP_INTERNAL_SERVER_ERROR);
        }

      } else {

        try {
          DB::table('statcounts')->insert($allData);
          if($data['declarationStat'] == 1){
            return response()->json([
              'success' => true,
              'msg' => 'No Declaration successfully imported.',
              'status'=>200
            ]);
          } else {
            return response()->json([
              'success' => true,
              'msg' => 'Declared successfully imported.',
              'status'=>200
            ]);
          }
        } catch (Exception $e) {
          return response()->json(['message'=>$e->getMessage()], Response::HTTP_INTERNAL_SERVER_ERROR);
        }

      }
    }

    //save CompanyEmployee Stat
    public function saveStats(Request $request){
      $data = $request->all();

      return $this->setStats($data['stats'], $data['userId']);
      
    }

    //save Company Declare Stat 
    public function saveDeclareStats(Request $request){
      $data = $request->all();
      return $this->setDeclarationStat($data['declareStat'], $data['userId']);
    }
    
    //Checking For Existing Records From BankUser Tbl
    public function setBankIdForDecorForm($userDatas, $duplicateArray) {
      //This Sql select the most recent user from the database group by same id_number
      $existedUserSql = DB::table('bankuser')->select('*')
        ->wherein('bankUserId', function($query) use ($userDatas) {
          $query->selectRaw('max(bankUserId)')->from('bankuser')
            ->wherein('id_number', $userDatas['passIds'])
            ->groupBy('id_number')->get();
          })
        ->get();

      $existedUsers = json_decode($existedUserSql, true);

      $oldIds=[];
      $dupPassIds = [];
      $noDuplicate = true;

      $bankIds = [];

      foreach( $existedUsers as $u ){
        array_push($bankIds, $u['bankUserId']);
      }

      $existedBankUser = DB::table('decoration_form')->select('*')
        ->wherein('pid', function($query) use ($bankIds) {
          $query->selectRaw('max(pid)')->from('decoration_form')
          ->wherein('bankUserId', $bankIds)
          ->groupBy('bankUserId')->get();
        })->get();

      $existedBankUser = json_decode($existedBankUser, true);
      
      for ($j = 0; $j < count($existedUsers); $j++) {
        for ($i = 0; $i < count($userDatas['user']); $i++) {

          $sameId = $userDatas['user'][$i]['id_number'] == $existedUsers[$j]['id_number'];
          $sameName = $userDatas['user'][$i]['name'] == $existedUsers[$j]['name'];
          $sameSex = $userDatas['user'][$i]['sex'] == $existedUsers[$j]['sex'];
          $userDatas['user'][$i]['date_of_birth'] == $existedUsers[$j]['date_of_birth'];
          $sameBirthday = $this->convertDmyToYmd($userDatas['user'][$i]['date_of_birth']) == $this->convertDmyToYmd2($existedUsers[$j]['date_of_birth']);

          //same id different name, add into duplicate array
          if ($sameId) {
            if ($sameName && $sameBirthday && $sameSex) {
              $userDatas['detail'][$i]['bankUserId'] = $existedUsers[$j]['bankUserId'];
            } else {
              array_push($dupPassIds, $existedUsers[$j]['id_number']);
              array_push($oldIds, $existedUsers[$j]['bankUserId']);
              $noDuplicate = false;
            }
          }
        }
      }
      
      $dupInfo = []; //contain previous id and passport Id
      if (!$noDuplicate) {
        $dupInfo = [
          'id_number' => $dupPassIds,
          'oldId' => $oldIds,
        ];
      }

      //If there is no Duplicate, No user needs to insert 
      if ($noDuplicate && (count($userDatas['passIds']) == count($existedUsers))) {
        foreach ($userDatas['detail'] as $userData) {
          // get the submission year and month
          $submissionYearMonth = $this->convertToMonthYear($userData["submission_date"], "/", 0, 1);
          $userData['effective_resignation'] = $this->convertDateForDB($userData['effective_resignation']);
          $userData['date_of_commencement'] = $this->convertDateForDB($userData['date_of_commencement']);
          // Remove here the ones that date is not right ?
          DB::table('decoration_form')->updateOrInsert(
            [
              'bankUserId' => $userData['bankUserId'],
              'userType' => $userData['userType'],
              'submission_month_year' => $submissionYearMonth
            ],
            $userData
          );
        }
        return true;
      } else {
        // remove the duplicates
        $insertUsers = $this->insertNewUser($userDatas, $dupInfo);
        return $insertUsers;
      }
    }

    public function convertDmyToYmd2($dateValue) {
      if ($dateValue === NULL) {
        return '';
      }
      $separatedDate = explode("-", $dateValue);
      $newString = $separatedDate[0] . "/" . $separatedDate[1] . "/" . $separatedDate[2];

      $newformat = date('Y/m/d',strtotime($newString));
      return $newformat;
    }

    public function convertDmyToYmd($dateValue) {
      if ($dateValue === NULL) {
        return '';
      }
      $separatedDate = explode("/", $dateValue);
      $newString = $separatedDate[2] . "/" . $separatedDate[1] . "/" . $separatedDate[0];

      $newformat = date('Y/m/d',strtotime($newString));
      return $newformat;
    }

    private function convertDateForDB($dateValue) {
      if ($dateValue === NULL) {
        return '';
      }
      $separatedDate = explode("/", $dateValue);
      $newString = $separatedDate[2] . "-" . $separatedDate[1] . "-" . $separatedDate[1];

      $newformat = date('Y-m-d', strtotime($newString));
      return $newformat;
    }

    //Insert New Users Into User Table and Return the Id_Numbers
    private function insertNewUser($userDatas, $dupInfo ) {
      $newBankUser = [];
      $newPassIds = [];
      $newDecorationUsers = [];

      //save new PassportIDs to query back the bankUserId, after insert
      for ($i = 0; $i < count($userDatas['user']); $i++ ) {
        $userDatas['user'][$i]['date_of_birth'] = $this->convertDmyToYmd($userDatas['user'][$i]['date_of_birth']);
        $userDatas['detail'][$i]['date_of_commencement'] = $this->convertDmyToYmd($userDatas['detail'][$i]['date_of_commencement']);
        $userDatas['detail'][$i]['submission_month_year'] = $this->convertToMonthYear($userDatas['detail'][$i]['submission_date'], "/", 0, 1);

        if ($userDatas['detail'][$i]['effective_resignation']){
          $userDatas['detail'][$i]['effective_resignation'] = $this->convertDmyToYmd($userDatas['detail'][$i]['effective_resignation']);
        }
        
        // you have to check that is not a duplicate as well.
        if ($userDatas['detail'][$i]['bankUserId'] == NULL ) {
          array_push($newBankUser, $userDatas['user'][$i]);
          array_push($newPassIds, $userDatas['user'][$i]['id_number']);
          array_push($newDecorationUsers, $userDatas['detail'][$i]);
        }
      }
    
      $insertNewUsers = DB::table('bankuser')->insert($newBankUser);
      
      if (!$insertNewUsers) return false;

      $selectInsertUsers = DB::table('bankuser')->select('*')
        ->wherein('bankUserId', function($query) use ($newPassIds) {
          $query->selectRaw('max(bankUserId)')->from('bankuser')
          ->wherein('id_number', $newPassIds)
          ->groupBy('id_number')->get();
        })
        ->get();
        
      $usersNew = json_decode($selectInsertUsers, true);

      foreach ($usersNew as $user) {
        for ($i = 0; $i < count($newBankUser); $i++) {
          //same id different name, add into duplicate array
          if ($newBankUser[$i]['id_number'] == $user['id_number']) {
            $newDecorationUsers[$i]['bankUserId'] = $user['bankUserId'];
          }
        }
      }

      $insertDecorTbl = DB::table('decoration_form')->insert($newDecorationUsers);

      if (!$insertDecorTbl) return false;

      //Handle Duplicate Insertion
      if ($dupInfo){
        $selectDuplicateDatas = DB::table('bankuser')
          ->join('decoration_form', 'bankuser.bankUserId', '=', 'decoration_form.bankUserId')
          ->select('bankuser.*', 'decoration_form.pid AS decorationId')
          ->wherein('bankuser.bankUserId', function($query) use ($newPassIds) {
            $query->selectRaw('max(bankUserId)')->from('bankuser')
              ->wherein('id_number', $newPassIds)
              ->groupBy('id_number')->get();
          });

        $selectDuplicateDatas = $selectDuplicateDatas->addSelect(DB::raw("'null' as bankUserIdOld"))->get();
        
        $dupArray = json_decode($selectDuplicateDatas, true);

        foreach ($dupArray as &$dupData) {
          for ($i = 0; $i < count($dupInfo['oldId']); $i++) {
            if ($dupData['id_number'] == $dupInfo['id_number'][$i]) {
              $dupData['bankUserIdOld'] = $dupInfo['oldId'][$i];
            }
          }
        }

        $insertDup = DB::table('duplicate')->insert($dupArray);

        return $insertDup;
      }
      return true;
    }

    private function convertToMonthYear($date, $separator, $yearPosition = 0, $monthPosition = 1) {
      $explodedDate = explode($separator, $date);
      return $explodedDate[$monthPosition] . '-' . $explodedDate[$yearPosition];
    }

    public function saveBankUserNew(Request $request) {
      $data=$request->all();
      
      $duplicateArray = [];
      $userDatas = $data['totalUsers'];
      $result = $this->setBankIdForDecorForm($userDatas, $duplicateArray );

      $id = $userDatas['detail'][0]['associateId'];
      $filename = session()->get('filename');

      $fileResult = DB::table('file')->where('fileName', '=', $filename)->limit(1)->get();
      $jsonfile = json_decode($fileResult, true);
      if(isset($jsonfile) && count($jsonfile)){
        if($jsonfile[0]['fileName'] == session()->get('filename')) {
          DB::table('file')->where('fileName', '=', $filename)->limit(1)->update(['fileName' => session()->get('filename')]);
        } else{
          Log::info("error");
        }
      } else {
        DB::table('file')->insert(
          ['associateId' => $id, 'fileName' => session()->get('filename')]
        );
      }

      if ($result) {
        $this->setStats($data['stats'], $data['users'][0]['associateId']);
        $this->setDeclarationStat($data['declareStat'], $data['users'][0]['associateId']);
        return response()->json([
          'success' => true,
          'msg' => 'Data successfully imported.',
          'status'=>200
        ]);
      } else {
        return response()->json([
          'success' => false,
          'msg' => 'Error while adding details.',
          'status'=>200
        ]);
      }
    }
    public function clearCurrentMonth($id){
      try  {
        $currentMY = date('m-Y');
        $deleteSql = DB::table('decoration_form')->where('associateId', $id)->where('submission_month_year', $currentMY)->delete();
        if($deleteSql){
          return response()->json(['message'=> 'Bank details deleted successfully.'], Response::HTTP_OK);
        }else{
          return response()->json(['message'=> 'There is no current month submission to clear out.'], Response::HTTP_OK );
        }
      } catch (Exception $e) {
        return response()->json(['message'=>$e->getMessage()], Response::HTTP_INTERNAL_SERVER_ERROR);
      }
    }

    public function getDataForReportTable( $monthYear ) {

      $user = Auth::user()->toarray();
      $finalResult = [];
      if ( $user['role'] === 1 ) {
        $parentIdSql = 'IN (1,2)';
      } else if ( $user['role'] === 2 ) {
        $parentIdSql = '='.$user['affiliation_id'];
      }
    
      $outgoingStats = DB::select('SELECT  a.af_idDF, a.name, decoration_form.userType, count(DISTINCT decoration_form.bankUserId) as IOvalue FROM decoration_form
      RIGHT JOIN (Select affiliations.name, affiliations.id, users.id as af_idDF from affiliations 
      LEFT JOIN users on affiliation_id = affiliations.id
      WHERE parent_id '.$parentIdSql.') as a on a.af_idDF = decoration_form.associateId AND userType = 2 WHERE decoration_form.submission_month_year = \''.$monthYear.'\' GROUP BY associateId');

      $incomingStats = DB::select('SELECT  a.af_idDF, a.name, decoration_form.userType, count(DISTINCT decoration_form.bankUserId) as IOvalue FROM decoration_form
      RIGHT JOIN (Select affiliations.name, affiliations.id, users.id as af_idDF from affiliations 
      LEFT JOIN users on affiliation_id = affiliations.id
      where parent_id '.$parentIdSql.') as a on a.af_idDF = decoration_form.associateId and userType = 1 WHERE decoration_form.submission_month_year = \''.$monthYear.'\' GROUP BY associateId');

      $listMonthYear = explode('-', $monthYear);
      $yearMonth = $listMonthYear[1].'-'.$listMonthYear[0];

      $declarationStats = DB::select('SELECT  a.af_idDF, a.name, MAX(statcounts.declarationStat) AS declareStatus FROM `statcounts`
      RIGHT JOIN (SELECT affiliations.name, affiliations.id, users.id AS af_idDF FROM affiliations 
      LEFT JOIN users ON affiliation_id = affiliations.id WHERE parent_id '.$parentIdSql.') as a on a.af_idDF = statcounts.associateId WHERE statcounts.createdAt LIKE \'%'.$yearMonth.'%\' GROUP by a.name');
      
      $allBank = DB::select('SELECT * FROM `affiliations` WHERE parent_id '.$parentIdSql.'');
      
      /* put incoming value and outgoing value into a list */
      $incomingList = $this->handleReportTableData($incomingStats, [], 1 );
      $incomingAndOutgoingList = $this->handleReportTableData($outgoingStats, $incomingList, 2 );
      $finalList = $this->handleDeclarationStats( $incomingAndOutgoingList, $declarationStats);
      $finalList_2 = $this->listAllTheBank($finalList, $allBank);

      $result = [];
      foreach ( $finalList_2 as $row) {
        $row['totalValue'] = $row['incomingValue'] + $row['outgoingValue'];
        array_push($result, $row);
      }

      return response()->json([ 'success' => true,
                                'msg' => 'Report Table',
                                'status'=>200,
                                'data' => $result
      ]);
    }

    //Add other banks that are new to the system, or did not declare banks
    private function listAllTheBank ($finalList, $allBank) {
      $allBank = json_decode(json_encode($allBank), true);
      foreach ( $allBank as $eachRecord ) {
        $key = $eachRecord['name'];
        if ( !array_key_exists($eachRecord['name'], $finalList ) ) {
          $finalList[$key] = array('bankName' => $eachRecord['name'], 'outgoingValue' => 0, 'incomingValue' => 0, 'note' => 'Did Not Declare' );
        }
      }

      return $finalList;
    }

    private function handleDeclarationStats ( $statsIncomingOutgoing, $declarationStatusList) {

      $declarationStatusList = json_decode(json_encode($declarationStatusList), true);
      
      foreach ( $declarationStatusList as $eachRecord ) {
        $key = $eachRecord['name'];
        if ( !array_key_exists($eachRecord['name'], $statsIncomingOutgoing ) ) {
          $statsIncomingOutgoing[$key] = array('bankName' => $eachRecord['name'], 'outgoingValue' => 0, 'incomingValue' => 0, 'note' => 'Did Not Declare' );
        }

        if ( $eachRecord['declareStatus'] === 1  ) {
          $statsIncomingOutgoing[$key]['note'] = 'None To Declare';
        } else if ( $eachRecord['declareStatus'] === 2 ) {
          $statsIncomingOutgoing[$key]['note'] = 'Already Declare';
        } else {
          $statsIncomingOutgoing[$key]['note'] = 'Did Not Declare';
        }

      }

      return $statsIncomingOutgoing;
    }
    //Type 1 = incoming , type 2 = outgoing
    private function handleReportTableData ( $stats, $result, $type ) {
      
      $stats = json_decode(json_encode($stats), true);

      foreach ($stats as $singleRow) {
        $key = $singleRow['name'];
        if ( !array_key_exists($singleRow['name'], $result ) ) {
          
          if ($type === 2) $result[$key] = array('bankName' => $singleRow['name'], 'outgoingValue' => $singleRow['IOvalue'], 'incomingValue' => 0 );
          else $result[$key] = array('bankName' => $singleRow['name'], 'outgoingValue' => 0, 'incomingValue' => $singleRow['IOvalue'] );
          
        } else {

          if ($singleRow['IOvalue'] > 0) {

            if ($type === 2) $result[$key]['outgoingValue'] += $singleRow['IOvalue'];
            else $result[$key]['incomingValue'] += $singleRow['IOvalue'];

          }
        }
      }
      return $result;
    }

    public function getExcelData() {
      $user = Auth::user()->toarray();
      $userId=$user['id'];
      if($user['role'] == 2){

        if ($user['affiliation_id'] == 1) {
          $affiliation_id = 2;
        }else{
          $affiliation_id = 1;
        }
        $result= DB::select("SELECT bankuser.*, decoration_form.*, users.name,users.id as user_id, af.name as companyName, users.affiliation_id, 
        ( CASE WHEN users.affiliation_id > 2 THEN 
        (SELECT b.name FROM affiliations a INNER JOIN affiliations b ON b.id = a.parent_id WHERE a.id = affiliation_id)
        ELSE af.name END) AS affiliationName FROM `users` LEFT JOIN affiliations as af ON af.id = users.affiliation_id 
        right join decoration_form on decoration_form.associateId=users.id 
        RIGHT join bankuser on bankuser.bankUserId = decoration_form.bankUserId
        where role > ".$user['role']." AND affiliation_id != ".$affiliation_id." AND af.parent_id != ". $affiliation_id." GROUP BY bankuser.id_number"); //group by bankuser.id_number

        // $result= DB::select("SELECT bankuser.*,users.name,users.id as user_id, af.name as companyName, users.affiliation_id, ( CASE
        //        WHEN users.affiliation_id > 2 THEN (SELECT b.name FROM affiliations a INNER JOIN affiliations b ON b.id = a.parent_id WHERE a.id = affiliation_id) ELSE af.name END) AS affiliationName FROM `users` LEFT JOIN affiliations as af ON af.id = users.affiliation_id right join bankuser on bankuser.associateId=users.id where role > ".$user['role']." AND affiliation_id != ".$affiliation_id." AND af.parent_id != ". $affiliation_id);
      } else if($user['role'] == 1) {
        $result= DB::select("SELECT bankuser.*, decoration_form.*, users.name,users.id as user_id, af.name as companyName, users.affiliation_id, 
        ( CASE WHEN users.affiliation_id > 2 THEN 
        (SELECT b.name FROM affiliations a INNER JOIN affiliations b ON b.id = a.parent_id WHERE a.id = affiliation_id)
        ELSE af.name END) AS affiliationName FROM `users` LEFT JOIN affiliations as af ON af.id = users.affiliation_id 
        right join decoration_form on decoration_form.associateId=users.id 
        RIGHT join bankuser on bankuser.bankUserId = decoration_form.bankUserId
        GROUP BY bankuser.id_number"); //group by bankuser.id_number

        // $result= DB::select("SELECT bankuser.*,users.name,users.id as user_id, af.name as companyName, users.affiliation_id, ( CASE
        //       WHEN users.affiliation_id > 2 THEN (SELECT b.name FROM affiliations a INNER JOIN affiliations b ON b.id = a.parent_id WHERE a.id = affiliation_id) ELSE af.name END) AS affiliationName FROM `users` LEFT JOIN affiliations as af ON af.id = users.affiliation_id right join bankuser on bankuser.associateId=users.id");
      } else {
        $result = DB::select("select bankuser.*, decoration_form.*, users.id,users.name as associateName from bankuser
        right join decoration_form on bankuser.bankUserId = decoration_form.bankUserId
        inner join users on decoration_form.associateId=users.id where decoration_form.associateId=".$userId.
        " GROUP BY bankuser.id_number"); //group by bankuser.id_number
      }
      $allcat = $this->getaffiliation($user);
      $coreAreas = $this->getCoreAreas();
      return response()->json([ 'success' => true,
                                'msg' => 'Users list.',
                                'status'=>200,
                                'data'=>$result,
                                'cat' => $allcat,
                                'coreAreas' => $coreAreas,
      ]);
    }

    public function getaffiliation($user) {
      $all = [];
      if($user['role'] == 2){
        $affiliation_parent_id = $this->getSelected($user['affiliation_id']);
        if ($affiliation_parent_id == 2) {
          $parent_id = 1;
        } else {
          $parent_id = 2;
        }
        $affiliation_id = $user['affiliation_id'];
        $all = Affiliation::select(['id','name','parent_id'])->where('id', '!=', $parent_id)->where('parent_id','!=', $parent_id)->get();
      } else if($user['role'] == 1) {
        $all = Affiliation::select(['id','name','parent_id'])->get();
      }
      return $all;
    }

    public static function getSelected($id) {
      $affiliationData  = Affiliation::select('parent_id')->where('id', $id)->first();
      return $affiliationData->parent_id;
    }


    public static function getCoreAreas() {
      return Core_areas::select('name','id')->get();
   }

  private function transformDateTime(string $value, string $format = 'd/m/Y')
  {
    
    //if date is in INT format
    if (  !preg_match("/[-\/]/", $value)  ) {
      try {
        return \PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($value)->format($format);
      } catch ( \ErrorException $e) {
        return '';
      }
    }

    //if date is in d-m-y format
    try  {
      $date = str_replace('/', '-', $value);
      $newDate = date("d/m/Y", strtotime($date));
      return $newDate;
    } catch ( \ErrorException $e) {
      return '';
    }
  }

  private function convertDateToCompare(string $value) {
    $date = str_replace('/', '-', $value);
    return $date;
  }
}
