<?php

namespace App\Http\Repositories;

use App\Helpers\UserHelper;
use App\Models\Chemical_operation;
use App\Models\Factory_chemical;
use Carbon\Carbon;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Query\Builder as QueryBuilder;
use Illuminate\Database\Eloquent\Builder as EloquentBuilder;
use Illuminate\Support\Collection;

class ChemicalOperationRepository
{

    function __construct(protected Chemical_operation $chem_op, protected AuthRepository $auth, protected Factory_chemical $factory_chem) {}

    function getChemOpQuery($user, $company_id, $type)
    {
        $childcompany = $this->auth->getchildcompany($user);
        $query = $this->chem_op->with(
            'fact_chem:id,chemical_name,chemical_other',
            'area:id,name',
            'build:id,name',
            'factories:id,name',
            'custodian:id,contact_name',
            'updated_user:id,contact_name'
        );
        if ($user->id === $company_id) {
            $query->whereNotIn('custodian', $childcompany)->where('company_id', $company_id);
        } elseif ($user->role === 'company' && $user->belongs_parent !== null) {
            $query->where('custodian', $user->id);
        } else {
            $query->whereNotIn('custodian', $childcompany)->where('custodian', $user->id);
        }
        return $query->whereIn('type', $type)->orderby('created_at', 'desc')->get();
    }
    function getGraphData($user, $company_id)
    {
        $childcompany = $this->auth->getchildcompany($user);
        $query = $this->chem_op
            ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
            ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
            ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
            ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')
            ->where('chem.toxic_moenv', 1)
            ->addSelect('chemical_operations.weight as op_weight', 'chemical_operations.operate_date');
        if ($user->id === $company_id) {
            $query->whereNotIn('chemical_operations.custodian', $childcompany)->where('chemical_operations.company_id', $company_id);
        } elseif ($user->role === 'company' && $user->belongs_parent !== null) {
            $query->where('chemical_operations.custodian', $user->id);
        } else {
            $query->whereNotIn('chemical_operations.custodian', $childcompany)->where('chemical_operations.custodian', $user->id);
        }
        return $query->where('chemical_operations.type', 'B');
    }
    function getGraphYear($user, $company_id)
    {
        $childcompany = $this->auth->getchildcompany($user);
        $query = $this->chem_op
            ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
            ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
            ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
            ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')
            ->where('chem.toxic_moenv', 1);
        if ($user->id === $company_id) {
            $query->whereNotIn('chemical_operations.custodian', $childcompany)->where('chemical_operations.company_id', $company_id);
        } elseif ($user->role === 'company' && $user->belongs_parent !== null) {
            $query->where('chemical_operations.custodian', $user->id);
        } else {
            $query->whereNotIn('chemical_operations.custodian', $childcompany)->where('chemical_operations.custodian', $user->id);
        }
        return $query->where('chemical_operations.type', 'B');
    }
    function getOperateData($user, $company_id, $request)
    {
        $childcompany = $this->auth->getchildcompany($user);
        $query = $this->chem_op
            ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
            ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
            ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
            ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')
            ->select(
                'mix.cas_no',
                'chem.en_name',
                'chem.ch_name',
                'mix.conc',
                'chemical_operations.state',
                DB::raw('SUM(chemical_operations.weight) as total_weight'),
                DB::raw('ROUND(SUM(chemical_operations.weight)* 100.0 / SUM(SUM(chemical_operations.weight)) OVER (),2) AS percent')
            )
            ->groupBy('mix.cas_no', 'chem.en_name', 'chem.ch_name', 'mix.conc', 'chemical_operations.state')
            ->where('chem.toxic_moenv', 1);
        if (!empty($request->date)) {
            $query->where('operate_date', $request->date);
        }
        if (!empty($request->week)) {
            [$year, $week] = explode('-W', trim($request->week));

            $start = Carbon::now()->setISODate($year, $week)->startOfWeek(); // 該週週一
            $end = Carbon::now()->setISODate($year, $week)->endOfWeek();     // 該週週日

            $query->whereBetween('chemical_operations.operate_date', [$start, $end]);
        }
        //按季度查詢
        if (!empty($request->quarter)) {
            $startDate = $request->input('quarter');
            $year = $request->year ?? date('Y');
            $startDate = $year . '-' . $request->quarter;
            $endDate = date('Y-m-t', strtotime($startDate . ' +2 months'));

            $query->whereBetween('chemical_operations.operate_date', [$startDate, $endDate]);
        }
        //按月查詢
        if (!empty($request->month)) {
            $startDate = $request->month . '-01';
            $endDate = date('Y-m-t', strtotime($startDate));

            $query->whereBetween('chemical_operations.operate_date', [$startDate, $endDate]);
        }
        if ($user->id === $company_id) {
            $query->whereNotIn('chemical_operations.custodian', $childcompany)->where('chemical_operations.company_id', $company_id);
        } elseif ($user->role === 'company' && $user->belongs_parent !== null) {
            $query->where('chemical_operations.custodian', $user->id);
        } else {
            $query->whereNotIn('chemical_operations.custodian', $childcompany)->where('chemical_operations.custodian', $user->id);
        }
        return $query->where('type', 'B');
    }
    //取得新增製造資料
    function getChemAdd($company_id, $user)
    {
        return $this->getChemOpQuery($user, $company_id, ['A', 'C', 'J', 'K', 'O']);
    }
    //取得使用資料
    function getSub($user, $company_id)
    {
        return $this->getChemOpQuery($user, $company_id, ['B']);
    }
    //取得分裝資料
    function getDecant($user, $company_id)
    {
        return $this->getChemOpQuery($user, $company_id, ['D', 'E']);
    }
    //取得廢棄資料
    function getDisposal($user, $company_id)
    {
        return $this->getChemOpQuery($user, $company_id, ['H']);
    }
    //取得調撥資料
    function getTransfer($user, $company_id)
    {
        return $this->getChemOpQuery($user, $company_id, ['F', 'G']);
    }
    //取得轉出資料
    function getShiftOut($user, $company_id)
    {
        return $this->getChemOpQuery($user, $company_id, ['L']);
    }
    //取得輸出資料
    function getExport($user, $company_id)
    {
        return $this->getChemOpQuery($user, $company_id, ['P']);
    }
    //取得賣出資料
    function getSell($user, $company_id)
    {
        return $this->getChemOpQuery($user, $company_id, ['N']);
    }
    //取得殘氣退回資料
    function getResidual($user, $company_id)
    {
        return $this->getChemOpQuery($user, $company_id, ['Q']);
    }
    /**
     * 取得危險化學品申報的詳細列表
     */
    const TYPE_INITIAL = 'f';   // 首次申報 (First)
    const TYPE_PERIODIC = 'r';  // 定期申報 (Regular)
    const TYPE_DYNAMIC = 'd';   // 動態變更 (Dynamic)
    public function getDeclarableOperationsList($user, $request): Collection
    {
        $query = $this->buildDeclarableOperationsQuery($user, $request);
        if (!$query) {
            return collect();
        }

        $selectColumns = [];
        if ($request->type === self::TYPE_PERIODIC) {
            // 定期申報：顯示達到歷史最大值的操作紀錄詳情
            $selectColumns = [
                'co.operate_date', 'co.weight', 'co.state', 'co.factory_id', 'co.company_id',
                'mix.cas_no', 'chem.id as chemical_id', 'chem.en_name', 'chem.ch_name',
                'chem.danger_name', 'chem.formula'
            ];
        } else {
            // 首次申報(f)與動態申報(d)：顯示觸發當日，所有相關化學品的日結算詳情
            $selectColumns = [
                'details.operate_date', 'details.factory_id', 'details.company_id','details.formula',
                'details.cas_no', 'details.danger_name','details.en_name', 'details.ch_name',
                'details.daily_total_weight as weight',
                'details.control_index_part' // 該品項當天貢獻的指數分數
            ];
        }

        return $query->select($selectColumns)->get();
    }

