/*
*  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.basicdata.repository;

import com.youchain.basicdata.domain.BigItem;
import com.youchain.basicdata.domain.BomAccount;
import com.youchain.businessdata.returnJson.BomPrint;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

/**
* @website https://eladmin.vip
* @author baobinglin
* @date 2024-01-02
**/
public interface BomAccountRepository extends JpaRepository<BomAccount, Long>, JpaSpecificationExecutor<BomAccount> {
    /**
     * 唯一 查询
     * @param big_item_id
     * @param item_id
     * @return
     */
    @Query(value = "SELECT * FROM base_bom_account ba WHERE ba.big_item_id=?1 and ba.item_id=?2 and ba.r_area_id=?3 and ba.z_point_id=?4", nativeQuery = true)
    BomAccount findByOnly(Long big_item_id,Long item_id,Long r_area_id,Long z_point_id);


    @Query(value = "SELECT * FROM base_bom_account ba WHERE ba.big_item_id=?1 and ba.item_id=?2 and ba.ns=?3 and ba.z_point_id=?4 and ba.bonded=?5" , nativeQuery = true)
    BomAccount findByQnique(Long big_item_id,Long item_id,String ns,Long z_point_id,String bonded);

    /**
     * 查询说有的 接收仓库 为true的 bigitem
     * @return
     */
    @Query(value = "SELECT * FROM `base_bom_account` b where b.r_area_id in (select a.id from base_area a where a.bexb=1)  GROUP BY b.big_item_id", nativeQuery = true)
    List<BomAccount> queryBomAccountsBIidOne();

    @Query(value = "select * from base_big_item where id in (SELECT DISTINCT b.big_item_id   FROM `base_bom_account` b where b.r_area_id in (select a.id from base_area a where a.bexb=1) )", nativeQuery = true)
    List<BigItem> queryBigItemALl();

    @Query(value = "SELECT * FROM `base_bom_account` b where b.r_area_id=?1 and b.item_id=?2 and b.z_point_id=?3", nativeQuery = true)
    List<BomAccount> queryBomUnique(Long area_id, Long item_id, Long poin_id);
    @Query(value = "SELECT IFNULL(sum(pc_qty),0) FROM `base_bom_account` b where b.r_area_id=?1 and b.item_id=?2 and b.z_point_id=?3", nativeQuery = true)
    Double getPcQty(Long area_id, Long item_id, Long point_id);
    /**台车库位对应的bom*/
    @Query(value = "SELECT * FROM `base_bom_account` b where b.station_type=:station_type", nativeQuery = true)
    List<BomAccount> queryTchws(String station_type );
    /**固定库位二维码解析*/
    @Query(value = "SELECT * FROM `base_bom_account` b where b.item_id=:item_id and b.z_point_id=:z_point_id and  b.bonded=:bonded", nativeQuery = true)
    List<BomAccount> queryEwmJx(Long item_id, Long z_point_id,String bonded);

    @Query(value = "SELECT * FROM `base_bom_account` b where b.r_area_id=?1 and b.item_id=?2", nativeQuery = true)
    List<BomAccount> getBomList(Long area_id, Long item_id);

    @Query(value = "SELECT * FROM `base_bom_account` b where  b.item_id=?1", nativeQuery = true)
    List<BomAccount> getItemBomList( Long item_id);

    @Query(value = "SELECT * FROM `base_bom_account` b where  b.big_item_id=?1", nativeQuery = true)
    List<BomAccount> getBigItemBomList( Long big_item_id);

    @Query(value = "SELECT r_area_id FROM `base_bom_account` b where  b.big_item_id=?1 group by b.r_area_id", nativeQuery = true)
    List<Long> getJsAreaList( Long big_item_id);
    @Query(value = "SELECT IFNULL(max(b.c_area_id),0) FROM `base_bom_account` b where  b.item_id=?1", nativeQuery = true)
    Long getItemAreaId( Long item_id);

    //获得所有BOM标签
    //SUBSTRING(bi.master_name, 1, CEIL(CHAR_LENGTH(bi.master_name)/2))
    @Query(value = "select max(ba.id) id ,it.code, ba.ns,it.name pm,point.code hw,ba.xz,ba.srs,ba.bonded,ba.supplier," +
            " GROUP_CONCAT(bi.master_name, ';') AS jxs ,GROUP_CONCAT(ba.singles, ';') AS tyls " +
            "from base_bom_account ba \n" +
            "LEFT JOIN base_big_item bi on bi.id=ba.big_item_id\n" +
            "left join base_item it on it.id=ba.item_id\n" +
            "left join base_point point on point.id=ba.z_point_id" +
            " left join base_area area on area.id=ba.r_area_id" +
            " where (ba.bp_type=:bp_type or :bp_type is null) and (it.code=:itemCode or :itemCode is null) and (point.code=:hw or :hw is null) " +
            " and (area.code=:kq or :kq is null)  and (ba.ns=:ns or :ns is null)" +
            " and (bi.code=:bigItemCode or :bigItemCode is null)  and (ba.supplier=:supplier or :supplier is null)" +
            " and (ba.tckw=:tckw or :tckw is null) and ba.bp_type=:dxw" +
            " GROUP BY it.code,it.name,ba.ns,ba.xz,ba.srs,ba.bonded,ba.supplier,point.code " +
            "order by point.code ", nativeQuery = true)
    List<BomPrint> queryPrintAll_old(String bigItemCode,String itemCode, String hw, String ns,String kq,String bp_type,String tckw,String supplier,String dxw);


