/*
*  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.basicdata.domain.ProductionPlan;
import com.youchain.businessdata.domain.PlanPickDetail;
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 jiangkun
* @date 2024-01-23
**/
public interface PlanPickDetailRepository extends JpaRepository<PlanPickDetail, Long>, JpaSpecificationExecutor<PlanPickDetail> {
    @Query(value = "SELECT * FROM `data_plan_pick_detail` dppd ORDER BY dppd.id DESC LIMIT 0,1", nativeQuery = true)
    PlanPickDetail queryMaxCode();

    /**
     * 获取单号  每个单号只查出条记录
     * @return
     */
    @Query(value = "SELECT * FROM `data_plan_pick_detail` dppd where dppd.status = 'WCD' GROUP BY dppd.code", nativeQuery = true)
    List<PlanPickDetail> queryPlanPickDetailByCode();

    @Query(value = "SELECT DISTINCT code FROM `data_plan_pick_detail` dppd where dppd.status = 'WCD' ", nativeQuery = true)
    List<String> queryPlanPickDetailCode();

    List<PlanPickDetail> findByCode(String code);

    /**
     *  根据单号 状态 查询数据
     * @return 数据
     */
    List<PlanPickDetail> findByStatusAndCode(String status,String code);

    /**
     *  根据单号 状态 查询数据
     * @return 数据
     */
    @Query(value = "SELECT * FROM `data_plan_pick_detail` dppd where dppd.code = :code  AND dppd.big_item_id = :bigItemId ", nativeQuery = true)
    PlanPickDetail findPlanPickData(@Param("code")String code,@Param("bigItemId")Long bigItem);
    @Query(value = "SELECT * FROM `data_plan_pick_detail` dppd where dppd.`status` = :status and dppd.`code` in :codes", nativeQuery = true)
    List<PlanPickDetail> findByStatusAndCodes(@Param("status")String status,@Param("codes")String[] codes);
    @Query(value = "SELECT dppd.gc_code,dppd.remark,max(dppd.id) id FROM `data_plan_pick_detail` dppd where dppd.gc_code is not null  and dppd.code in :codes group by dppd.gc_code,dppd.remark order by dppd.remark ", nativeQuery = true)
    List<Object[]> groupGcPc(@Param("codes")String[] codes);

    @Query(value = "select t.cArea.id from BomAccount t,PlanPickDetail  ptd  where ptd.code in :codes  and ptd.gcCode=:gc and ptd.remark=:pc and t.bigItem.id=ptd.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,@Param("pc")String pc);

    @Query(value = "select t.rArea.id,max(t.id),sum(t.singles*ptd.orderQuantity) from BomAccount t,PlanPickDetail  ptd  where ptd.code in :codes  and ptd.gcCode=:gc and ptd.remark=:pc and t.cArea.id=:cAreaId and t.bigItem.id=ptd.bigItem.id and t.item.enabled=true and t.rArea.id=ptd.zone.id group by t.rArea.id, t.zPoint.id,t.item.id")
    List<Object[]> sumBomSingle(@Param("codes")String[] codes,@Param("gc")String gc,@Param("pc")String pc,@Param("cAreaId")Long cAreaId);

}