    /**
     * 取得各工廠須申報的危險化學品品項數量
     */
    public function getDeclarableFactoryCounts($user, $request): Collection
    {
        $baseQuery = $this->buildDeclarableOperationsQuery($user, $request);
        if (!$baseQuery) {
            return collect();
        }

        return DB::query()
            ->fromSub($baseQuery, 'declarable_items')
            ->join('factories', 'factories.id', '=', 'declarable_items.factory_id')
            ->select(
                'declarable_items.factory_id',
                'factories.name',
                DB::raw('COUNT(*) as declaration_count')
            )
            ->groupBy('declarable_items.factory_id', 'factories.name')
            ->orderBy('declaration_count', 'desc')
            ->get();
    }

    /**
     *  核心邏輯 
     * 根據類型，建立一個包含所有符合申報條件的紀錄的基礎查詢
     */
    private function buildDeclarableOperationsQuery($user, $request)
    {
        $company_id = UserHelper::getCompanyId();

        switch ($request->type) {
            case self::TYPE_PERIODIC:
                return $this->buildPeriodicQuery($user, $company_id, $request);

            case self::TYPE_INITIAL:
            case self::TYPE_DYNAMIC:
                return $this->buildIndexBasedQuery($user, $company_id, $request);
        }
        return null;
    }

    /**
     * 建立「定期申報」的查詢 (type 'r')
     */
    private function buildPeriodicQuery($user, $company_id, $request)
    {
        $baseQuery = DB::table('chemical_operations as co')
            ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'co.factory_chemical_id')
            ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
            ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
            ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')
            ->where('chem.danger', 1)->whereIn('co.type', ['B', 'C'])
            ->whereRaw('co.weight > chem.danger_volume::numeric');

        $this->applySharedFilters($baseQuery, $user, $company_id, $request);

        $maxWeights = (clone $baseQuery)->select('mix.cas_no', 'co.state', DB::raw('MAX(co.weight) as max_weight'))->groupBy('mix.cas_no', 'co.state');

        $query = $baseQuery->joinSub($maxWeights, 'max_w', function ($join) {
            $join->on('mix.cas_no', '=', 'max_w.cas_no')->on('co.state', '=', 'max_w.state')->on('co.weight', '=', 'max_w.max_weight');
        });

