新增加工产品出库，修改加工产品数据存储逻辑

guoshilong

2022-09-17 680b380e3d9189f2bfd79fb33a6428d82219e4bc

新增加工产品出库，修改加工产品数据存储逻辑

 src/main/java/org/springblade/modules/InventoryLoss/controller/InventoryController.java

@@ -64,7 +64,7 @@
     */
    @GetMapping("/detail")
    @ApiOperationSupport(order = 1)
    @ApiOperation(value = "详情", notes = "传入sale")
    @ApiOperation(value = "详情", notes = "传入inventory")
    public R<Inventory> detail(Inventory inventory) {
        Inventory detail = inventoryService.getOne(Condition.getQueryWrapper(inventory));
        return R.data(detail);
@@ -75,7 +75,7 @@
     */
    @GetMapping("/list")
    @ApiOperationSupport(order = 2)
    @ApiOperation(value = "分页", notes = "传入sale")
    @ApiOperation(value = "分页", notes = "传入inventory")
    public R<IPage<Inventory>> list(Inventory inventory, Query query) {
        IPage<Inventory> pages = inventoryService.page(Condition.getPage(query), Condition.getQueryWrapper(inventory));
        return R.data(pages);
@@ -86,7 +86,7 @@
     */
    @GetMapping("/page")
    @ApiOperationSupport(order = 3)
    @ApiOperation(value = "分页", notes = "传入sale")
    @ApiOperation(value = "分页", notes = "传入inventory")
    public R<IPage<InventoryVO>> page(InventoryVO inventory, Query query) {
        IPage<InventoryVO> pages = inventoryService.selectSalePage(Condition.getPage(query), inventory);
        return R.data(pages);
@@ -97,7 +97,7 @@
     */
    @PostMapping("/save")
    @ApiOperationSupport(order = 4)
    @ApiOperation(value = "新增", notes = "传入sale")
    @ApiOperation(value = "新增", notes = "传入inventory")
    public R save(@Valid @RequestBody Inventory inventory) {
        return R.status(inventoryService.save(inventory));
    }
@@ -107,7 +107,7 @@
     */
    @PostMapping("/update")
    @ApiOperationSupport(order = 5)
    @ApiOperation(value = "修改", notes = "传入sale")
    @ApiOperation(value = "修改", notes = "传入inventory")
    public R update(@Valid @RequestBody Inventory inventory) {
        return R.status(inventoryService.updateById(inventory));
    }
@@ -117,7 +117,7 @@
     */
    @PostMapping("/submit")
    @ApiOperationSupport(order = 6)
    @ApiOperation(value = "新增或修改", notes = "传入sale")
    @ApiOperation(value = "新增或修改", notes = "传入inventory")
    public R submit(@Valid @RequestBody InventoryVO inventory) {
        boolean res = farmProductStockService.stockCompare(inventory.getSaleNum(),inventory.getProid());
        if (!res){
@@ -135,7 +135,7 @@
     */
    @PostMapping("/submitProcess")
    @ApiOperationSupport(order = 8)
    @ApiOperation(value = "新增或修改", notes = "传入sale")
    @ApiOperation(value = "新增或修改", notes = "传入inventory")
    public R submitProcess(@Valid @RequestBody InventoryVO inventory) {
//        boolean res = processService.stockCompare(inventory.getSaleNum(),inventory.getProid());
//        if (!res){

 src/main/java/org/springblade/modules/InventoryLoss/mapper/InventoryMapper.xml

@@ -12,32 +12,21 @@

    <select id="selectSalePage" resultMap="inventoryResultMap">
        select inventory.*,
               strain.strain_name,bdb.dict_value,
        CASE WHEN inventory.type = 1 THEN
        (
        SELECT m.machining_type FROM sys_process_inv inv
        LEFT JOIN sys_machining m ON m.id = inv.product_id
        WHERE inventory.prod_id = inv.id
        )
        ELSE
        (
        SELECT strain.strain_name FROM sys_farm_product_stock sfps
        LEFT JOIN sys_strain strain ON strain.id = sfps.strain_id
        WHERE inventory.prod_id = sfps.id
        )
        END AS proName
               strain.strain_name,mach.machining_type AS proName,bdb.dict_value
        from sys_inventory inventory
        LEFT JOIN sys_strain strain on inventory.strain_id = strain.id
        LEFT JOIN blade_dict_biz bdb ON inventory.reason = bdb.dict_key
        LEFT JOIN sys_land land ON land.id = inventory.land_id
        LEFT JOIN sys_process_inv inv ON inv.id = inventory.prod_id
        LEFT JOIN sys_machining mach ON mach.id = inv.product_id
        where
        <if test="inventory.strainId !=null and inventory.strainId != '' ">
            inventory.strain_id = #{inventory.strainId} AND
        </if>
        1=1
        <if test="inventory.farmId != null and inventory.farmId !='' ">
            land.farm_id = #{inventory.farmId} AND
            AND (land.farm_id = #{inventory.farmId} OR inv.fps_id = #{inventory.farmId})
        </if>
              bdb.code = "lossReason" AND inventory.is_deleted = 0 ORDER BY inventory.create_time DESC
            AND bdb.code = "lossReason"
            AND inventory.is_deleted = 0
            ORDER BY inventory.create_time DESC
    </select>

</mapper>

 src/main/java/org/springblade/modules/process/controller/ProcessController.java

@@ -125,10 +125,35 @@
            farmProductStockService.stockReduce(process.getSaleNum(),process.getProid());
            //向加工产品库存表中添加数据
            ProcessInv processInv = new ProcessInv();
            processInv.setFpsId(process.getProid());

            processInv.setFpsId(process.getFarmId());
            processInv.setProductId(Long.parseLong(process.getProcessId()));
            processInv.setProductInventoryNum(process.getProcessNum());
            processInv.setStrainId(Long.parseLong(process.getStrainId()));
//            processInv.setStrainId(Long.parseLong(process.getStrainId()));
            processInv.setUpdateUser(process.getCreateUser());
            processInvService.insertOrUpdate(processInv);
        }
        return R.status(processService.saveOrUpdate(process));
    }

    /**
     * 新增或修改 加工记录表(加工产品)
     */
    @PostMapping("/submitProcess")
    @ApiOperation(value = "新增或修改", notes = "传入process")
    public R submitProcess(@Valid @RequestBody ProcessVO process) {
        boolean res = processInvService.stockCompare(process.getSaleNum(),process.getProid());
        if (!res){
            throw new org.springblade.core.log.exception.ServiceException(StringUtil.format("当前库存不足！"));
        }else {
            //库存充足，减去相应库存
            processInvService.stockReduce(process.getSaleNum(),process.getProid());
            //向加工产品库存表中添加数据
            ProcessInv processInv = new ProcessInv();

            processInv.setFpsId(process.getFarmId());
            processInv.setProductId(Long.parseLong(process.getProcessId()));
            processInv.setProductInventoryNum(process.getProcessNum());
            processInv.setUpdateUser(process.getCreateUser());
            processInvService.insertOrUpdate(processInv);
        }
@@ -142,27 +167,34 @@
    @PostMapping("/remove")
    @ApiOperationSupport(order = 7)
    @ApiOperation(value = "逻辑删除", notes = "传入ids")
    public R remove(@ApiParam(value = "主键集合", required = true) @RequestParam String ids) {
        //对加工产品库存和农产品库存进行回滚
    public R remove(@ApiParam(value = "主键集合", required = true) @RequestParam String ids,@RequestParam String farmId) {
        Process process = processService.getById(Long.parseLong(ids));
        //通过strainId和landId 确定是由哪一个品种库存加工而来
        if (process.getStrainId() == null){
            //只对加工产品库存操作
            ProcessInv processInvCP = processInvService.selectByFarmIdProductId(farmId,process.getProcessId());
            ProcessInv processInvLY = processInvService.selectByFarmIdProductId(farmId,process.getParentId());
            if (processInvCP.getProductInventoryNum()<0){
                throw new org.springblade.core.log.exception.ServiceException(StringUtil.format("当前加工产品库存不足,不能删除数据！"));
            }else {
                processInvService.stockReduce(process.getProcessNum(),processInvCP.getId().toString());
                processInvService.stockAdd(process.getSaleNum(),processInvLY.getId().toString());
            }
        }else {
            //对加工产品库存和农产品库存操作
        FarmProductStock farmProductStock = farmProductStockService.selectBy2Id(process.getStrainId(),process.getLandId());
        //通过strainId和processId(processId为产品id)确定对应的加工库存
        ProcessInv processInv = processInvService.selectBySPId(process.getStrainId(),process.getProcessId(),farmProductStock.getId());
        //农产品增加相应库存
            ProcessInv processInv = processInvService.selectByFarmIdProductId(farmId,process.getProcessId());
            Double invNum = processInv.getProductInventoryNum()-process.getProcessNum();
            if (invNum<0){
                throw new org.springblade.core.log.exception.ServiceException(StringUtil.format("当前加工产品库存不足,不能删除数据！"));
            }else {
                //更新加工产品库存
                processInv.setProductInventoryNum(invNum);
                processInvService.updateById(processInv);
                //更新农产品库存
        Double weight = Double.parseDouble(farmProductStock.getWeight())+process.getSaleNum();
        farmProductStock.setWeight(weight.toString());
        farmProductStockService.updateById(farmProductStock);
        //加工产品减少对应库存
        Double num = processInv.getProductInventoryNum()-process.getProcessNum();
        if (num<0){
            //删除对应库存
//            processInvService.del(processInv.getId());
            throw new org.springblade.core.log.exception.ServiceException(StringUtil.format("当前库存不足,不能删除数据！"));
        }else {
            //更新库存
            processInv.setProductInventoryNum(num);
            processInvService.updateById(processInv);
            }
        }
        return R.status(processService.deleteLogic(Func.toLongList(ids)));
    }

 src/main/java/org/springblade/modules/process/entity/Process.java

@@ -60,6 +60,10 @@
     */
    private String processId;
    /**
     * 来源id
     */
    private String parentId;
    /**
     * 产品数量
     */
    private Double processNum;

 src/main/java/org/springblade/modules/process/mapper/ProcessMapper.xml

@@ -23,23 +23,32 @@


    <select id="selectProcessPage" resultMap="processResultMap">
        SELECT p.strain_id,p.id,
        p.land_id,sale_time,sale_num,strain_name,process_id,
        SUM(p.process_num) as process_num,
        SELECT     DISTINCT  p.id,
        p.strain_id,
        p.land_id,
        p.sale_time,
        p.sale_num,
        strain_name,p.process_id,
        p.parent_id,
        p.process_num as process_num,
        m.machining_type AS processName,
        m.machining_tp AS tpurl
        m.machining_tp AS tpurl,
        a.machining_type AS parentName
        FROM sys_process p
        LEFT JOIN sys_machining m ON m.id = p.process_id
        LEFT JOIN sys_land sl ON sl.id = p.land_id
        LEFT JOIN sys_strain strain ON p.strain_id = strain.id
        LEFT JOIN sys_process_inv inv ON inv.product_id = p.process_id
        LEFT JOIN (SELECT mm.id,mm.machining_type FROM sys_machining mm) a ON a.id = p.parent_id
        WHERE p.is_deleted = 0
        <if test="process.farmId!=null and process.farmId!=''">
            and sl.farm_id = #{process.farmId}
            AND (sl.farm_id = #{process.farmId} or inv.fps_id = #{process.farmId})
        </if>
        <if test="process.strainId !=null and process.strainId !=''">
            and p.strain_id = #{process.strainId}
        </if>
        GROUP BY p.strain_id,m.machining_type,m.machining_tp,p.land_id,sale_time,sale_num,process_id,p.id ORDER BY p.create_time DESC
        GROUP BY p.id
        ORDER BY p.create_time DESC
    </select>

    <select id="stockCompare" resultType="boolean">

 src/main/java/org/springblade/modules/process/vo/ProcessVO.java

@@ -44,4 +44,8 @@
     * 品种名称
     */
    private String strainName;
    /**
     * 源头名
     */
    private String parentName;
}

 src/main/java/org/springblade/modules/processInv/mapper/ProcessInvMapper.java

@@ -58,4 +58,10 @@
    ProcessInv findById(@Param("prodId") String prodId);

    Double statisticsStock(@Param("year") String year, @Param("farmId") String farmId);

    ProcessInv selectByFarmIdProductId(@Param("productId") Long productId,@Param("farmId") String farmId);

    ProcessInv selectByProcessIdLandId(@Param("processId") String processId, @Param("landId") String landId);

    boolean stockAdd(@Param("saleNum") Double saleNum, @Param("proid") String proid);
}

 src/main/java/org/springblade/modules/processInv/mapper/ProcessInvMapper.xml

@@ -20,10 +20,9 @@

    <select id="selectProcessInvPage" resultMap="processInvResultMap">
        select inv.*,m.machining_tp,
               m.machining_type,strain_name
               m.machining_type
        from sys_process_inv inv
        LEFT JOIN sys_machining m ON m.id = inv.product_id
        LEFT JOIN sys_strain strain ON strain.id = inv.strain_id
        LEFT JOIN sys_farm_product_stock fps ON inv.fps_id = fps.id
        LEFT JOIN sys_land land ON land.id = fps.land_id
        where inv.is_deleted = 0 AND inv.product_inventory_num != 0
@@ -31,7 +30,7 @@
            AND inv.fps_id = #{processInv.fpsId}
        </if>
        <if test="processInv.farmId != null and processInv.farmId !=''">
            AND land.farm_id = #{processInv.farmId}
            AND inv.fps_id = #{processInv.farmId}
        </if>
    </select>

@@ -70,6 +69,11 @@
        WHERE
            id = #{proid}
    </update>
    <update id="stockAdd">
        UPDATE sys_process_inv SET product_inventory_num = product_inventory_num + #{saleNum}
        WHERE
            id = #{proid}
    </update>

    <delete id="del">
        DELETE FROM sys_process_inv WHERE id = #{id}
@@ -102,5 +106,41 @@
            and land.farm_id = #{farmId}
        </if>
    </select>
    <select id="selectByFarmIdProductId" resultType="org.springblade.modules.processInv.entity.ProcessInv">
--         SELECT id,strain_id,product_id,product_inventory_num,create_time,update_user,update_time,status,is_deleted,fps_id
        SELECT *
        FROM sys_process_inv
        WHERE is_deleted = 0
        <if test="farmId != null and farmId !=''">
            AND fps_id = #{farmId}
        </if>
        <if test="productId != null and productId !=''">
            AND product_id = #{productId}
        </if>
    </select>
    <select id="selectByProcessIdLandId" resultType="org.springblade.modules.processInv.entity.ProcessInv">
        SELECT
            inv.id,
            inv.strain_id,
            inv.product_id,
            inv.product_inventory_num,
            inv.create_time,
            inv.update_user,
            inv.update_time,
            inv.fps_id,
            inv.status,
            inv.is_deleted
        FROM
            sys_process_inv inv
            LEFT JOIN sys_land l ON l.farm_id = inv.fps_id
        WHERE inv.is_deleted = 0 AND 1=1
        <if test="processId !=null and processId !='' ">
           AND inv.product_id = #{processId}
        </if>
        <if test="landId !=null and landId !='' ">
            AND l.id = #{landId}
        </if>

    </select>

</mapper>

 src/main/java/org/springblade/modules/processInv/service/IProcessInvService.java

@@ -66,4 +66,10 @@
    ProcessInv findById(String prodId);

    Double statisticsStock(String year, String farmId);

    ProcessInv selectByProcessIdLandId(String processId, String landId);

    ProcessInv selectByFarmIdProductId(String farmId,String processId);

    boolean stockAdd(Double saleNum, String proid);
}

 src/main/java/org/springblade/modules/processInv/service/impl/ProcessInvServiceImpl.java

@@ -44,7 +44,8 @@
    @Override
    public Boolean insertOrUpdate(ProcessInv processInv) {
        //通过strainId和productId查询对应数据
        ProcessInv processInv1 = baseMapper.selectBySFId(processInv.getStrainId(),processInv.getFpsId());
//        ProcessInv processInv1 = baseMapper.selectBySFId(processInv.getStrainId(),processInv.getFpsId());
        ProcessInv processInv1 = baseMapper.selectByFarmIdProductId(processInv.getProductId(),processInv.getFpsId());
        if (processInv1 != null){
            //当存在该数据时，修改
            processInv1.setProductInventoryNum(processInv1.getProductInventoryNum()+processInv.getProductInventoryNum());
@@ -92,4 +93,19 @@
    public Double statisticsStock(String year, String farmId) {
        return baseMapper.statisticsStock(year,farmId);
    }

    @Override
    public ProcessInv selectByProcessIdLandId(String processId, String landId) {
        return baseMapper.selectByProcessIdLandId(processId,landId);
    }

    @Override
    public ProcessInv selectByFarmIdProductId(String farmId,String processId) {
        return baseMapper.selectByFarmIdProductId(Long.parseLong(processId),farmId);
    }

    @Override
    public boolean stockAdd(Double saleNum, String proid) {
        return baseMapper.stockAdd(saleNum,proid);
    }
}

 src/main/java/org/springblade/modules/retrieval/controller/RetrievalController.java

@@ -29,8 +29,13 @@
import org.springblade.core.tool.api.R;
import org.springblade.core.tool.utils.Func;
import org.springblade.core.tool.utils.StringUtil;
import org.springblade.modules.InventoryLoss.entity.Inventory;
import org.springblade.modules.farmplant.entity.FarmProductStock;
import org.springblade.modules.farmplant.service.FarmProductStockService;
import org.springblade.modules.processInv.entity.ProcessInv;
import org.springblade.modules.processInv.service.IProcessInvService;
import org.springblade.modules.processInv.vo.ProcessInvVO;
import org.springblade.modules.sale.vo.SaleVO;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.bind.annotation.RequestParam;
import com.baomidou.mybatisplus.core.metadata.IPage;
@@ -38,6 +43,8 @@
import org.springblade.modules.retrieval.vo.RetrievalVO;
import org.springblade.modules.retrieval.service.IRetrievalService;
import org.springblade.core.boot.ctrl.BladeController;

import java.util.Date;

/**
 * 库存出库记录表 控制器
@@ -53,6 +60,7 @@

    private final IRetrievalService retrievalService;
    private final FarmProductStockService farmProductStockService;
    private final IProcessInvService processInvService;

    /**
     * 详情
@@ -121,9 +129,45 @@
            //库存充足，减去相应库存
            farmProductStockService.stockReduce(retrieval.getSaleNum(), retrieval.getProid());
        }
        retrieval.setProdId(retrieval.getProid());
        return R.status(retrievalService.saveOrUpdate(retrieval));
    }

    /**
     * 新增或修改 库存出库记录表(加工产品)
     */
    @PostMapping("/submitProcess")
    @ApiOperationSupport(order = 8)
    @ApiOperation(value = "新增或修改", notes = "传入sale")
    public R submitProcess(@Valid @RequestBody RetrievalVO retrieval) {
//        //对比加工表库存量
//        boolean res = processService.stockCompare(sale.getSaleNum(),sale.getProid());
//        if (!res){
//            throw new org.springblade.core.log.exception.ServiceException(StringUtil.format("当前库存不足！"));
//        }else {
//            //库存充足，减去相应库存
//            processService.stockReduce(sale.getSaleNum(),sale.getProid());
//        }
//        return R.status(saleService.saveOrUpdate(sale));
        //对比加工表库存量
        boolean res = processInvService.stockCompare(retrieval.getSaleNum(),retrieval.getProid());
        if (!res){
            throw new org.springblade.core.log.exception.ServiceException(StringUtil.format("当前库存不足！"));
        }else {
            //库存充足，减去相应库存
            processInvService.stockReduce(retrieval.getSaleNum(),retrieval.getProid());
            //更新updateTIme 和 updateUser
            ProcessInv processInv = new ProcessInv();
            processInv.setUpdateUser(retrieval.getCreateUser());
            processInv.setUpdateTime(new Date());
            processInv.setId(Long.parseLong(retrieval.getProid()));
            ProcessInvVO processInvVO = processInvService.getLand(processInv);
            retrieval.setLandId(processInvVO.getLandId());
            processInvService.updateById(processInv);
        }
        retrieval.setProdId(retrieval.getProid());
        return R.status(retrievalService.saveOrUpdate(retrieval));
    }

    /**
     * 删除 库存出库记录表
@@ -132,11 +176,25 @@
    @ApiOperationSupport(order = 7)
    @ApiOperation(value = "逻辑删除", notes = "传入ids")
    public R remove(@ApiParam(value = "主键集合", required = true) @RequestParam String ids) {
//        Retrieval retrieval = retrievalService.getById(Long.parseLong(ids));
//        FarmProductStock farmProductStock = farmProductStockService.selectBy2Id(retrieval.getStrainId(), retrieval.getLandId());
//        Double weight = Double.parseDouble(farmProductStock.getWeight())+retrieval.getSaleNum();
//        farmProductStock.setWeight(weight.toString());
//        farmProductStockService.updateById(farmProductStock);
//        return R.status(retrievalService.deleteLogic(Func.toLongList(ids)));

        Retrieval retrieval = retrievalService.getById(Long.parseLong(ids));
        FarmProductStock farmProductStock = farmProductStockService.selectBy2Id(retrieval.getStrainId(), retrieval.getLandId());
        if (retrieval.getType() == 0){
            FarmProductStock farmProductStock = farmProductStockService.getById(retrieval.getProdId());
        Double weight = Double.parseDouble(farmProductStock.getWeight())+retrieval.getSaleNum();
        farmProductStock.setWeight(weight.toString());
        farmProductStockService.updateById(farmProductStock);
        }else {
            ProcessInv inv = processInvService.findById(retrieval.getProdId());
            Double weight = inv.getProductInventoryNum()+retrieval.getSaleNum();
            inv.setProductInventoryNum(weight);
            processInvService.updateById(inv);
        }
        return R.status(retrievalService.deleteLogic(Func.toLongList(ids)));
    }


 src/main/java/org/springblade/modules/retrieval/entity/Retrieval.java

@@ -79,6 +79,13 @@
     * 地块id
     */
    private String landId;

    /**
     * 品种类型（0农产品、1加工产品）
     */
    private Integer type;
    /**
     * 各自库存的主键
     */
    private String prodId;

}

 src/main/java/org/springblade/modules/retrieval/mapper/RetrievalMapper.xml

@@ -52,19 +52,19 @@
        SELECT
        retrieval.*,
        strain.strain_name,
        mach.machining_type AS proName,
        bdb.dict_value AS reDestination
        FROM
        sys_retrieval retrieval
        LEFT JOIN sys_strain strain ON strain.id = retrieval.strain_id
        LEFT JOIN blade_dict_biz bdb ON bdb.dict_key = retrieval.sale_destination1
        LEFT JOIN sys_land land ON land.id = retrieval.land_id
        LEFT JOIN sys_process_inv inv ON inv.id = retrieval.prod_id
        LEFT JOIN sys_machining mach ON mach.id = inv.product_id
        WHERE
        bdb.code = "reDestination"
        <if test="retrieval.strainId !=null and retrieval.strainId != '' ">
            AND retrieval.strain_id = #{retrieval.strainId}
        </if>
        <if test="retrieval.farmId != null and retrieval.farmId !=''">
            AND land.farm_id = #{retrieval.farmId}
            AND (land.farm_id = #{retrieval.farmId} OR inv.fps_id = #{retrieval.farmId})
        </if>
        AND retrieval.is_deleted = 0 ORDER BY retrieval.create_time DESC
    </select>

 src/main/java/org/springblade/modules/retrieval/vo/RetrievalVO.java

@@ -62,4 +62,8 @@
     * 农场id
     */
    private String farmId;
    /**
     * 品种名
     */
    private String proName;
}

 src/main/java/org/springblade/modules/sale/mapper/SaleMapper.xml

@@ -29,42 +29,70 @@


    <select id="selectSalePage" resultMap="saleResultMap">
<!--        SELECT-->
<!--            sale.*,-->
<!--            user.real_name,-->
<!--            strain.strain_name,-->
<!--            strain.url,-->
<!--            district.name as district,-->
<!--            region.city_name,-->
<!--            region.district_name,-->
<!--        CASE WHEN sale.type = 1 THEN-->
<!--        (-->
<!--        SELECT m.machining_type FROM sys_process_inv inv-->
<!--        LEFT JOIN sys_machining m ON m.id = inv.product_id-->
<!--        WHERE sale.prod_id = inv.id-->
<!--        )-->
<!--        ELSE-->
<!--        (-->
<!--        SELECT strain.strain_name FROM sys_farm_product_stock sfps-->
<!--        LEFT JOIN sys_strain strain ON strain.id = sfps.strain_id-->
<!--        WHERE sale.prod_id = sfps.id-->
<!--        )-->
<!--        END AS proName-->
<!--        FROM-->
<!--            sys_sale sale-->
<!--        LEFT JOIN blade_user user on sale.create_user = user.id-->
<!--        LEFT JOIN sys_strain strain on sale.strain_id = strain.id-->
<!--        LEFT JOIN sys_district district on sale.sale_destination = district.code-->
<!--        LEFT JOIN blade_region region on sale.county = region.district_code-->
<!--        LEFT JOIN sys_land land ON land.id = sale.land_id-->
<!--        WHERE-->
<!--        <if test="sale.strainId !=null and sale.strainId != '' ">-->
<!--            sale.strain_id = #{sale.strainId} AND-->
<!--        </if>-->
<!--        <if test="sale.farmId != null and sale.farmId !=''">-->
<!--           land.farm_id = #{sale.farmId} AND-->
<!--        </if>-->
<!--            sale.is_deleted = 0 ORDER BY sale.create_time DESC-->

        SELECT
            sale.*,
            user.real_name,
            USER.real_name,
            strain.strain_name,
            mach.machining_type AS proName,
            strain.url,
            district.name as district,
            district.NAME AS district,
            region.city_name,
            region.district_name,
        CASE WHEN sale.type = 1 THEN
        (
        SELECT m.machining_type FROM sys_process_inv inv
        LEFT JOIN sys_machining m ON m.id = inv.product_id
        WHERE sale.prod_id = inv.id
        )
        ELSE
        (
        SELECT strain.strain_name FROM sys_farm_product_stock sfps
        LEFT JOIN sys_strain strain ON strain.id = sfps.strain_id
        WHERE sale.prod_id = sfps.id
        )
        END AS proName
            region.district_name
        FROM
            sys_sale sale
        LEFT JOIN blade_user user on sale.create_user = user.id
        LEFT JOIN sys_strain strain on sale.strain_id = strain.id
        LEFT JOIN sys_district district on sale.sale_destination = district.code
        LEFT JOIN blade_region region on sale.county = region.district_code
            LEFT JOIN blade_user USER ON sale.create_user = USER.id
            LEFT JOIN sys_strain strain ON sale.strain_id = strain.id
            LEFT JOIN sys_district district ON sale.sale_destination = district.CODE
            LEFT JOIN blade_region region ON sale.county = region.district_code
        LEFT JOIN sys_land land ON land.id = sale.land_id
            LEFT JOIN sys_process_inv inv ON inv.id = sale.prod_id
            LEFT JOIN sys_machining mach ON mach.id = inv.product_id
        WHERE
        <if test="sale.strainId !=null and sale.strainId != '' ">
            sale.strain_id = #{sale.strainId} AND
        </if>
            1 = 1
        <if test="sale.farmId != null and sale.farmId !=''">
           land.farm_id = #{sale.farmId} AND
            AND (land.farm_id = #{sale.farmId} OR inv.fps_id = #{sale.farmId})
        </if>
            sale.is_deleted = 0 ORDER BY sale.create_time DESC
        AND sale.is_deleted = 0
        ORDER BY
        sale.create_time DESC

    </select>