    @Query(value = "select ba.id id ,it.code, ba.ns,it.name pm,point.code hw,ba.xz,ba.srs,ba.bonded," +
            "ba.supplier,bi.master_name AS jxs ,ba.singles AS tyls " +
            "from base_bom_account ba \n" +
            "LEFT JOIN base_big_item bi on bi.id=ba.big_item_id\n" +
            "left join base_item it on it.id=ba.item_id\n" +
            "left join base_point point on point.id=ba.z_point_id" +
            " left join base_area area on area.id=ba.r_area_id" +
            " where (ba.bp_type=:bp_type or :bp_type is null) and (it.code=:itemCode or :itemCode is null) and (point.code like CONCAT(:hw, '%') or :hw is null) " +
            " and (area.code=:kq or :kq is null)  and (ba.ns=:ns or :ns is null)" +
            " and (bi.code=:bigItemCode or :bigItemCode is null)  and (ba.supplier=:supplier or :supplier is null)" +
            " and (ba.tckw=:tckw or :tckw is null) and ba.bp_type=:dxw" +
            " order by point.code, ba.ns,it.code,ba.bonded,bi.master_name ", nativeQuery = true)
    List<BomPrint> queryPrintAll(String bigItemCode,String itemCode, String hw,
                                 String ns,String kq,String bp_type,String tckw,
                                 String supplier,String dxw);


    @Query(value = "select ba.id id ,it.code, ba.ns,it.name pm,point.code hw,ba.xz,ba.srs,ba.bonded," +
            "ba.supplier,bi.master_name AS jxs ,ba.singles AS tyls,ba.bp_type dxw,v.jxs jxs_v " +
            "from base_bom_account ba \n" +
            "LEFT JOIN base_big_item bi on bi.id=ba.big_item_id\n" +
            "left join base_item it on it.id=ba.item_id\n" +
            "left join base_point point on point.id=ba.z_point_id" +
            " left join base_area area on area.id=ba.r_area_id " +
            " LEFT JOIN bom_print_view v on  v.item_id=ba.item_id and v.bonded=ba.bonded and v.z_point_id=ba.z_point_id and v.ns=ba.ns " +
            " where (ba.bp_type=:bp_type or :bp_type is null) " +
            " and (it.code=:itemCode or :itemCode is null) " +
            " and (point.code like CONCAT(:hw, '%') or :hw is null) " +
            " and (area.code=:kq or :kq is null)  and (ba.ns=:ns or :ns is null)" +
            " and (v.jxs LIKE CONCAT('%',:bigItemCode,'%') or :bigItemCode is null) " +
            " and (ba.supplier=:supplier or :supplier is null)" +
            " and (ba.tckw=:tckw or :tckw is null)" +
            " and (point.code in :hws1 or :hws2 is null) " +
            " order by point.code, ba.ns,it.code,ba.bonded,bi.master_name ", nativeQuery = true)
    List<BomPrint> queryPrintAll(String bigItemCode,String itemCode, String hw,
                                 String ns,String kq,String bp_type,String tckw,
                                 String supplier,List<String> hws1,String hws2);



    @Query(value = "select v.tckw hw,v.jxs from bom_tckw_print_view v " +
            "where  (v.tckw=:tckw or :tckw is null) " +
            " and (v.jxs LIKE CONCAT('%',:jx,'%') or :jx is null) ", nativeQuery = true)
    List<BomPrint> queryTckwPrintAll_old(String tckw,String jx);

    @Query(value = "SELECT `ba`.`tckw` AS `hw`,bi.code ,MAX(bi.name) pm\n" +
            "FROM `base_bom_account` `ba`\n" +
            "LEFT JOIN `base_big_item` `bi` ON `bi`.`id` = `ba`.`big_item_id` \n" +
            "where ba.tckw is not null and ba.tckw!='' and (ba.tckw LIKE CONCAT('%',:tckw,'%') or :tckw is null) and (bi.code=:jx or :jx is null)" +
            "GROUP BY `ba`.`tckw`,bi.code " +
            "order by ba.tckw,bi.code ", nativeQuery = true)
    List<BomPrint> queryTckwPrintAll(String tckw,String jx);

    /**查找台车库位*/
    @Query(value = "SELECT `ba`.* FROM `base_bom_account` `ba` " +
            "LEFT JOIN `base_big_item` `bi` ON `bi`.`id` = `ba`.`big_item_id` " +
            "where (ba.tckw=:tckw or :tckw is null) and (bi.code=:bigItemCode or :bigItemCode is null) " , nativeQuery = true)
    List<BomAccount> queryTckwBom(String tckw,String bigItemCode);

    @Query(value = "select ba.tckw,bi.code,ba.item_id,ba.ky_qz,CAST(sum(ba.singles) as SIGNED) singles from base_bom_account ba\n" +
            "\tLEFT JOIN base_big_item bi on bi.id=ba.big_item_id\n" +
            "\twhere ba.tckw is not null and ba.tckw!='' and ba.ky_qz is not null and ba.singles>0 \n" +
            "\tGROUP BY ba.tckw,bi.code,ba.item_id,ba.ky_qz \n" +
            "\tORDER BY ba.tckw,bi.code;" , nativeQuery = true)
    List<Object[]> queryTckwBom();
}