        return $query->select(
            'co.id', 'co.operate_date', 'co.weight', 'co.state', 'co.factory_id', 'co.company_id',
            'mix.cas_no', 'chem.id as chemical_id', 'chem.en_name', 'chem.ch_name', 'chem.danger_name', 'chem.formula'
        );
    }

    /**
     * 建立基於「綜合管制指數」的查詢 (type 'f' and 'd')
     */
    private function buildIndexBasedQuery($user, $company_id, $request)
    {
        $dateStart = Carbon::now()->subDays(10)->startOfDay();
        $dateEnd = Carbon::now()->endOfDay();

        // 步驟 1: 計算每日、每個化學品的日總量和指數分數
        $chemicalDailySummary = DB::table('chemical_operations as co')
            ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'co.factory_chemical_id')
            ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
            ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
            ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')
            ->select(
                'co.operate_date', 'co.factory_id', 'co.company_id', 'co.custodian', 'mix.cas_no',
                DB::raw('MIN(chem.en_name) as en_name'),
                DB::raw('MIN(chem.ch_name) as ch_name'),
                DB::raw('MIN(chem.formula) as formula'),
                DB::raw('MIN(chem.danger_name) as danger_name'),
                DB::raw('SUM(co.weight) as daily_total_weight'),
                DB::raw('SUM(co.weight) / NULLIF(MIN(chem.danger_volume::numeric), 0) as control_index_part')
            )
            ->where('chem.danger', 1)->whereIn('co.type', ['B', 'C'])
            ->whereBetween('co.operate_date', [$dateStart, $dateEnd]);

        $this->applySharedFilters($chemicalDailySummary, $user, $company_id, $request, 'co');
        $chemicalDailySummary->groupBy('co.operate_date', 'co.factory_id', 'co.company_id', 'co.custodian', 'mix.cas_no');

        // 步驟 2: 計算每個工廠每日的「綜合管制指數」
        $factoryDailyIndex = DB::query()->fromSub($chemicalDailySummary, 'summary')
            ->select('summary.factory_id', 'summary.operate_date', DB::raw('SUM(summary.control_index_part) as total_control_index'))
            ->groupBy('summary.factory_id', 'summary.operate_date');

        // 步驟 3: 找出觸發申報的「事件」(factory_id, operate_date)
        $triggerEventQuery = null;
        if ($request->type === self::TYPE_INITIAL) {
            $rankedDays = DB::query()->fromSub($factoryDailyIndex, 'daily_index')
                ->select('daily_index.factory_id', 'daily_index.operate_date', DB::raw('ROW_NUMBER() OVER (PARTITION BY daily_index.factory_id ORDER BY daily_index.operate_date ASC) as rn'))
                ->where('daily_index.total_control_index', '>=', 1);
            $triggerEventQuery = DB::query()->fromSub($rankedDays, 'triggers')->where('rn', 1)->select('triggers.factory_id', 'triggers.operate_date');
        } else { // TYPE_DYNAMIC
            $laggedIndex = DB::query()->fromSub($factoryDailyIndex, 'daily_index')
                ->select('daily_index.factory_id', 'daily_index.operate_date', 'daily_index.total_control_index', DB::raw('LAG(daily_index.total_control_index, 1, 0) OVER (PARTITION BY daily_index.factory_id ORDER BY daily_index.operate_date) as previous_index'));
            $triggerEventQuery = DB::query()->fromSub($laggedIndex, 'triggers')->whereRaw('ABS(triggers.total_control_index - triggers.previous_index) > 1')->select('triggers.factory_id', 'triggers.operate_date');
        }

        // 最終步驟: 用觸發事件去 JOIN 每日化學品詳情，並用 select('details.*') 解決歧義
        return DB::query()
            ->fromSub($chemicalDailySummary, 'details')
            ->joinSub($triggerEventQuery, 'events', function ($join) {
                $join->on('details.factory_id', '=', 'events.factory_id')->on('details.operate_date', '=', 'events.operate_date');
            })
            ->select('details.*');
    }

    private function applySharedFilters(QueryBuilder $query, $user, $company_id, $request, string $prefix = 'co'): void
    {
        if ($company_id === $user->id) {
            $query->where("{$prefix}.company_id", $user->id);
        } else {
            $query->where("{$prefix}.custodian", $user->id);
        }
        if ($request->type === self::TYPE_PERIODIC && !empty($request->helfYear)) {
            $year = $request->year ?? date('Y');
            $startDate = "{$year}-{$request->helfYear}";
            $endDate = date('Y-m-t', strtotime($startDate . ' +5 months'));
            $query->whereBetween("{$prefix}.operate_date", [$startDate, $endDate]);
        }
    }
    //取得報表資料
    function getReportlist($user, $request)
    {
        $childcompany = $this->auth->getchildcompany($user);
        $comapny_id = UserHelper::getCompanyId();
        $query = $this->chem_op
            ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
            ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
            ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
            ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')
            ->join('users as user', 'user.id', '=', 'chemical_operations.company_id')
            ->join('factories as factory', 'factory.id', '=', 'chemical_operations.factory_id')
            ->join('factory_bdings as build', 'build.id', '=', 'chemical_operations.build_id')
            ->join('factory_areas as area', 'area.id', '=', 'chemical_operations.area_id')
            ->select(
                'mix.cas_no',
                'mix.conc',
                'chemical_operations.company_id',
                'user.name as company',
                'chemical_operations.custodian',
                'fact_chem.state',
                'chem.id',
                'en_name',
                'ch_name',
                'factory.id as factory_id',
                'build.id as build_id',
                'area.id as area_id',
                'factory.name as factory_name',
                'build.name as build_name',
                'area.name as area_name',
                'hazards',
                'priority',
                'control',
                'pioneer',
                'water_poll',
                'concerned',
                'toxic_moenv',
                'pub_hazard',
                'solv',
                'monitor',
                'chem_spec',
                'lead',
                'four_lead',
                'ccd_health',
                'expose_conc'
            );
        //cas_no查詢
        if (!empty($request->cas_no)) {
            $query->where('mix.cas_no', 'like', '%' . $request->cas_no . '%');
        }
        //廠區查詢
        if (!empty($request->factory_id)) {
            $query->where('factory_id', $request->factory_id);
        }
        //棟別查詢
        if (!empty($request->build_id)) {
            $query->where('build_id', $request->build_id);
        }
        //儲存區查詢
        if (!empty($request->area_id)) {
            $query->where('area_id', $request->area_id);
        }
        //日期區間查詢
        if (!empty($request->startDate) && !empty($request->endDate)) {
            $query->whereBetween('operate_date', [$request->startDate, $request->endDate]);
        }
        if (isset($request->year) && !isset($request->helfYear)) {
            $year = $request->year;
            $startDate = "{$year}-01-01";
            $endDate = "{$year}-12-31";
            $query->whereBetween('operate_date', [$startDate, $endDate]);
        }
        //按年度查詢
        if (!empty($request->helfYear)) {
            $year = $request->year ?? date('Y');
            $startDate = $year . '-' . $request->helfYear;
            // $startDate = Carbon::createFromFormat('Y-m-d', $year . '-' . $request->helfYear)->subDay();
            // $endDate = $startDate->copy()->subMonths(6); // 回推 6 個月
            $endDate = date('Y-m-t', strtotime($startDate . ' +5 months'));
            $query->whereBetween('operate_date', [$startDate, $endDate]);
        }
        //按季度查詢
        if (!empty($request->season)) {
            $startDate = $request->input('season');
            $year = $request->year ?? date('Y');
            $startDate = $year . '-' . $request->season;
            $endDate = date('Y-m-t', strtotime($startDate . ' +2 months'));

            $query->whereBetween('operate_date', [$startDate, $endDate]);
        }
        //按月查詢
        if (isset($request->year) && isset($request->month)) {
            $year = $request->year;
            $startDate = $year . '-' . $request->month . '-' . '01';
            $endDate = date('Y-m-t', strtotime($startDate));
            $query->whereBetween('operate_date', [$startDate, $endDate]);
        }
        //按動態、固態查詢
        if (isset($request->dynamic)) {
            if ($request->dynamic === 'day') {
                $endDate = date('Y-m-d');
                $startDate = date('Y-m-d', strtotime('-30 days'));
                $query->whereBetween('operate_date', [$startDate, $endDate]);
            } else if ($request->dynamic === 'person') {
                $lastRecords = $query
                    ->addSelect('chemical_operations.weight', 'chemical_operations.operate_date')
                    ->whereExists(function ($subQuery) {
                        $subQuery->from('chemical_operations as prev')
                            ->whereColumn('mix.cas_no', 'prev.cas_no')
                            ->whereColumn('fact_chem.state', 'prev.state')
                            ->whereColumn('mix.conc', 'prev.conc')
                            ->whereRaw('chemical_operations.operate_date > prev.operate_date')
                            ->orderByDesc('prev.operate_date')
                            ->limit(1)
                            ->whereRaw('chemical_operations.weight > prev.weight * 1.3');
                    })
                    ->orderByDesc('chemical_operations.operate_date');
            }
        }
        //勞動部公告之危害性化學品
        if (!empty($request->hazards)) {
            $query->where('hazards', 1);
        }
        //勞動部公告之優先管理化學品
        if (!empty($request->priority)) {
            $query->where('priority', 1);
        }
        //環境部之毒化物查詢
        if (!empty($request->toxic)) {
            $query->where('toxic_moenv', 1);
        }
        //環境部之關注物查詢
        if (!empty($request->concerned)) {
            $query->where('concerned', 1);
        }
        //經濟部之先驅物查詢
        if (!empty($request->pioneer)) {
            $query->where('pioneer', 1);
        }
        if ($user->role === 'company' && $comapny_id === $user->id && !$request->has('child')) {
            $query->where('chemical_operations.company_id', $user->id);
        } else if ($user->role !== 'company') {
            $query->where('chemical_operations.custodian', $user->id);
        }
        $querygroup = $query->groupBy(
            'mix.cas_no',
            'mix.conc',
            'chemical_operations.company_id',
            'chemical_operations.custodian',
            'user.name',
            'fact_chem.state',
            'chem.id',
            'en_name',
            'ch_name',
            'factory.id',
            'build.id',
            'area.id',
            'factory.name',
            'build.name',
            'area.name',
            'hazards',
            'priority',
            'control',
            'pioneer',
            'water_poll',
            'concerned',
            'toxic_moenv',
            'pub_hazard',
            'solv',
            'monitor',
            'chem_spec',
            'lead',
            'four_lead',
            'ccd_health',
            'expose_conc',
        );
        $distinctCols = $request->has('factory_id')
            ? ['cas_no', 'state', 'conc', 'factory.id', 'build.id', 'area.id']
            : ['cas_no', 'state', 'conc'];
        if ($user->role === 'company') {
            if ($user->belongs_parent !== null || !$request->has('child')) {
                return $querygroup->where('chemical_operations.company_id', $user->id)->distinct(...$distinctCols)->get();
            }
            if ($request->filled('child')) {
                return $querygroup->where('chemical_operations.company_id', $request->child)->distinct(...$distinctCols)->get();
            }
            return $querygroup->whereIn('chemical_operations.company_id', $childcompany)->distinct(...$distinctCols)->get();
        }
        return $query->distinct(...$distinctCols)->get();
    }

    //取得環境部報表
    function getMoenvlist($user, $request)
    {
        $childcompany = $this->auth->getchildcompany($user);
        $company_id = UserHelper::getCompanyId();
        $query = $this->chem_op
            ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
            ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
            ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
            ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')
            // ->join('chemicals as chem', 'chem.id', '=', 'chemical_operations.chemical_id')
            ->join('users as user', 'user.id', '=', 'chemical_operations.company_id')
            // ->join('approval_doc_lists as list', function ($join) use ($company_id) {
            //     $join->on('list.chemical_id', '=', 'chemical_operations.chemical_id')
            //         ->join('approval_docs as doc', 'doc.id', '=', 'list.doc_id')
            //         ->whereRaw('CAST(list.conc_low AS NUMERIC) <= CAST(chemical_operations.conc AS NUMERIC)')
            //         ->whereRaw('CAST(list.conc_up AS NUMERIC) >= CAST(chemical_operations.conc AS NUMERIC)')
            //         ->whereRaw('chemical_operations.operate_date BETWEEN doc.start_date AND doc.end_date')
            //         ->where('chemical_operations.company_id', $company_id);
            // })
            ->select(
                'mix.cas_no',
                'mix.conc',
                'chemical_operations.company_id',
                'user.name as company',
                'fact_chem.state',
                'chem.id',
                'en_name',
                'ch_name',
                'hazards',
                'priority',
                'control',
                'pioneer',
                'water_poll',
                'concerned',
                'toxic_moenv',
                'pub_hazard',
                'solv',
                'monitor',
                'chem_spec',
                'lead',
                'four_lead',
                'ccd_health',
                'expose_conc'
            )->groupBy(
                'mix.cas_no',
                'mix.conc',
                'chemical_operations.company_id',
                'user.name',
                'fact_chem.state',
                'chem.id',
                'en_name',
                'ch_name',
                'hazards',
                'priority',
                'control',
                'pioneer',
                'water_poll',
                'concerned',
                'toxic_moenv',
                'pub_hazard',
                'solv',
                'monitor',
                'chem_spec',
                'lead',
                'four_lead',
                'ccd_health',
                'expose_conc',
            );
        if (isset($request->year) && !isset($request->helfYear)) {
            $year = $request->year;
            $startDate = "{$year}-01-01";
            $endDate = "{$year}-12-31";
            $query->whereBetween('operate_date', [$startDate, $endDate]);
        }
        //按年度查詢
        if (!empty($request->helfYear)) {
            $year = $request->year ?? date('Y');
            $startDate = $year . '-' . $request->helfYear;
            // $startDate = Carbon::createFromFormat('Y-m-d', $year . '-' . $request->helfYear)->subDay();
            // $endDate = $startDate->copy()->subMonths(6); // 回推 6 個月
            $endDate = date('Y-m-t', strtotime($startDate . ' +5 months'));
            $query->whereBetween('operate_date', [$startDate, $endDate]);
        }
        //按季度查詢
        if (!empty($request->season)) {
            $startDate = $request->input('season');
            $year = $request->year ?? date('Y');
            $startDate = $year . '-' . $request->season;
            $endDate = date('Y-m-t', strtotime($startDate . ' +2 months'));

            $query->whereBetween('operate_date', [$startDate, $endDate]);
        }
        //按月查詢
        if (isset($request->year) && isset($request->month)) {
            $year = $request->year;
            $startDate = $year . '-' . $request->month . '-' . '01';
            $endDate = date('Y-m-t', strtotime($startDate));
            $query->whereBetween('operate_date', [$startDate, $endDate]);
        }
        //勞動部公告之危害性化學品
        if (!empty($request->hazards)) {
            $query->where('hazards', 1);
        }
        //勞動部公告之優先管理化學品
        if (!empty($request->priority)) {
            $query->where('priority', 1);
        }
        //環境部之毒化物查詢
        if (!empty($request->toxic)) {
            $query->where('toxic_moenv', 1);
        }
        //環境部之關注物查詢
        if (!empty($request->concerned)) {
            $query->where('concerned', 1);
        }
        //經濟部之先驅物查詢
        if (!empty($request->pioneer)) {
            $query->where('pioneer', 1);
        }
        if ($user->role === 'company' && $company_id === $user->id && !$request->has('child')) {
            $query->where('chemical_operations.company_id', $user->id);
        } else if ($user->role !== 'company') {
            $query->where('chemical_operations.custodian', $user->id);
        }

        $query2 = DB::table('approval_doc_lists as list')
            ->join('approval_docs as doc', 'doc.id', 'list.doc_id')
            ->rightJoin('chemical_operations as co', function ($join) use ($company_id) {
                $join->on('co.chemical_id', '=', 'list.chemical_id')
                    ->whereRaw('CAST(list.conc_low AS NUMERIC) <= CAST(co.conc AS NUMERIC)')
                    ->whereRaw('CAST(list.conc_up AS NUMERIC) >= CAST(co.conc AS NUMERIC)')
                    // ->whereRaw('co.operate_date BETWEEN doc.start_date AND doc.end_date')
                    ->where('co.company_id', $company_id);
            })
            ->join('users as user', 'user.id', '=', 'co.company_id')
            ->join('chemicals as chem', 'chem.id', '=', 'co.chemical_id')
            ->where('doc.start_date', '<=', $endDate)
            ->where('doc.end_date', '>=', $startDate)
            ->select(
                'co.cas_no',
                'co.conc',
                'co.company_id',
                'user.name as company',
                'co.state',
                'chem.id as chem_id',
                'chem.en_name',
                'chem.ch_name',
                'hazards',
                'priority',
                'control',
                'pioneer',
                'water_poll',
                'concerned',
                'toxic_moenv',
                'pub_hazard',
                'solv',
                'monitor',
                'chem_spec',
                'lead',
                'four_lead',
                'ccd_health',
                'expose_conc',
            )
            ->groupBy(
                'co.cas_no',
                'co.conc',
                'co.company_id',
                'user.name',
                'co.state',
                'chem.id',
                'chem.en_name',
                'chem.ch_name',
                'hazards',
                'priority',
                'control',
                'pioneer',
                'water_poll',
                'concerned',
                'toxic_moenv',
                'pub_hazard',
                'solv',
                'monitor',
                'chem_spec',
                'lead',
            );
        //勞動部公告之危害性化學品
        if (!empty($request->hazards)) {
            $query2->where('hazards', 1);
        }
        //勞動部公告之優先管理化學品
        if (!empty($request->priority)) {
            $query2->where('priority', 1);
        }
        //環境部之毒化物查詢
        if (!empty($request->toxic)) {
            $query2->where('toxic_moenv', 1);
        }
        //環境部之關注物查詢
        if (!empty($request->concerned)) {
            $query2->where('concerned', 1);
        }
        //經濟部之先驅物查詢
        if (!empty($request->pioneer)) {
            $query2->where('pioneer', 1);
        }
        if ($user->role === 'company' && $company_id === $user->id && !$request->has('child')) {
            $query2->where('co.company_id', $user->id);
        } else if ($user->role !== 'company') {
            $query2->where('co.custodian', $user->id);
        }
        $distinctCols = ['cas_no', 'state', 'conc'];
        // dd($query2->distinct('cas_no', 'state', 'conc')->get());
        // dd($query->distinct('cas_no', 'state', 'conc')->get());
        // $query = $query->union($query2)->distinct('cas_no', 'state', 'conc');
        if ($user->role === 'company') {
            if ($user->belongs_parent !== null || !$request->has('child')) {
                $query->where('chemical_operations.company_id', $user->id)->distinct(...$distinctCols)->get();
            }
            $companyIds = array_merge($childcompany, [$user->id]);
            return $query->whereIn('chemical_operations.company_id', $companyIds)->distinct(...$distinctCols)->get();
        }
        return $query->distinct(...$distinctCols)->get();
    }

    //環境部未申報統計
    function moenvCount($toxic, $concerned)
    {
        $user = Auth::user();
        $childcompany = $this->auth->getchildcompany($user);
        $company_id = UserHelper::getCompanyId();

        $now = now();
        $year = $now->year;
        $month = $now->month - 1;

        if ($month === 0) {
            $month = 12;
            $year -= 1;
        }

        $monthPadded = str_pad($month, 2, '0', STR_PAD_LEFT);
        $startDate = "$year-$monthPadded-01";
        $endDate = date('Y-m-t', strtotime($startDate));

        // 主查詢
        $query = DB::table('chemical_operations')
            ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
            ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
            ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
            ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')
            // ->join('chemicals as chem', 'chem.id', '=', 'chemical_operations.chemical_id')
            ->join('users as user', 'user.id', '=', 'chemical_operations.company_id')
            // ->join('approval_doc_lists as list', function ($join) use ($company_id) {
            //     $join->on('list.chemical_id', '=', 'chemical_operations.chemical_id')
            //         ->join('approval_docs as doc', 'doc.id', '=', 'list.doc_id')
            //         ->whereRaw('CAST(list.conc_low AS NUMERIC) <= CAST(chemical_operations.conc AS NUMERIC)')
            //         ->whereRaw('CAST(list.conc_up AS NUMERIC) >= CAST(chemical_operations.conc AS NUMERIC)')
            //         ->whereRaw('chemical_operations.operate_date BETWEEN doc.start_date AND doc.end_date')
            //         ->where('chemical_operations.company_id', $company_id);
            // })
            ->select('mix.cas_no', 'chemical_operations.state', 'mix.conc','chem.toxic_moenv')
            ->whereBetween('chemical_operations.operate_date', [$startDate, $endDate]);
        if ($toxic) {
            $query->where('chem.toxic_moenv', 1);
        }
        if ($concerned) {
            $query->where('chem.concerned', 1);
        }
        if ($user->role === 'company' && $company_id === $user->id) {
            $query->where('chemical_operations.company_id', $user->id);
        } else if ($user->role !== 'company') {
            $query->where('chemical_operations.custodian', $user->id);
        }
        // 次查詢 (right join 版本)
        $query2 = DB::table('approval_doc_lists as list')
            ->join('approval_docs as doc', 'doc.id', '=', 'list.doc_id')
            ->rightJoin('chemical_operations as co', function ($join) use ($company_id) {
                $join->on('co.chemical_id', '=', 'list.chemical_id')
                    ->whereRaw('CAST(list.conc_low AS NUMERIC) <= CAST(co.conc AS NUMERIC)')
                    ->whereRaw('CAST(list.conc_up AS NUMERIC) >= CAST(co.conc AS NUMERIC)')
                    ->where('co.company_id', $company_id);
            })
            ->join('users as user', 'user.id', '=', 'co.company_id')
            ->join('chemicals as chem', 'chem.id', '=', 'co.chemical_id')
            ->where('doc.start_date', '<=', $endDate)
            ->where('doc.end_date', '>=', $startDate)
            ->select('co.cas_no', 'co.state', 'co.conc');

        if ($toxic) {
            $query2->where('chem.toxic_moenv', 1);
        }
        if ($concerned) {
            $query2->where('chem.concerned', 1);
        }
        if ($user->role === 'company' && $company_id === $user->id) {
            $query2->where('co.company_id', $user->id);
        } else if ($user->role !== 'company') {
            $query2->where('co.custodian', $user->id);
        }

        // union 後包成子查詢做 count
        // $unionQuery = $query->union($query2);

        // return DB::table(DB::raw("({$unionQuery->toSql()}) as sub"))
        //     ->mergeBindings($unionQuery)
        //     ->count();
        return $query->count();
    }

    function getDangerCount($user, $request)
    {
        $childcompany = $this->auth->getchildcompany($user);
        $comapny_id = UserHelper::getCompanyId();

        $query = null; // 初始化 $query

        if ($request->type == 'r') {
            $query = $this->chem_op
                ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
                ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
                ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
                ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id');

            // 半年
            if (!empty($request->helfYear)) {
                $year = $request->year ?? date('Y');
                $startDate = $year . '-' . $request->helfYear;
                $endDate = date('Y-m-t', strtotime($startDate . ' +5 months'));
                $query->whereBetween('operate_date', [$startDate, $endDate]);
            }

            // 通用權限篩選應用於 $query
            if ($comapny_id === $user->id) {
                $query->where('chemical_operations.company_id', $user->id);
            } else {
                $query->where('chemical_operations.custodian', $user->id);
            }
            $query->whereRaw('chemical_operations.weight > chem.danger_volume::numeric');
            $subQuery = $this->chem_op
                ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
                ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
                ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
                ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')
                ->select(DB::raw('MAX(chemical_operations.weight) as max_weight'), 'mix.cas_no', 'chemical_operations.state')
                ->whereIn('type', ['B', 'C'])
                ->groupBy('mix.cas_no', 'chemical_operations.state');

            $query->joinSub($subQuery, 'max_weights', function ($join) {
                $join->on('mix.cas_no', '=', 'max_weights.cas_no')
                    ->on('chemical_operations.state', '=', 'max_weights.state')
                    ->on('chemical_operations.weight', '=', 'max_weights.max_weight');
            })
                ->select(
                    'chemical_operations.factory_id',
                    DB::raw('COUNT(DISTINCT mix.cas_no) as cas_no_count')
                )
                ->where('danger', 1);
        } else if ($request->type == 'f') {
            $dateStart = Carbon::now()->subDays(10)->startOfDay();
            $dateEnd = Carbon::now()->endOfDay();
            // Step 1: 找出每個 factory_id 和 cas_no 組合的首次達到 danger_volume 的記錄
            $firstDeclarationSubquery = DB::table('chemical_operations')
                ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
                ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
                ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
                ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')
                ->select(
                    'chemical_operations.factory_id',
                    'mix.cas_no',
                    'chemical_operations.operate_date',
                    DB::raw('ROW_NUMBER() OVER (PARTITION BY mix.cas_no ORDER BY chemical_operations.operate_date ASC) as rn')
                )
                ->where('chem.danger', 1)
                ->whereIn('chemical_operations.type', ['B', 'C'])
                ->whereRaw('chemical_operations.weight >= chem.danger_volume::numeric')
                ->whereBetween('chemical_operations.operate_date', [$dateStart, $dateEnd]);

            // --- 將公司/保管人篩選邏輯應用到這個內部子查詢中 ---
            if ($comapny_id === $user->id) {
                $firstDeclarationSubquery->where('chemical_operations.company_id', $user->id);
            } else {
                $firstDeclarationSubquery->where('chemical_operations.custodian', $user->id);
            }
            // --- 結束權限篩選 ---

            // 主查詢現在從這個子查詢的結果開始
            $query = DB::table(DB::raw("({$firstDeclarationSubquery->toSql()}) as first_declarations"))
                ->mergeBindings($firstDeclarationSubquery) // <-- 修正點：直接傳遞 Query Builder 實例
                ->select(
                    DB::raw('COUNT(DISTINCT first_declarations.cas_no) as cas_no_count')
                )
                ->where('first_declarations.rn', 1) // 只取每個 cas_no 的首次申報
                ->orderBy('cas_no_count', 'desc');
        } else if ($request->type == 'd') {
            $dateStart = Carbon::now()->subDays(10)->startOfDay();
            $dateEnd = Carbon::now()->endOfDay();

            $initialSubQuery = DB::table('chemical_operations') // 使用 DB::table
                ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
                ->join('chemical_ords', 'chemical_ords.id', '=', 'fact_chem.ord_id')
                ->join('chemical_mixes', 'chemical_mixes.ord_id', '=', 'chemical_ords.id')
                ->join('chemicals', 'chemicals.id', '=', 'chemical_mixes.chemical_id')
                ->select(
                    'chemical_operations.operate_date',
                    'chemical_mixes.cas_no',
                    'chemical_operations.weight',
                    'chemical_operations.company_id', // 確保選取 company_id
                    'chemical_operations.custodian', // 確保選取 custodian
                    'chemical_operations.state',
                    'chemicals.id',
                    'chemicals.en_name',
                    'chemicals.ch_name',
                    'chemicals.danger_name',
                    'chemicals.formula',
                    'chemicals.danger_volume',
                    DB::raw('LAG(chemical_operations.weight, 1) OVER (PARTITION BY chemical_mixes.cas_no, chemical_operations.state ORDER BY chemical_operations.operate_date) as previous_weight')
                )
                ->where('chemicals.danger', 1)
                ->whereIn('chemical_operations.type', ['B', 'C'])
                ->whereBetween('chemical_operations.operate_date', [$dateStart, $dateEnd]);

            // --- 將公司/保管人篩選邏輯應用到這個內部子查詢中 ---
            if ($comapny_id === $user->id) {
                $initialSubQuery->where('chemical_operations.company_id', $user->id);
            } else {
                $initialSubQuery->where('chemical_operations.custodian', $user->id);
            }
            // --- 結束權限篩選 ---

            // 主查詢現在從這個子查詢的結果開始
            $subQueryResult = DB::table(DB::raw("({$initialSubQuery->toSql()}) as sub"))
                ->mergeBindings($initialSubQuery) // <-- 修正點：直接傳遞 Query Builder 實例
                ->whereRaw('ABS(sub.weight - sub.previous_weight) > sub.danger_volume::numeric')
                ->distinct('sub.cas_no')
                ->select(
                    'sub.cas_no'
                );

            // 將此查詢作為子查詢，來獲取工廠名稱和計數
            $query = DB::table(DB::raw("({$subQueryResult->toSql()}) as danger_change_ops"))
                ->mergeBindings($subQueryResult) // <-- 修正點：直接傳遞 Query Builder 實例
                ->select(
                    DB::raw('COUNT(DISTINCT danger_change_ops.cas_no) as cas_no_count')
                );
        }

        // 最終返回查詢結果
        // 由於 'r', 'f', 'd' 類型現在都直接將結果賦值給 $query，
        // 所以這裡直接 $query->get() 即可
        return $query->get();
    }
    //取得其他未申報數量
    function otherCount($priority, $danger, $pioneer)
    {
        $user = Auth::user();
        $childcompany = $this->auth->getchildcompany($user);
        $company_id = UserHelper::getCompanyId();

        // $now = now();
        // $year = $now->year;
        // $month = $now->month - 1;

        // if ($month === 0) {
        //     $month = 12;
        //     $year -= 1;
        // }

        // $monthPadded = str_pad($month, 2, '0', STR_PAD_LEFT);
        // $startDate = "$year-$monthPadded-01";
        // $endDate = date('Y-m-t', strtotime($startDate));
        $year = date('Y');
        $month = date('n');
        $quarter = (int) ceil($month / 3);
        $startMonth = ($quarter - 1) * 3 + 1;
        $startDate = date('Y-m-d', strtotime("$year-$startMonth-01"));
        if ($quarter == 4) {
            // 第4季，結束日是12/31
            $endDate = date('Y-m-d', strtotime("$year-12-31"));
        } else {
            // 下一季的第一天
            $nextQuarterFirstDay = date('Y-m-d', strtotime("$year-" . ($startMonth + 3) . "-01"));
            // 往前一天
            $endDate = date('Y-m-d', strtotime($nextQuarterFirstDay . " -1 day"));
        }
        $query = $this->chem_op
            // ->join('chemicals as chem', 'chem.id', '=', 'chemical_operations.chemical_id')
            ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
            ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
            ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
            ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')
            ->join('users as user', 'user.id', '=', 'chemical_operations.company_id')
            ->select('mix.cas_no', 'chemical_operations.state', 'mix.conc')
            ->whereBetween('chemical_operations.operate_date', [$startDate, $endDate]);
        if ($user->role === 'company' && $company_id === $user->id) {
            $query->where('chemical_operations.company_id', $user->id);
        } else if ($user->role !== 'company') {
            $query->where('chemical_operations.custodian', $user->id);
        }
        if ($priority) {
            $query->where('chem.priority', 1);
        }
        if ($pioneer) {
            $query->where('chem.pioneer', 1);
        }
        $distinctCols = ['mix.cas_no', 'chemical_operations.state', 'mix.conc'];
        return $query->distinct(...$distinctCols)->get()->count();
    }
    //報表詳細
    function getChemReport($request, $user)
    {
        $company_id = UserHelper::getCompanyId();
        $query = $this->chem_op->with(
            'supply:id,name,No,tax',
            'area:id,name',
            'build:id,name',
            'factories:id,name'
        )
            // ->leftjoin('approval_doc_lists as list', function ($join) use ($company_id) {
            //     $join->on('list.chemical_id', '=', 'chemical_operations.chemical_id')
            //         ->join('approval_docs as doc', 'doc.id', '=', 'list.doc_id')
            //         ->whereRaw('CAST(list.conc_low AS NUMERIC) <= CAST(chemical_operations.conc AS NUMERIC)')
            //         ->whereRaw('CAST(list.conc_up AS NUMERIC) >= CAST(chemical_operations.conc AS NUMERIC)')
            //         ->whereRaw('chemical_operations.operate_date BETWEEN doc.start_date AND doc.end_date')
            //         ->where('chemical_operations.company_id', $company_id);
            // })
            // ->addSelect('chemical_operations.*', 'doc.per_no','doc.id as doc_id')
            ->join('factory_chemicals as fact_chem', 'fact_chem.id', '=', 'chemical_operations.factory_chemical_id')
            ->join('chemical_ords as ord', 'ord.id', '=', 'fact_chem.ord_id')
            ->join('chemical_mixes as mix', 'mix.ord_id', '=', 'ord.id')
            ->join('chemicals as chem', 'chem.id', '=', 'mix.chemical_id')

            ->where('mix.chemical_id', $request->chemical_id)
            ->where('mix.conc', $request->conc)
            ->where('fact_chem.state', $request->state);
        $query->addSelect(
            'chemical_operations.*'
        );
        if ($user->role === 'company') {
            $query->where('chemical_operations.company_id', $user->id);
        } else {
            $query->where('chemical_operations.custodian', $user->id);
        }
        return $query;
    }

    //報表廠區資料
    function onlyReportGet($request, $user)
    {
        if ($request->filled('factory_id')) {
            return $this->getChemReport($request, $user)->where('chemical_operations.factory_id', $request->factory_id)
                ->where('chemical_operations.build_id', $request->build_id)->where('chemical_operations.area_id', $request->area_id)->first();
        } else {
            return $this->getChemReport($request, $user)->first();
        }
    }

    function getChildReport($request)
    {
        $query = $this->chem_op->with(
            'supply:id,name,No,tax',
            'area:id,name',
            'build:id,name',
            'factories:id,name'
        )->where('chemical_id', $request->chemical_id)
            ->where('conc', $request->conc)
            ->where('state', $request->state)
            ->where('custodian', $request->child);
        return $query;
    }

    //報表化學品資料列表
    function manyReportGet($request, $user)
    {
        if ($request->filled('child')) {
            $data = $this->getChildReport($request);
        } else {
            $data = $this->getChemReport($request, $user);
        }
        if ($request->filled('factory_id')) {
            $data->where('chemical_operations.factory_id', $request->factory_id)
                ->where('chemical_operations.build_id', $request->build_id)
                ->where('chemical_operations.area_id', $request->area_id)
                ->orderby('chemical_operations.operate_date', 'asc');
        }
        if (isset($request->startDate) && isset($request->endDate)) {
            $data->whereBetween('chemical_operations.operate_date', [$request->startDate, $request->endDate])->orderby('chemical_operations.operate_date', 'asc');
        }
        if (isset($request->year) && isset($request->season)) {
            $year = $request->year;
            $startDate = $year . '-' . $request->season;
            $endDate = date('Y-m-t', strtotime($startDate . ' +2 months'));
            $data->whereBetween('chemical_operations.operate_date', [$startDate, $endDate])->orderby('chemical_operations.operate_date', 'asc');
        }
        if (isset($request->year) && !isset($request->season)) {
            $year = $request->year;
            $startDate = $year . '-' . '01' . '-' . '01';
            $endDate = $year . '-' . '12' . '-' . '31';
            $data->whereBetween('chemical_operations.operate_date', [$startDate, $endDate])->orderby('chemical_operations.operate_date', 'asc');
        }
        if (isset($request->year) && isset($request->month)) {
            $year = $request->year;
            $startDate = $year . '-' . $request->month . '-' . '01';
            $endDate = date('Y-m-t', strtotime($startDate));
            $data->whereBetween('chemical_operations.operate_date', [$startDate, $endDate])->orderby('chemical_operations.operate_date', 'asc');
        }
        return $data->get();
    }

    //報表化學品資料列表
    function IdaReportGet($request, $user)
    {
        $data = $this->getChemReport($request, $user);
        $types = ['C', 'B', 'A', 'O', 'N', 'P', 'Q'];
        if ($request->filled('factory_id')) {
            $data->where('factory_id', $request->factory_id)
                ->where('build_id', $request->build_id)
                ->where('area_id', $request->area_id);
        }
        if (isset($request->year) && isset($request->season)) {
            $year = $request->year;
            $startDate = $year . '-' . $request->season;
            $endDate = date('Y-m-t', strtotime($startDate . ' +2 months'));
            $data->whereBetween('operate_date', [$startDate, $endDate])
                ->whereIn('type', $types);
        }
        if (isset($request->year) && isset($request->month)) {
            $year = $request->year;
            $startDate = $year . '-' . $request->month . '-' . '01';
            $endDate = date('Y-m-t', strtotime($startDate));
            $data->whereBetween('operate_date', [$startDate, $endDate])
                ->whereIn('type', $types);
        }
        return $data->orderby('operate_date', 'asc');
    }
    function IdaOtherReportGet($request, $user)
    {
        $data = $this->getChemReport($request, $user);
        if ($request->filled('factory_id')) {
            $data->where('factory_id', $request->factory_id)
                ->where('build_id', $request->build_id)->where('area_id', $request->area_id);
        }
        if (isset($request->startDate) && isset($request->endDate)) {
            $data->whereBetween('operate_date', [$request->startDate, $request->endDate]);
        }
        if (isset($request->year) && isset($request->season)) {
            $year = $request->year;
            $startDate = $year . '-' . $request->season;
            $endDate = date('Y-m-t', strtotime($startDate . ' +2 months'));
            $data->whereBetween('operate_date', [$startDate, $endDate]);
        }
        if (isset($request->year) && isset($request->month)) {
            $year = $request->year;
            $startDate = $year . '-' . $request->month . '-' . '01';
            $endDate = date('Y-m-t', strtotime($startDate));
            $data->whereBetween('operate_date', [$startDate, $endDate]);
        }
        $data->whereIn('type', ['D', 'E', 'F', 'G', 'H', 'J', 'K', 'L'])->orderby('operate_date', 'asc');
        return $data->get();
    }

    //找出之前的結餘量
    function remainder($request, $user)
    {
        $addTypes = ['A', 'C', 'J', 'K', 'E', 'G', 'O'];
        $subTypes = ['B', 'D', 'F', 'H', 'L', 'Q'];
        $startDate = $request->startDate ??
            ($request->year && $request->season ? "{$request->year}-{$request->season}" : ($request->year && $request->month ? "{$request->year}-{$request->month}-01" : "{$request->year}-01-01"));
        $query = $this->getChemReport($request, $user)
            ->where('operate_date', '<', $startDate);
        if (isset($request->startDate) && isset($request->factory_id) && isset($request->build_id) && isset($request->area_id)) {
            $add = $query->clone()->where('chemical_operations.factory_id', $request->factory_id)
                ->where('chemical_operations.build_id', $request->build_id)
                ->where('chemical_operations.area_id', $request->area_id)
                ->whereIn('type', $addTypes)->sum('chemical_operations.weight');
            $sub = $query->clone()->where('chemical_operations.factory_id', $request->factory_id)
                ->where('chemical_operations.build_id', $request->build_id)
                ->where('chemical_operations.area_id', $request->area_id)
                ->whereIn('type', $subTypes)->sum('chemical_operations.weight');
            return round($add - $sub, 8);
        } else if (isset($request->startDate)) {
            $add = $query->clone()
                ->whereIn('type', $addTypes)->sum('chemical_operations.weight');
            $sub = $query->clone()
                ->whereIn('type',  $subTypes)->sum('chemical_operations.weight');

            return round($add - $sub, 8);
        } else if (isset($request->year)) {
            $add = $query->clone()
                ->whereIn('type', $addTypes)->sum('chemical_operations.weight');
            $sub = $query->clone()
                ->whereIn('type',  $subTypes)->sum('chemical_operations.weight');

            return round($add - $sub, 8);
        } else {
            return 0;
        }
    }
    function find($operate_id)
    {
        return $this->chem_op->where('id', $operate_id)->first();
    }

    //最後一筆資料廢棄驗證
    function checkDisposal($operate)
    {
        return $this->chem_op->where('factory_chemical_id', $operate->factory_chemical_id)
            ->where('serial_num', $operate->serial_num)
            ->orderBy('created_at', 'desc')
            ->first();
    }
    function update($data, $operate_id)
    {
        return $this->chem_op->where('id', $operate_id)->update($data);
    }

    function operation($data)
    {
        return  $this->chem_op->create($data);
    }

    function del($data)
    {
        return $this->chem_op->where('factory_chemical_id', $data->factory_chemical_id)->delete();
    }


    function delSubOperate($id)
    {
        $this->chem_op->destroy($id);
    }

    function updateAdd($data, $id, $ori_type)
    {
        switch ($data['ord_type']) {
            case 'A';
                $remark = '新購買';
                break;
            case 'J';
                $remark = '盤點增加';
                break;
            case 'C';
                $remark = '製造';
                break;
            case 'K';
                $remark = '轉入';
                break;
            case 'O';
                $remark = '輸入';
                break;
        }
        $operData = [
            'operate_date' => $data['add_date'],
            'serial_num' => $data['serial_num'],
            'custodian' => $data['custodian'],
            'supplier_id' => $data['supplier_id'],
            'level' => $data['level'],
            'brand' => $data['brand'],
            'weight' => $data['weight'],
            'type' => $data['ord_type'],
            'remark' => $remark
        ];
        $this->chem_op->where('factory_chemical_id', $id)->where('type', $ori_type)->update($operData);
    }
    function destory($id)
    {
        $this->chem_op->whereIn('factory_chemical_id', $id)->delete();
    }
}
