<?php

namespace App\Http\Controllers\API;

use App\Affiliation;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Illuminate\Http\Response;
use Illuminate\Support\Facades\Auth;
use App\User;
use App\Core_areas;
use DB;

use Illuminate\Support\Facades\Log;
use App\Exports\ExcelMultipleExport;

use App\Exports\ExcelsExport;

class AffiliationController extends Controller
{

    public function index()
    {
        try {
            DB::connection()->enableQueryLog();
            $user = Auth::user()->toarray();

            if ($user) {

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

                } else if ($user['role'] == 1) {
                    $all = Affiliation::select(['id', 'name', 'parent_id'])->get();
                }
                return response()->json([
                    'message' => "Affiliation all Data.",
                    'data' => $all
                ], Response::HTTP_OK);
            } else {
                return response()->json(['message' => 'UnAuthorised'], Response::HTTP_UNAUTHORIZED);
            }
        } catch (Exception $e) {
            return response()->json([
                'message' => $e->getMessage(),
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }


    public function add(Request $request)
    {
        try {
            $user = Auth::user()->toarray();
            if ($user) {
                $checkAff = Affiliation::where('name', $request->bankName)->first();
                if ($checkAff) {
                    return response()->json(['message' => 'Already exist', 'data' => false], Response::HTTP_OK);
                }
                $affiliation = new Affiliation();
                $affiliation->name = $request->bankName;
                $affiliation->parent_id = $request->affiliationId;
                $res = $affiliation->save();
                if ($res) {
                    $all = $this->getList();
                    return response()->json(['message' => 'Affiliation added successfully', 'data' => $all], Response::HTTP_OK);
                } else {
                    return response()->json(['message' => 'Something went wrong!', 'data' => false], Response::HTTP_OK);
                }
            } else {
                return response()->json(['message' => 'UnAuthorised'], Response::HTTP_UNAUTHORIZED);
            }
        } catch (Exception $e) {
            return response()->json([
                'message' => $e->getMessage(),
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }


    public function addCoreAreas(Request $request)
    {
        try {
            $name = $request->name;
            $checkArea = Core_areas::where('name', $name)->first();
            if ($checkArea) {
                return response()->json(['message' => 'Already exists!', 'data' => false], Response::HTTP_OK);
            }
            $core_areas = new Core_areas();
            $core_areas->name = $name;
            $res = $core_areas->save();
            if ($res) {
                $coreAreaData = Core_areas::get();
                return response()->json(['message' => 'Core area added successfully', 'data' => $coreAreaData], Response::HTTP_OK);
            } else {
                return response()->json(['message' => 'Something went wrong!', 'data' => false], Response::HTTP_OK);
            }
        } catch (Exception $e) {
            return response()->json([
                'message' => $e->getMessage(),
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }


    public function getCoreAreas()
    {
        try {
            $user = Auth::user()->toarray();
            if ($user) {
                $coreAreaData = Core_areas::get();
                return response()->json(['message' => 'Core area get successfully', 'data' => $coreAreaData, 'month' => $user['month']], Response::HTTP_OK);
            } else {
                return response()->json(['message' => 'UnAuthorised'], Response::HTTP_UNAUTHORIZED);
            }
        } catch (Exception $e) {
            return response()->json([
                'message' => $e->getMessage(),
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }

    public function updateDeadline(Request $request)
    {
        try {
            $user = Auth::user()->toarray();
            if ($user) {
                $updateDeadline = User::where('id', $user['id'])->update(['month' => $request->months]);
                if ($updateDeadline) {
                    return response()->json(['message' => 'Deadline updation successfully', 'data' => true], Response::HTTP_OK);
                } else {
                    return response()->json(['message' => 'Something went worng!', 'data' => false], Response::HTTP_OK);
                }
            } else {
                return response()->json(['message' => 'UnAuthorised'], Response::HTTP_UNAUTHORIZED);
            }
        } catch (Exception $e) {
            return response()->json([
                'message' => $e->getMessage(),
            ], Response::HTTP_INTERNAL_SERVER_ERROR);
        }
    }


    public static function getList()
    {
        $affilation = Affiliation::select(['id', 'name', 'parent_id'])->get();
        return $affilation;
    }

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

    public function secondExcelDowFile($monthYear)
    {
        try {

            $user = Auth::user()->toarray();

            $explodeStr = explode("-", $monthYear);
            $yearMonth = $explodeStr[1] . "-" . $explodeStr[0];

            if ($user['affiliation_id'] == 0) {
                $parentIdSql = "affiliations.parent_id in (1,2)";
            } else {
                $parentIdSql = "affiliations.parent_id = " . $user['affiliation_id'];
            }

            $incomingHeader[] = ['Association', 'Company', 'Admin', '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 In Turnover Level 4', 'Staff In Turnover Level 4 %', '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 %'];
            $outgoingHeader[] = ['Association', 'Company', 'Admin', '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 Out Turnover Level 4', 'Staff Out Turnover Level 4 %', '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 %'];
            $areaLevelHeader[] = ['Association', 'Company', 'Admin', 'Total Staff',  'Staff Movement', 'Staff Movement %',  'Staff Movement Level 1%', 'Staff Movement Level 2 %', 'Staff Movement Level 3 %', 'Staff Movement Level 4 %', 'Staff Movement Credit %', 'Staff Movement Risk %', 'Staff Movement Compliance %', 'Staff Movement Trade Finance %', 'Staff Movement IT %', 'Staff Movement HR %', 'Staff Movement Internal Audit %', 'Staff Movement Treasury %', 'Staff Movement Operation %',  'Staff Movement Others %'];
            $salaryScaleHeader[] = [
                'Association', 'Company', 'Admin', 'Credit - Level 1', 'Credit - Level 2',    'Credit - Level 3', 'Credit - Level 4',
                'Risk - Level 1', 'Risk - Level 2', 'Risk - Level 3', 'Risk - Level 4',
                'Compliance - Level 1', 'Compliance - Level 2', 'Compliance - Level 3', 'Compliance - Level 4',
                'Trade Finance - Level 1',    'Trade Finance - Level 2',    'Trade Finance - Level 3',    'Trade Finance - Level 4',
                'IT - Level 1',    'IT - Level 2',    'IT - Level 3', 'IT - Level 4',
                'Human Resources - Level 1', 'Human Resources - Level 2', 'Human Resources - Level 3', 'Human Resources - Level 4',
                'Internal Audit - Level 1', 'Internal Audit - Level 2', 'Internal Audit - Level 3', 'Internal Audit - Level 4',
                'Treasury - Level 1', '	Treasury - Level 2', 'Treasury - Level 3', 'Treasury - Level 4',
                'Operation - Level 1',    'Operation - Level 2', 'Operation - Level 3', 'Operation - Level 4',
                'Other - Level 1', '	Other - Level 2', '	Other - Level 3', 'Other - Level 4'
            ];
            $durationServiceHeader[] = [
                'Association', 'Company', 'Admin', 'Total Staff', 'Duration of Services -Total (Days)', 'Duration of Services - Level 1', 'Duration of Services - Level 2', 'Duration of Services - Level 3', 'Duration of Servies - Level 4',
                'Duration of Servies - Credit', 'Duration of Servies - Risk', 'Duration of Servies - Compliance', 'Duration of Servies - Trade Finance', 'Duration of Servies - IT', 'Duration of Servies - HR',
                'Duration of Servies - Internal Audit', 'Duration of Servies - Treasury', 'Duration of Servies - Operation', 'Duration of Servies - Others'
            ];

            $staffIn = $this->staffInOut(1, $parentIdSql, $monthYear, $incomingHeader, $yearMonth);
            $staffOut = $this->staffInOut(2, $parentIdSql, $monthYear, $outgoingHeader, $yearMonth);
            $levelAndAreaAffect = $this->areaLevelAffect($parentIdSql, $monthYear, $areaLevelHeader, $yearMonth);
            $salaryScale = $this->salaryScaleNew($parentIdSql, $monthYear, $salaryScaleHeader, $yearMonth);
            $durationOfService = $this->durationOfServiceNew($parentIdSql, $monthYear, $durationServiceHeader, $yearMonth);

            return \Excel::download(new ExcelMultipleExport($staffIn, $staffOut, [[]], $levelAndAreaAffect, $durationOfService, $salaryScale), 'Statistics Report.xls');

            die;
        } catch (Exception $e) {
            echo $e->getMessage();
        }
    }

    //New Functions for excel
    private static function durationOfServiceNew($parentIdSql, $monthYear, $excelData, $yearMonth)
    {
        $totalDurationSql = "SELECT SUM(DATEDIFF( effective_resignation, date_of_commencement ))";
        $fromClause = "From decoration_form df Where df.userType = 2 and df.associateId = decoration_form.associateId AND df.submission_month_year = \"" . $monthYear . "\"";
        $result = DB::select("SELECT (select a.name from affiliations a where a.id = affiliations.parent_id ) as AssociationName, affiliations.name as CompanyName, users.name as AdminName, statcounts.total,
                            (" . $totalDurationSql . " AS totalDuration " . $fromClause . "),
                            (" . $totalDurationSql . " AS totalDurationLevel1 " . $fromClause . " AND df.level = 'Level 1' ),
                            (" . $totalDurationSql . " AS totalDurationLevel2 " . $fromClause . " AND df.level = 'Level 2' ),
                            (" . $totalDurationSql . " AS totalDurationLevel3 " . $fromClause . " AND df.level = 'Level 3' ),
                            (" . $totalDurationSql . " AS totalDurationLevel4 " . $fromClause . " AND df.level = 'Level 4' ),
                            (" . $totalDurationSql . " AS totalDurationCredit " . $fromClause . " AND df.core_area = 'Credit' ),
                            (" . $totalDurationSql . " AS totalDurationRisk " . $fromClause . " AND df.core_area = 'Risk' ),
                            (" . $totalDurationSql . " AS totalDurationCompliance " . $fromClause . " AND df.core_area Like '%Compliance%' ),
                            (" . $totalDurationSql . " AS totalDurationTrade " . $fromClause . " AND df.core_area = 'Trade Finance' ),
                            (" . $totalDurationSql . " AS totalDurationRisk " . $fromClause . " AND df.core_area = 'IT' ),
                            (" . $totalDurationSql . " AS totalDurationRisk " . $fromClause . " AND df.core_area = 'HR' ),
                            (" . $totalDurationSql . " AS totalDurationRisk " . $fromClause . " AND df.core_area = 'Internal Audit' ),
                            (" . $totalDurationSql . " AS totalDurationRisk " . $fromClause . " AND df.core_area = 'Treasury' ),
                            (" . $totalDurationSql . " AS totalDurationRisk " . $fromClause . " AND df.core_area = 'Operation' ),
                            (" . $totalDurationSql . " AS totalDurationRisk " . $fromClause . " AND df.core_area NOT IN ('Credit','Risk','Trade Finance','IT','HR','Internal Audit','Treasury','Operation') AND df.core_area NOT LIKE '%Compliance%' )
                            from decoration_form
                            inner join users on users.id = decoration_form.associateId
                            inner join affiliations on affiliations.id = users.affiliation_id
                            left join statcounts on statcounts.associateId = users.id AND statcounts.createdAt Like \"%" . $yearMonth . "%\"
                            where ".$parentIdSql." AND decoration_form.userType = 2 AND decoration_form.submission_month_year = \"" . $monthYear . "\" group by users.name ");

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

        $totalStaff = 0;
        foreach ($result as $row) {
            $totalStaff += $row['total'];
            array_push($excelData, $row);
        }

        array_push($excelData, ['', '', 'Total', $totalStaff]);

        return $excelData;
    }

    private static function salaryScaleNew($parentIdSql, $monthYear, $excelData)
    {
        $selectSql = "SELECT CONCAT(MIN(montly_gross_salary), '-', MAX(montly_gross_salary) ) from decoration_form as df WHERE df.associateId = decoration_form.associateId AND df.submission_month_year = \"" . $monthYear . "\"";

        $result = DB::select("SELECT (select a.name from affiliations a where a.id = affiliations.parent_id ) as AssociationName, affiliations.name as CompanyName,users.name as AdminName,

                            (" . $selectSql . " AND df.level = 'Level 1' ) as level1_scale,
                            (" . $selectSql . " AND df.level = 'Level 2' ) as level2_scale,
                            (" . $selectSql . " AND df.level = 'Level 3' ) as level3_scale,
                            (" . $selectSql . " AND df.level = 'Level 4' ) as level4_scale,
                            
                            (" . $selectSql . " AND df.level = 'Level 1' AND df.core_area = 'Credit' ) as level1_scale_credit,
                            (" . $selectSql . " AND df.level = 'Level 2' AND df.core_area = 'Credit' ) as level2_scale_credit,
                            (" . $selectSql . " AND df.level = 'Level 3' AND df.core_area = 'Credit' ) as level3_scale_credit,
                            (" . $selectSql . " AND df.level = 'Level 4' AND df.core_area = 'Credit' ) as level4_scale_credit,
                            
                            (" . $selectSql . " AND df.level = 'Level 1' AND df.core_area = 'Risk' ) as level1_scale_risk,
                            (" . $selectSql . " AND df.level = 'Level 2' AND df.core_area = 'Risk' ) as level2_scale_risk,
                            (" . $selectSql . " AND df.level = 'Level 3' AND df.core_area = 'Risk' ) as level3_scale_risk,
                            (" . $selectSql . " AND df.level = 'Level 4' AND df.core_area = 'Risk' ) as level4_scale_risk,
                            
                            (" . $selectSql . " AND df.level = 'Level 1' AND df.core_area LIKE '%Compliance%' ) as level1_scale_compliance,
                            (" . $selectSql . " AND df.level = 'Level 2' AND df.core_area LIKE '%Compliance%' ) as level2_scale_compliance,
                            (" . $selectSql . " AND df.level = 'Level 3' AND df.core_area LIKE '%Compliance%' ) as level3_scale_compliance,
                            (" . $selectSql . " AND df.level = 'Level 4' AND df.core_area LIKE '%Compliance%' ) as level4_scale_compliance,
                            
                            (" . $selectSql . " AND df.level = 'Level 1' AND df.core_area = 'Trade Finance' ) as level1_scale_trade,
                            (" . $selectSql . " AND df.level = 'Level 2' AND df.core_area = 'Trade Finance' ) as level2_scale_trade,
                            (" . $selectSql . " AND df.level = 'Level 3' AND df.core_area = 'Trade Finance' ) as level3_scale_trade,
                            (" . $selectSql . " AND df.level = 'Level 4' AND df.core_area = 'Trade Finance' ) as level4_scale_trade,
                            
                            (" . $selectSql . " AND df.level = 'Level 1' AND df.core_area = 'IT' ) as level1_scale_IT,
                            (" . $selectSql . " AND df.level = 'Level 2' AND df.core_area = 'IT' ) as level2_scale_IT,
                            (" . $selectSql . " AND df.level = 'Level 3' AND df.core_area = 'IT' ) as level3_scale_IT,
                            (" . $selectSql . " AND df.level = 'Level 4' AND df.core_area = 'IT' ) as level4_scale_IT,
                            
                            (" . $selectSql . " AND df.level = 'Level 1' AND df.core_area = 'HR' ) as level1_scale_HR,
                            (" . $selectSql . " AND df.level = 'Level 2' AND df.core_area = 'HR' ) as level2_scale_HR,
                            (" . $selectSql . " AND df.level = 'Level 3' AND df.core_area = 'HR' ) as level3_scale_HR,
                            (" . $selectSql . " AND df.level = 'Level 4' AND df.core_area = 'HR' ) as level4_scale_HR,

                            (" . $selectSql . " AND df.level = 'Level 1' AND df.core_area = 'Internal Audit' ) as level1_scale_IA,
                            (" . $selectSql . " AND df.level = 'Level 2' AND df.core_area = 'Internal Audit' ) as level2_scale_IA,
                            (" . $selectSql . " AND df.level = 'Level 3' AND df.core_area = 'Internal Audit' ) as level3_scale_IA,
                            (" . $selectSql . " AND df.level = 'Level 4' AND df.core_area = 'Internal Audit' ) as level4_scale_IA,
                            
                            (" . $selectSql . " AND df.level = 'Level 1' AND df.core_area = 'Treasury' ) as level1_scale_Treasury,
                            (" . $selectSql . " AND df.level = 'Level 2' AND df.core_area = 'Treasury' ) as level2_scale_Treasury,
                            (" . $selectSql . " AND df.level = 'Level 3' AND df.core_area = 'Treasury' ) as level3_scale_Treasury,
                            (" . $selectSql . " AND df.level = 'Level 4' AND df.core_area = 'Treasury' ) as level4_scale_Treasury,
                            
                            (" . $selectSql . " AND df.level = 'Level 1' AND df.core_area = 'Operation' ) as level1_scale_Operation,
                            (" . $selectSql . " AND df.level = 'Level 2' AND df.core_area = 'Operation' ) as level2_scale_Operation,
                            (" . $selectSql . " AND df.level = 'Level 3' AND df.core_area = 'Operation' ) as level3_scale_Operation,
                            (" . $selectSql . " AND df.level = 'Level 4' AND df.core_area = 'Operation' ) as level4_scale_Operation,
                    
                            (" . $selectSql . " AND df.level = 'Level 1' AND df.core_area NOT IN ('Credit','Risk','Trade Finance','IT','HR','Internal Audit','Treasury','Operation') AND df.core_area NOT LIKE '%Compliance%' ) as level1_scale_Operation,
                            (" . $selectSql . " AND df.level = 'Level 2' AND df.core_area NOT IN ('Credit','Risk','Trade Finance','IT','HR','Internal Audit','Treasury','Operation') AND df.core_area NOT LIKE '%Compliance%' ) as level2_scale_Operation,
                            (" . $selectSql . " AND df.level = 'Level 3' AND df.core_area NOT IN ('Credit','Risk','Trade Finance','IT','HR','Internal Audit','Treasury','Operation') AND df.core_area NOT LIKE '%Compliance%' ) as level3_scale_Operation,
                            (" . $selectSql . " AND df.level = 'Level 4' AND df.core_area NOT IN ('Credit','Risk','Trade Finance','IT','HR','Internal Audit','Treasury','Operation') AND df.core_area NOT LIKE '%Compliance%' ) as level4_scale_Operation
                    
                            from decoration_form
                            inner join users on users.id = decoration_form.associateId
                            inner join affiliations on affiliations.id = users.affiliation_id
                            
                            where ".$parentIdSql." AND decoration_form.submission_month_year = \"" . $monthYear . "\" group by users.name ");



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

        foreach ($result as $row) {
            array_push($excelData, $row);
        }
        return $excelData;
    }

    private static function areaLevelAffect($parentIdSql, $monthYear, $excelData, $yearMonth)
    {
        $selectSql = "SELECT COUNT(DISTINCT bankUserId, userType) from decoration_form df where df.associateId = decoration_form.associateId AND df.submission_month_year = \"" . $monthYear . "\"";
        $result = DB::select("SELECT (select a.name from affiliations a where a.id = affiliations.parent_id ) as AssociationName, affiliations.name as CompanyName,users.name as AdminName,  statcounts.total,
                            (" . $selectSql . ") as staffMovement,
                            (" . $selectSql . ")/statcounts.total*100 as staffMovementPercent,
                            (" . $selectSql . " AND df.level = 'level 1' )/statcounts.total*100 as staffMovementLevel1,
                            (" . $selectSql . " AND df.level = 'level 2' )/statcounts.total*100 as staffMovementLevel2,
                            (" . $selectSql . " AND df.level = 'level 3' )/statcounts.total*100 as staffMovementLevel3,
                            (" . $selectSql . " AND df.level = 'level 4' )/statcounts.total*100 as staffMovementLevel4,
                            (" . $selectSql . " AND df.core_area = 'Credit' )/statcounts.total*100 as staffMovementCredit,
                            (" . $selectSql . " AND df.core_area = 'Risk' )/statcounts.total*100 as staffMovementRisk,
                            (" . $selectSql . " AND df.core_area like '%Compliance%' )/statcounts.total*100 as staffMovementCompliance,
                            (" . $selectSql . " AND df.core_area = 'Trade Finance' )/statcounts.total*100 as staffMovementFinance,
                            (" . $selectSql . " AND df.core_area = 'IT' )/statcounts.total*100 as staffMovementIT,
                            (" . $selectSql . " AND df.core_area = 'HR' )/statcounts.total*100 as staffMovementHR,
                            (" . $selectSql . " AND df.core_area = 'Internal Audit' )/statcounts.total*100 as staffMovementInternalAudit,
                            (" . $selectSql . " AND df.core_area = 'Treasury' )/statcounts.total*100 as staffMovementTreasury,
                            (" . $selectSql . " AND df.core_area = 'Operation' )/statcounts.total*100 as staffMovementOperation,
                            (" . $selectSql . " AND df.core_area NOT IN ('Credit','Risk','Trade Finance','IT','HR','Internal Audit','Treasury','Operation') AND df.core_area NOT LIKE '%Compliance%' )/statcounts.total*100 as staffMovementOthers
                            FROM decoration_form
                            inner join users on users.id = decoration_form.associateId
                            inner join affiliations on affiliations.id = users.affiliation_id
                            left join statcounts on statcounts.associateId = users.id AND statcounts.createdAt Like \"%" . $yearMonth . "%\"
                            where ".$parentIdSql." AND decoration_form.submission_month_year = \"" . $monthYear . "\" group by users.name
                            ");

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

        $totalStaff = 0;
        $totalMovement = 0;
        foreach ($result as $row) {
            $totalStaff += $row['total'];
            $totalMovement += $row['staffMovement'];
            array_push($excelData, $row);
        }

        array_push($excelData, ['', '', 'Total', $totalStaff, $totalMovement]);

        return $excelData;
    }

    private static function staffInOut($userType, $parentIdSql, $monthYear, $excelData, $yearMonth)
    {
        $selectSql = "SELECT COUNT(DISTINCT bankUserId) FROM decoration_form df WHERE df.associateId = decoration_form.associateId AND df.userType = " . $userType . " AND df.submission_month_year = \"" . $monthYear . "\"";
        $fromWhereSql = "from decoration_form df where df.associateId = decoration_form.associateId AND df.userType = " . $userType . " AND df.submission_month_year = \"" . $monthYear . "\"";
        //Some count(distinct bankUserId) is divided by 1 because new excel file returns '' when column value = 0
        $result = DB::select("SELECT (select a.name from affiliations a where a.id = affiliations.parent_id ) as AssociationName, affiliations.name as CompanyName,users.name as AdminName,  statcounts.total,
                            (" . $selectSql . " ) as staffIn,
                            (" . $selectSql . " )/statcounts.total*100 as staffInPercent,
                            (" . $selectSql . " AND df.level = 'level 1' ) as staffInLevel1,
                            (" . $selectSql . " AND level = 'level 1' )/statcounts.total*100 as staffInLevel1Percent,
                            (" . $selectSql . " AND df.level = 'level 2' ) as staffInLevel2,
                            (" . $selectSql . " AND level = 'level 2' )/statcounts.total*100 as staffInLevel2Percent,
                            (" . $selectSql . " AND df.level = 'level 3' ) as staffInLevel3,
                            (" . $selectSql . " AND level = 'level 3' )/statcounts.total*100 as staffInLevel3Percent,
                            (" . $selectSql . " AND df.level = 'level 4' ) as staffInLevel4,
                            (" . $selectSql . " AND level = 'level 4' )/statcounts.total*100 as staffInLevel4Percent,
                            (" . $selectSql . " AND df.core_area = 'Credit' ) as staffCredit,
                            (" . $selectSql . " AND df.core_area = 'Credit' )/statcounts.total*100 as staffCreditPercent,
                            (" . $selectSql . " AND df.core_area = 'Risk' ) as staffRisk,
                            (" . $selectSql . " AND df.core_area = 'Risk' )/statcounts.total*100 as staffRiskPercent,
                            (" . $selectSql . " AND df.core_area like '%Compliance%' ) as staffCompliance,
                            (" . $selectSql . " AND df.core_area like '%Compliance%' )/statcounts.total*100 as staffCompliancePercent,
                            (" . $selectSql . " AND df.core_area = 'Trade Finance' ) as staffTradeFinance,
                            (" . $selectSql . " AND df.core_area = 'Trade Finance' )/statcounts.total*100 as staffTradeFinancePercent,
                            (" . $selectSql . " AND df.core_area = 'IT' ) as staffIT,
                            (" . $selectSql . " AND df.core_area = 'IT' )/statcounts.total*100 as staffITPercent,
                            (" . $selectSql . " AND df.core_area = 'HR' ) as staffHR,
                            (" . $selectSql . " AND df.core_area = 'HR' )/statcounts.total*100 as staffHRPercent,
                            (" . $selectSql . " AND df.core_area = 'Internal Audit' ) as staffInternalAudit,
                            (" . $selectSql . " AND df.core_area = 'Internal Audit' )/statcounts.total*100 as staffInternalAuditPercent,
                            (" . $selectSql . " AND df.core_area = 'Treasury' ) as staffTreasury,
                            (" . $selectSql . " AND df.core_area = 'Treasury' )/statcounts.total*100 as staffTreasuryPercent,
                            (" . $selectSql . " AND df.core_area = 'Operation' ) as staffOperation,
                            (" . $selectSql . " AND df.core_area = 'Operation' )/statcounts.total*100 as staffOperationPercent,
                            (" . $selectSql . " AND df.core_area NOT IN ('Credit','Risk','Trade Finance','IT','HR','Internal Audit','Treasury','Operation') AND df.core_area NOT LIKE '%Compliance%' ) as staffOthers,
                            (" . $selectSql . " AND df.core_area NOT IN ('Credit','Risk','Trade Finance','IT','HR','Internal Audit','Treasury','Operation') AND df.core_area NOT LIKE '%Compliance%' )/statcounts.total*100 as staffOthersPercent
                            from decoration_form
                            inner join users on users.id = decoration_form.associateId
                            inner join affiliations on affiliations.id = users.affiliation_id
                            left join statcounts on statcounts.associateId = users.id AND statcounts.createdAt Like \"%" . $yearMonth . "%\"
                            where ".$parentIdSql." AND userType = " . $userType . " AND decoration_form.submission_month_year = \"" . $monthYear . "\" group by users.name");

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

        $totalStaff = 0;
        $totalInOrOut = 0;
        foreach ($result as $row) {
            $totalStaff += $row['total'];
            $totalInOrOut += $row['staffIn'];
            array_push($excelData, $row);
        }

        array_push($excelData, ['', '', 'Total', $totalStaff, $totalInOrOut]);

        return $excelData;
    }

    //End of new functions
    public static function StaffInTurnover($preDate, $nextDate)
    {
        $products = DB::select("SELECT  bankuser.associateId,
                                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.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 getCoreAreasAccordingSalary($query = "", $where)
    {
        $sql = $query;
        $coreArea = Core_areas::all();
        $count_coreArea = count($coreArea);
        $i = 1;
        foreach ($coreArea as $key => $value) {
            $string = str_replace(' ', '', $value->name); // Replaces all spaces with hyphens.

            $coreAreaName = preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.

            $sql .=  "(SELECT sum(montly_gross_salary) from bankuser as bu WHERE bu.core_area =" . "'" . $value->name . "'" . $where . ") as " . $coreAreaName;
            if ($i != $count_coreArea) {
                $sql .= ", ";
            } else {
                $sql .= " from bankuser";
            }
            $i++;
        }
        return $sql;
    }


    public function InflationInPay($preDate, $nextDate)
    {
        DB::enableQueryLog();

        $level1 = "Level 1";
        $level2 = "Level 2";
        $level3 = "Level 3";
        $where = " AND bu.name=bankuser.name AND bu.id_number = bankuser.id_number AND bu.date_of_birth = bankuser.date_of_birth";
        $query = "SELECT associateId, bankUserId, montly_gross_salary, bankuser.name, bankuser.id_number, bankuser.date_of_birth, ";
        $query .= "(SELECT sum(montly_gross_salary) from bankuser as bu WHERE  bu.level =" . "'" . $level1 . "'" . $where . ") as level1_salary, (SELECT sum(montly_gross_salary) from bankuser as bu WHERE bu.level =" . "'" . $level2 . "'" . $where . ") as level2_salary, (SELECT sum(montly_gross_salary) from bankuser as bu WHERE bu.level =" . "'" . $level3 . "'" . $where . ") as level3_salary, ";

        $sql = $this->getCoreAreasAccordingSalary($query, $where);
        $sql1 = $sql;
        $sql1 .= " where userType=1";
        $sql1 .= " order BY associateId";
        $products = DB::select($sql1);

        // dd(DB::getQueryLog());
        $newprod = [];
        $new_salary = [];
        $pre_salary = [];
        $companyDataArr = [];
        foreach ($products as $prod) {
            $sql2 = "";
            $sql2 = $sql;
            $associateId = $prod->associateId;
            $name = $prod->name;
            $idNumber = $prod->id_number;
            $DOB = $prod->date_of_birth;
            $sql2 .= " where userType=2 AND name = " . "'" . $name . "'" . "  AND id_number = " . "'" . $idNumber . "'" . " AND date_of_birth = " . "'" . $DOB . "'";
            $productsNew = DB::select($sql2);
            unset($prod->associateId);
            unset($prod->name);
            unset($prod->id_number);
            unset($prod->date_of_birth);
            $newProd = (array) $prod;
            $new_salary = $this->filterData($newProd, $associateId);
            if (count($productsNew) > 0) {
                $newProdData = (array) $productsNew[0];
                $pre_salary = $this->filterData($newProdData, $associateId);
            } else {
                $pre_salary = $this->filterData($newProd, $associateId);
            }
            $compayData[$associateId] = $this->getCompayData($associateId, $preDate, $nextDate);
            array_push($companyDataArr, $compayData[$associateId]);
        }
        $newCalDatas = $this->calculateSalary($new_salary);
        $preCalDatas = $this->calculateSalary($pre_salary);
        $preCalDatasData = array_values($preCalDatas);
        $newCalDatasData = array_values($newCalDatas);
        $compayDataList = array_values($companyDataArr);
        $finalData = array();
        $finalData = $this->calculatetotalAvg($preCalDatasData, $newCalDatasData, $compayDataList);
        return $finalData;
    }

    public static function calculatetotalAvg($preSalary, $newSalary, $companyData)
    {
        $coreArea = Core_areas::all();
        $allDatas = array();
        $i = 0;
        $total = 0;
        $firstIndex = ['Association', 'Company',    'Total Staff',  'Staff Inflation - Total',  'Staff Inflation - Level 1',    'Staff Inflation - Level 2',    'Staff Inflation - Level 3'];
        foreach ($coreArea as $cValue) {
            $secondIndex[] = 'Staff Inflation -' . $cValue->name;
        }
        $allDatas[] = array_merge($firstIndex, $secondIndex);
        foreach ($preSalary as $value) {
            $allData['Association'] = $companyData[$i]->associate;
            $allData['companyName'] = $companyData[$i]->companyName;
            $total += $companyData[$i]->total;
            $allData['total'] = $companyData[$i]->total;
            $allData['montly_gross_salary'] = 0;
            if (!empty($value['montly_gross_salary']) && !empty($newSalary[$i]['montly_gross_salary'])) {
                $allData['montly_gross_salary'] = round((($newSalary[$i]['montly_gross_salary'] / $newSalary[$i]['counts']) - ($value['montly_gross_salary'] / $value['counts'])) / $newSalary[$i]['montly_gross_salary']);
            } else  if (!empty($value['montly_gross_salary']) && empty($newSalary[$i]['montly_gross_salary'])) {
                $allData['montly_gross_salary'] = round((($value['montly_gross_salary'] / $value['counts']) - ($value['montly_gross_salary'] / $value['counts'])) / $value['montly_gross_salary']);
            }
            $allData['level1_salary'] = 0;
            if (!empty($value['level1_salary']) && !empty($newSalary[$i]['level1_salary'])) {
                $allData['level1_salary'] = round((($newSalary[$i]['level1_salary'] / $newSalary[$i]['counts']) - ($value['level1_salary'] / $value['counts'])) / $newSalary[$i]['level1_salary']);
            } else if (!empty($value['level1_salary']) && empty($newSalary[$i]['level1_salary'])) {
                $allData['level1_salary'] = round((($value['level1_salary'] / $value['counts']) - ($value['level1_salary'] / $value['counts'])) / $value['level1_salary']);
            }
            $allData['level2_salary'] = 0;
            if (!empty($value['level2_salary']) && !empty($newSalary[$i]['level2_salary'])) {
                $allData['level2_salary'] = round((($newSalary[$i]['level2_salary'] / $newSalary[$i]['counts']) - ($value['level2_salary'] / $value['counts'])) / $newSalary[$i]['level2_salary']);
            } else if (!empty($value['level2_salary']) && empty($newSalary[$i]['level2_salary'])) {
                $allData['level2_salary'] = round((($value['level2_salary'] / $value['counts']) - ($value['level2_salary'] / $value['counts'])) / $value['level2_salary']);
            }
            $allData['level3_salary'] = 0;
            if (!empty($value['level3_salary']) && !empty($newSalary[$i]['level3_salary'])) {
                $allData['level3_salary'] = round((($newSalary[$i]['level3_salary'] / $newSalary[$i]['counts']) - ($value['level3_salary'] / $value['counts'])) / $newSalary[$i]['level3_salary']);
            } else if (!empty($value['level3_salary']) && empty($newSalary[$i]['level3_salary'])) {
                $allData['level3_salary'] = round((($value['level3_salary'] / $value['counts']) - ($value['level3_salary'] / $value['counts'])) / $value['level3_salary']);
            }

            foreach ($coreArea as $cValue) {
                $string = str_replace(' ', '', $cValue->name); // Replaces all spaces with hyphens.

                $coreAreaName = preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.
                if (array_key_exists($coreAreaName, $value)) {

                    if ((!empty($value[$coreAreaName]) && $value[$coreAreaName] > 0) && (!empty($newSalary[$i][$coreAreaName]) && $newSalary[$i][$coreAreaName] > 0)) {
                        $allData[$coreAreaName] = round((($newSalary[$i][$coreAreaName] / $newSalary[$i]['counts']) - ($value[$coreAreaName] / $value['counts'])) / $newSalary[$i][$coreAreaName]);
                    } else if ((!empty($value[$coreAreaName]) && $value[$coreAreaName] > 0) && empty($newSalary[$i][$coreAreaName])) {
                        $allData[$coreAreaName] = round((($value[$coreAreaName] / $value['counts']) - ($value[$coreAreaName] / $value['counts'])) / $value[$coreAreaName]);
                    } else {
                        $allData[$coreAreaName] = 0;
                    }
                }
            }
            $i++;
            $allDatas[] = $allData;
        }
        $count = count($allDatas);
        if ($count > 0) {
            $allDatas[] = ['', 'TOTAL', $total];
        }
        return $allDatas;
    }

    public static function calculateSalary($data)
    {
        $groups = array();
        $coreArea = Core_areas::all();
        foreach ($data as $item) {
            if (!empty($item)) {
                $key = $item;
                if (!array_key_exists($key, $groups)) {
                    $counts = 1;
                    $groups[$key] = $item;
                    $groups[$key]['counts'] = $counts;
                } else {
                    $groups[$key]['montly_gross_salary'] += $item['montly_gross_salary'];
                    $groups[$key]['level1_salary'] += $item['level1_salary'];
                    $groups[$key]['level2_salary'] += $item['level2_salary'];
                    $groups[$key]['level3_salary'] += $item['level3_salary'];
                    $groups[$key]['counts'] += 1;
                    foreach ($coreArea as $value) {
                        $string = str_replace(' ', '', $value->name); // Replaces all spaces with hyphens.

                        $coreAreaName = preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.
                        if (array_key_exists($coreAreaName, $data)) {
                            $groups[$key][$coreAreaName] += $data[$coreAreaName];
                        }
                    }
                }
            }
        }
        return $groups;
    }


    public static function getCompayData($associateId, $preDate, $nextDate)
    {
        $compayData = 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 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 bankuser.associateId = " . $associateId . " AND (date_format(bankuser.submission_date,'%Y-%m-%d')  BETWEEN " . "'" . $preDate . "'" . " AND " . "'" . $nextDate . "'" . ") GROUP BY associateId");
        return $compayData[0];
    }

    public static function filterData($newSalary, $associateId)
    {
        $coreArea = Core_areas::all();
        $count_coreArea = count($coreArea);
        $i = 1;
        $allData = [];
        $allData['associateId'] = $associateId;
        $allData['montly_gross_salary'] = $newSalary['montly_gross_salary'];
        $allData['level1_salary'] = $newSalary['level1_salary'];
        $allData['level2_salary'] = $newSalary['level2_salary'];
        $allData['level3_salary'] = $newSalary['level3_salary'];
        foreach ($coreArea as $key => $value) {
            $string = str_replace(' ', '', $value->name); // Replaces all spaces with hyphens.

            $coreAreaName = preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.
            if (array_key_exists($coreAreaName, $newSalary)) {
                $allData[$coreAreaName] = $newSalary[$coreAreaName];
            }
        }
        return $allData;
    }



    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 getCoreAreasAccordingMonthDuration($query = "", $where, $select)
    {
        $sql = $query;
        $coreArea = Core_areas::all();
        $count_coreArea = count($coreArea);
        $i = 1;
        foreach ($coreArea as $key => $value) {
            $string = str_replace(' ', '', $value->name); // Replaces all spaces with hyphens.

            $coreAreaName = preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.

            $sql .=  "(" . $select . $where . " AND bu.core_area =" . "'" . $value->name . "'" . ") as " . $coreAreaName;
            if ($i != $count_coreArea) {
                $sql .= ", ";
            }
            $i++;
        }
        return $sql;
    }


    public static function calculatetotalAvgDuration($data, $companyData)
    {
        $coreArea = Core_areas::all();
        $allDatas = array();
        $i = 0;
        $total = 0;
        $firstIndex = ['Association', 'Company',    'Total Staff',  'Duration of Service - Total',  'Duration of Service - Level 1',    'Duration of Service - Level 2',    'Duration of Service - Level 3'];
        foreach ($coreArea as $cValue) {
            $secondIndex[] = 'Duration of Service -' . $cValue->name;
        }
        $allDatas[] = array_merge($firstIndex, $secondIndex);
        foreach ($data as $value) {
            $allData['Association'] = $companyData[$i]->associate;
            $allData['companyName'] = $companyData[$i]->companyName;
            $total += $companyData[$i]->total;
            $allData['total'] = $companyData[$i]->total;
            $allData['monthDuration'] = 0;
            if (!empty($value['monthDuration'])) {
                $allData['montly_gross_salary'] = round($value['monthDuration'] / $value['counts']);
            }
            $allData['monthDurationLevel1'] = 0;
            if (!empty($value['monthDurationLevel1'])) {
                $allData['monthDurationLevel1'] = round($value['monthDurationLevel1'] / $value['counts']);
            }
            $allData['monthDurationLevel2'] = 0;
            if (!empty($value['monthDurationLevel2'])) {
                $allData['monthDurationLevel2'] = round($value['monthDurationLevel2'] / $value['counts']);
            }
            $allData['monthDurationLevel3'] = 0;
            if (!empty($value['monthDurationLevel3'])) {
                $allData['monthDurationLevel3'] = round($value['monthDurationLevel3'] / $value['counts']);
            }


            foreach ($coreArea as $cValue) {
                $string = str_replace(' ', '', $cValue->name); // Replaces all spaces with hyphens.

                $coreAreaName = preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.
                if (array_key_exists($coreAreaName, $value)) {
                    if (!empty($value[$coreAreaName])) {
                        $allData[$coreAreaName] = round($value[$coreAreaName] / $value['counts']);
                    }
                }
            }
            $i++;
            $allDatas[] = $allData;
        }
        $count = count($allDatas);
        if ($count > 0) {
            $allDatas[] = ['', 'TOTAL', $total];
        }
        return $allDatas;
    }



    public static function calculateDuration($data)
    {
        $groups = array();
        $coreArea = Core_areas::all();
        foreach ($data as $item) {
            $item = (array) $item;
            if (!empty($item)) {
                $key = $item['associateId'];
                if (!array_key_exists($key, $groups)) {
                    $counts = 1;
                    $groups[$key] = $item;
                    $groups[$key]['counts'] = $counts;
                } else {
                    $groups[$key]['monthDuration'] += $item['monthDuration'];
                    $groups[$key]['monthDurationLevel1'] += $item['monthDurationLevel1'];
                    $groups[$key]['monthDurationLevel2'] += $item['monthDurationLevel2'];
                    $groups[$key]['monthDurationLevel1'] += $item['monthDurationLevel3'];
                    $groups[$key]['counts'] += 1;
                    foreach ($coreArea as $value) {
                        $string = str_replace(' ', '', $value->name); // Replaces all spaces with hyphens.

                        $coreAreaName = preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.
                        if (array_key_exists($coreAreaName, $data)) {
                            $groups[$key][$coreAreaName] += $data[$coreAreaName];
                        }
                    }
                }
            }
        }
        return $groups;
    }

    public function DurationofService($preDate, $nextDate)
    {
        DB::enableQueryLog();
        $where = " WHERE bankuser.name = bu.name AND bankuser.id_number = bankuser.id_number AND bankuser.date_of_birth = bu.date_of_birth";
        $select = "SELECT  ROUND(sum(12 * (YEAR(bu.effective_resignation) - YEAR(bu.date_of_commencement)) + (MONTH(bu.effective_resignation) - MONTH(bu.date_of_commencement)))/count(*), 0) as monthsDu FROM bankuser as bu ";
        $query = "SELECT bankuser.associateId,(" . $select . $where . ") as monthDuration,(" . $select . $where . " AND bu.level = 'Level 1') as monthDurationLevel1, (" . $select . $where . " AND bu.level = 'Level 2') as monthDurationLevel2,(" . $select . $where . " AND bu.level = 'Level 3') as monthDurationLevel3, ";
        $sql = $this->getCoreAreasAccordingMonthDuration($query, $where, $select);
        $sql .= " FROM bankuser INNER JOIN bankuser as bus ON bus.name= bankuser.name AND bus.id_number = bankuser.id_number AND bus.date_of_birth = bankuser.date_of_birth where bankuser.userType = 2 AND bus.userType = 1";
        $products = DB::select($sql);
        $totalStaff = 0;
        $totalInStaff = 0;
        $newprod = [];
        $newDatas = $this->calculateDuration($products);
        $companyDataArr = [];

        foreach ($newDatas as $prod) {
            if (isset($prod['associateId'])) {
                $associateId = $prod['associateId'];
                unset($prod['associateId']);
                $newprod[] = (array) $prod;
                $companyData[$associateId] = $this->getCompayData($associateId, $preDate, $nextDate);
                Log::info($companyData[$associateId]);
                $companyDataArr = array_push($companyDataArr, $companyData[$associateId]);
            }
        }
        $companyDataList = array_values($companyDataArr);
        $filterData = $this->calculatetotalAvgDuration($newprod, $companyDataList);
        return $filterData;
    }

    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;
    }
}
