/*
*  Copyright 2019-2020 Zheng Jie
*
*  Licensed under the Apache License, Version 2.0 (the "License");
*  you may not use this file except in compliance with the License.
*  You may obtain a copy of the License at
*
*  http://www.apache.org/licenses/LICENSE-2.0
*
*  Unless required by applicable law or agreed to in writing, software
*  distributed under the License is distributed on an "AS IS" BASIS,
*  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
*  See the License for the specific language governing permissions and
*  limitations under the License.
*/
package com.youchain.businessdata.repository;

import com.youchain.businessdata.domain.PickKitPlan;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;

/**
* @website https://eladmin.vip
* @author DengJiangKun
* @date 2024-02-28
**/
public interface PickKitPlanRepository extends JpaRepository<PickKitPlan, Long>, JpaSpecificationExecutor<PickKitPlan> {
    @Query(value = "SELECT distinct plan_number FROM `data_pick_kit_plan` p WHERE p.id in :ids", nativeQuery = true)
    List<String> queryYcds(@Param("ids") Long[] ids);

    @Query(value = "SELECT p.work_area_id FROM `data_pick_kit_plan` p WHERE p.plan_number =:code and status='WCD' group by p.work_area_id", nativeQuery = true)
    List<Long> queryCodeArea(@Param("code") String code);

    @Query(value = "SELECT * FROM `data_pick_kit_plan` p WHERE p.plan_number=:code and  p.work_area_id =:areaId and status='WCD'", nativeQuery = true)
    List<PickKitPlan> getPickKitPlansCodeArea(@Param("code") String code,@Param("areaId")Long areaId);

    @Query(value = "SELECT * FROM `data_pick_kit_plan` p WHERE p.plan_number =:code", nativeQuery = true)
    List<PickKitPlan> queryPickKitPlanCode(@Param("code") String code);

    @Query(value = "SELECT p.workArea.gcCode FROM PickKitPlan p where p.workArea.gcCode is not null  and p.planNumber in :codes group by p.workArea.gcCode ")
    List<String> groupGc(@Param("codes")String[] codes);

    @Query(value = "select t.cArea.id from BomAccount t,PickKitPlan  p  where p.planNumber in :codes  and p.workArea.gcCode=:gc  and t.bigItem.id=p.bigItem.id and t.item.enabled=true group by t.cArea.id order by t.cArea.id")
    List<Long> groupCArea(@Param("codes")String[] codes,@Param("gc")String gc);

    @Query(value = "select t.rArea.id,max(t.id),sum(t.singles*p.orderQty) from BomAccount t,PickKitPlan  p  where p.planNumber in :codes  and p.workArea.gcCode=:gc and t.cArea.id=:cAreaId and t.bigItem.id=p.bigItem.id and t.item.enabled=true and t.rArea.id=p.workArea.id group by t.rArea.id, t.zPoint.id,t.item.id")
    List<Object[]> sumBomSingle(@Param("codes")String[] codes,@Param("gc")String gc,@Param("cAreaId")Long cAreaId);

}