package cn.csbr.app.service;

import cn.csbr.app.config.FxConfigure;
import cn.csbr.app.exception.ApiException;
import cn.csbr.app.gui.GUIContext;
import cn.csbr.app.model.InboundItem;
import cn.csbr.springboot.dao.mapper.*;
import cn.csbr.springboot.dao.model.*;
import cn.hutool.core.bean.BeanUtil;
import com.alibaba.fastjson.JSON;
import lombok.extern.log4j.Log4j2;
import org.apache.commons.lang.StringUtils;

import org.springframework.beans.BeanUtils;
import org.springframework.transaction.annotation.Transactional;
import tk.mybatis.mapper.entity.Example;

import java.math.BigDecimal;
import java.sql.Date;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.csbr.mybatis.CommonMapper;
import com.github.pagehelper.Page;

/**
 * 查询业务类
 */
@Service
@Log4j2
public class QueryService {
    DateFormat df = new SimpleDateFormat("yyyy-MM-dd");

    @Autowired
    private CommonMapper commonMapper;
    @Autowired
    private GoodsService goodsService;
    @Autowired
    private MfcubestockdetailMapper mfcubestockdetailMapper;

    @Autowired
    private FxConfigure fxConfigure;

    @Autowired
    private MfcubestockstatementMapper mfcubestockstatementMapper;

    @Autowired
    private TrchargingdetailMapper trchargingdetailMapper;
    @Autowired
    private TrchargingMapper trchargingMapper;
    @Autowired
    private MfcubestockdetailOldMapper mfcubestockdetailOldMapper;
    @Autowired
    private GUIContext guiContext;
    @Autowired
    private HisGoodsMapper hisGoodsMapper;

    public synchronized Page<Map<String, Object>> queryStockData(Mfcubestockdetail detail) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        String sql = "SELECT \n" +
                "  a.`guid`,\n" +
                "  a.`LabGuid`,\n" +
                "  a.`GoodsGUID`,\n" +
                "  g.`GoodsCode`,\n" +
                "  a.`GoodsName`,\n" +
                "  g.`GoodsSpec`,\n" +
                "  g.Producer,\n" +
                "  a.`Unit`,\n" +
                "  a.`SupplierGUID`,\n" +
                "  a.`SupplierName`,\n" +
                "  a.`Lot`,\n" +
                "  a.`Productiondate`,\n" +
                "  a.ExpireDate,\n" +
                "  TO_DAYS(a.ExpireDate) - TO_DAYS(NOW()) AS NearDay,\n" +
                "  a.`CubeGuid`,\n" +
                "  a.`CubeName`,\n" +
                "  a.`BillState`\n" +
                "FROM\n" +
                "  Mfcubestockdetail AS a \n" +
                "  LEFT JOIN `his_goods` AS g \n" +
                "    ON a.`GoodsGUID` = g.`GUID` \n" ;

        sql=sql+ "  WHERE 1=1 ";

        if (!StringUtils.isEmpty(detail.getGoodsName())) {
            sql += " AND (g.GoodsCode like '%" + detail.getGoodsName() + "%' or a.GoodsName like '%" + detail.getGoodsName() + "%' or a.LabGuid like'%" + detail.getGoodsName() + "%')";
        }
        if (!StringUtils.isEmpty(detail.getSupplierName())) {
            sql +=" AND a.suppliername like '%"+detail.getSupplierName()+"%'";
        }
        if(StringUtils.isNotEmpty(detail.getBillState())) {
            sql +=" AND a.billstate = '"+detail.getBillState()+"'";
        }
        if(StringUtils.isNotEmpty(detail.getCubeGuid())) {
            sql +=" AND a.CubeGuid = '"+detail.getCubeGuid()+"'";
        }
        if(StringUtils.isNotEmpty(detail.getDepotGuid())) {
            //sql +=" AND a.CubeGuid = '"+detail.getCubeGuid()+"'";
            sql=sql+" and  a.depotGuid="+"'"+detail.getDepotGuid()+"'";
        }
//        sql +=" AND a.Depotname = '"+guiContext.getLoginUserVali().getOrgName()+"'";
        if(detail.getExpireDate() !=null) {
            sql +=" AND a.ExpireDate <= '"+ format.format(detail.getExpireDate()) +"'";
        }
        if(!StringUtils.isEmpty(fxConfigure.getCabinetGroupCode())) {
            sql +=" and CubeGuid in (select guid from  cabinet where groupcode='" + fxConfigure.getCabinetGroupCode() + "')";
        }
        if(StringUtils.isNotEmpty(detail.getCubeName())) {
            sql +=" AND a.CubeName = '"+detail.getCubeName()+"'";
        }
        sql +=" ORDER BY a.`ExpireDate` ASC";
        System.out.println("库存查询SQL："+sql);
        //endregion
        Page<Map> list = (Page<Map>) commonMapper.queryList(sql);

        List<Map<String, Object>> results = new ArrayList();
        Page<Map<String, Object>> newresults = new Page<>();
        newresults.setPageNum(list.getPageNum());
        newresults.setPages(list.getPages());
        newresults.setPageSize(list.getPageSize());
        newresults.setTotal(list.getTotal());

        for(Map map:list) {
            Map<String, Object> result = BeanUtil.beanToMap(map);
            result.putAll(map);
            result.put("labguid",map.get("LabGuid").toString());
            result.put("suppliername",map.get("SupplierName").toString());
            result.put("cubename",map.get("CubeName").toString());
            result.put("guid",map.get("guid").toString());
            StringBuilder goodsInfo = new StringBuilder();
            goodsInfo.append(map.get("GoodsCode")).append("\n")
                    .append(map.get("GoodsName")).append("\n")
                    .append(map.get("Unit"));
            result.put("goodsInfo", goodsInfo.toString());
            StringBuilder goodsguiginfo = new StringBuilder();
            HisGoods hisGoods=hisGoodsMapper.selectByPrimaryKey(map.get("GoodsGUID"));
            if(hisGoods!=null) {
                goodsguiginfo.append(hisGoods.getGoodsspec()).append("\n").append(hisGoods.getModel());
                result.put("gsd",hisGoods.getGoodsspec()+"("+hisGoods.getModel()+")");
            }
            result.put("goodsguiginfo",goodsguiginfo.toString());

            result.put("days", map.get("NearDay").toString());
            String lot = map.get("Lot").toString();
            lot += "\n" + format.format(map.get("ExpireDate"));
            result.put("lot", lot);
            if(map.get("Producer")!=null)
            result.put("goodsCount",map.get("Producer").toString());
            results.add(result);
        }
        newresults.addAll(results);
        return newresults;
    }


    public synchronized Page<Map<String, Object>> queryStockDataByPage(Mfcubestockdetail detail,int pagesize,int pageindex) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");

        String sql = "SELECT \n" +
                "  a.`guid`,\n" +
                "  a.`LabGuid`,\n" +
                "  a.`GoodsGUID`,\n" +
                "  g.`GoodsCode`,\n" +
                "  a.`GoodsName`,\n" +
                "  g.`GoodsSpec`,\n" +
                "  g.Producer,\n" +
                "  a.`Unit`,\n" +
                "  a.`SupplierGUID`,\n" +
                "  a.`SupplierName`,\n" +
                "  a.`Lot`,\n" +
                "  a.`Productiondate`,\n" +
                "  a.ExpireDate,\n" +
                "  TO_DAYS(a.ExpireDate) - TO_DAYS(NOW()) AS NearDay,\n" +
                "  a.`CubeGuid`,\n" +
                "  a.`CubeName`,\n" +
                "  a.`BillState`\n" +
                "FROM\n" +
                "  Mfcubestockdetail AS a \n" +
                "  LEFT JOIN `his_goods` AS g \n" +
                "    ON a.`GoodsGUID` = g.`GUID` \n" ;

        sql=sql+ "  WHERE 1=1 ";

        if (!StringUtils.isEmpty(detail.getGoodsName())) {
            sql += " AND (g.GoodsCode like '%" + detail.getGoodsName() + "%' or a.GoodsName like '%" + detail.getGoodsName() + "%' or a.LabGuid like'%" + detail.getGoodsName() + "%')";
        }
        if (!StringUtils.isEmpty(detail.getSupplierName())) {
            sql +=" AND a.suppliername like '%"+detail.getSupplierName()+"%'";
        }
        if(StringUtils.isNotEmpty(detail.getBillState()) && !detail.getBillState().equals("%")) {
            sql +=" AND a.billstate = '"+detail.getBillState()+"'";
        }
        if(StringUtils.isNotEmpty(detail.getCubeGuid())) {
            sql +=" AND a.CubeGuid = '"+detail.getCubeGuid()+"'";
        }
        if(StringUtils.isNotEmpty(detail.getDepotGuid())) {
            //sql +=" AND a.CubeGuid = '"+detail.getCubeGuid()+"'";
            sql=sql+" and  a.depotGuid="+"'"+detail.getDepotGuid()+"'";
        }
//        sql +=" AND a.Depotname = '"+guiContext.getLoginUserVali().getOrgName()+"'";
        if(detail.getExpireDate() !=null) {
            sql +=" AND a.ExpireDate <= '"+ format.format(detail.getExpireDate()) +"'";
        }
        if(!StringUtils.isEmpty(fxConfigure.getCabinetGroupCode())) {
            sql +=" and CubeGuid in (select guid from  cabinet where groupcode='" + fxConfigure.getCabinetGroupCode() + "')";
        }



        sql +=" ORDER BY a.`ExpireDate` ASC";
        System.out.println("库存查询SQL："+sql);
        //endregion
        Page<Map> list = (Page<Map>) commonMapper.queryList(sql);

        List<Map<String, Object>> results = new ArrayList();
        Page<Map<String, Object>> newresults = new Page<>();
        newresults.setPageNum(list.getPageNum());
        newresults.setPages(list.getPages());
        newresults.setPageSize(list.getPageSize());
        newresults.setTotal(list.getTotal());

        for(Map map:list) {

            System.out.println("map"+map);
            System.out.println("map"+map.get("CubeName"));
            Map<String, Object> result = BeanUtil.beanToMap(map);
            result.put("labguid",map.get("LabGuid").toString());
            result.put("suppliername",map.get("SupplierName").toString());
            result.put("cubename",map.get("CubeName").toString());
            result.put("guid",map.get("guid").toString());
            StringBuilder goodsInfo = new StringBuilder();
            System.out.println("test1");
            goodsInfo.append(map.get("GoodsCode")).append("\n")
                    .append(map.get("GoodsName")).append("\n")
                    .append(map.get("Unit"));
            result.put("goodsInfo", goodsInfo.toString());
            StringBuilder goodsguiginfo = new StringBuilder();
            HisGoods hisGoods=hisGoodsMapper.selectByPrimaryKey(map.get("GoodsGUID"));
            System.out.println("test1");
            if(hisGoods!=null) {
                goodsguiginfo.append(hisGoods.getGoodsspec()).append("\n").append(hisGoods.getModel());
            }


            result.put("goodsguiginfo",goodsguiginfo.toString());



            result.put("days", map.get("NearDay").toString());
            String lot = map.get("Lot").toString();
            lot += "\n" + format.format(map.get("ExpireDate"));
            result.put("lot", lot);
            if(map.get("Producer")!=null)
                result.put("goodsCount",map.get("Producer").toString());
            results.add(result);
        }
        newresults.addAll(results);
        return newresults;
    }

    public synchronized Page<Map<String, Object>> queryStockStatementData(Mfcubestockstatement statement, String beginDate, String endDate, List<String> otype) {
        System.out.println(statement.getGoodsname());
        System.out.println(statement.getSuppliername());
        System.out.println(beginDate);
        System.out.println(endDate);
        Example condition = new Example(Mfcubestockstatement.class);
        Example.Criteria cr = condition.createCriteria().andEqualTo("billno", statement.getBillno());
        //cr.andEqualTo("storeguid",guiContext.getStoreguid());

        cr.andCondition(" 1 = 1 ");
        if (!StringUtils.isEmpty(statement.getGoodsname())) {
            statement.setGoodsname("%" + statement.getGoodsname() + "%");
            String sql = " (  GoodsName like '" + statement.getGoodsname() + "' or LabGuid like'" + statement.getGoodsname() + "')";
            cr.andCondition("(" + sql + ")");
        }
        if (!StringUtils.isEmpty(statement.getSuppliername())) {
            cr.andLike("suppliername", "%" + statement.getSuppliername() + "%");
        }
//        if(!StringUtils.isEmpty(statement.getDepotguid())) {
//            //term.append(" and depotGuid='" + statement.getDepotguid()+ "'");
//            cr.andEqualTo("depotguid",statement.getDepotguid());
//        }
        cr.andCondition(" depotguid ='"+guiContext.getLoginUserVali().getOrgGuid()+"'");
        System.out.println("--------------begin date:" + beginDate);

        if (!StringUtils.isEmpty(beginDate)) {
            cr.andGreaterThanOrEqualTo("createTime", beginDate);
        }
        if (!StringUtils.isEmpty(endDate)) {
            cr.andLessThanOrEqualTo("createTime", endDate);
        }

        if (otype != null && otype.size() > 0) {
            cr.andIn("type", otype);
        }
        cr.andEqualTo("cubeguid", statement.getCubeguid());
        cr.andCondition(" CubeGuid in (select guid from  cabinet where groupcode='" + fxConfigure.getCabinetGroupCode() + "')");
        condition.orderBy("createTime").desc();

        Page<Mfcubestockstatement> datas = (Page) mfcubestockstatementMapper.selectByExample(condition);
        List<Map<String, Object>> results = new ArrayList();
        Page<Map<String, Object>> newresults = new Page<>();
        newresults.setPageNum(datas.getPageNum());
        newresults.setPages(datas.getPages());
        newresults.setPageSize(datas.getPageSize());
        newresults.setTotal(datas.getTotal());
        for (Mfcubestockstatement data : datas) {
            Map<String, Object> result = BeanUtil.beanToMap(data);
            StringBuilder goodsInfo = new StringBuilder();
            goodsInfo.append(data.getGoodsname()).append("\n")
                     .append(data.getUnit());
            result.put("goodsInfo", goodsInfo.toString());
            String lot = data.getLot() + "\n" + df.format(data.getProductiondate()) + "\n" + df.format(data.getExpiredate());
            result.put("lot", lot);
            String type = data.getType();

            StringBuilder goodsguiginfo = new StringBuilder();
            HisGoods hisGoods=hisGoodsMapper.selectByPrimaryKey(data.getGoodsguid());
            if(hisGoods!=null) {
                goodsguiginfo.append(hisGoods.getGoodsspec()).append("\n").append(hisGoods.getModel());
            }
            result.put("goodsguiginfo",goodsguiginfo.toString());

//            1：、入库  2：出库  3：盘升  4：盘损 5：退货，6还货
            switch (type) {

                case "1":
                    result.put("typeTitle", "入库");
                    break;
                case "2":
                    result.put("typeTitle", "取货");
                    break;
                case "3":
                    result.put("typeTitle", "盘升");
                    break;

                case "4":
                    result.put("typeTitle", "盘损");
                    break;
                case "5":
                    result.put("typeTitle", "退货");
                    break;
                case "6":
                    result.put("typeTitle", "还货");
                    break;
                default:
                    result.put("typeTitle", "不详");
                    break;
            }
            results.add(result);
            DatUserLog datUserLog=new DatUserLog();
        }
        newresults.addAll(results);
        return newresults;
    }

    /**
     * 操作记录查询
     * @param statement
     * @param beginDate
     * @param endDate
     * @param otype
     * @return
     */
    public synchronized Page<Map<String, Object>> queryStockStatementData(Mfcubestockstatement statement, LocalDate beginDate, LocalDate endDate, List<String> otype) {
        Example condition = new Example(Mfcubestockstatement.class);
        Example.Criteria cr = condition.createCriteria().andEqualTo("billno", statement.getBillno());
        cr.andEqualTo("storeguid",guiContext.getStoreguid());
        if (!StringUtils.isEmpty(statement.getGoodsname())) {
            statement.setGoodsname("%" + statement.getGoodsname() + "%");
            String sql = "  GoodsName like '" + statement.getGoodsname() + "' or LabGuid like'" + statement.getGoodsname() + "'";
            cr.andCondition("(" + sql + ")");
        }
        if(!StringUtils.isEmpty(statement.getDepotguid())) {
            //term.append(" and depotGuid='" + statement.getDepotguid()+ "'");
            cr.andEqualTo("depotguid",statement.getDepotguid());
        }
        //cr.andEqualTo("depotguid", guiContext.getLoginUserVali().getOrgGuid());
        if (!StringUtils.isEmpty(statement.getSuppliername())) {
            cr.andLike("suppliername", "%" + statement.getSuppliername() + "%");
        }

        System.out.println("--------------begin date:" + beginDate);

        cr.andGreaterThanOrEqualTo("createTime", beginDate);

        cr.andLessThanOrEqualTo("createTime", endDate);

        if (otype != null && otype.size() > 0) {
            cr.andIn("type", otype);
        }
        if (!StringUtils.isEmpty(statement.getCubeguid()))
        cr.andEqualTo("cubeguid", statement.getCubeguid());
        cr.andCondition(" CubeGuid in (select guid from  cabinet where groupcode='" + fxConfigure.getCabinetGroupCode() + "')");
        condition.orderBy("createTime").desc();

        Page<Mfcubestockstatement> datas = (Page) mfcubestockstatementMapper.selectByExample(condition);
        List<Map<String, Object>> results = new ArrayList();
        Page<Map<String, Object>> newresults = new Page<>();
        newresults.setPageNum(datas.getPageNum());
        newresults.setPages(datas.getPages());
        newresults.setPageSize(datas.getPageSize());
        newresults.setTotal(datas.getTotal());

        for (Mfcubestockstatement data : datas) {
            Map<String, Object> result = BeanUtil.beanToMap(data);
            StringBuilder goodsInfo = new StringBuilder();
            goodsInfo.append(data.getGoodscode()).append("\n")
                    .append(data.getGoodsname()).append("\n")
                    .append(data.getUnit());
            result.put("goodsInfo", goodsInfo.toString());

            String lot = data.getLot() +  "\n" + df.format(data.getExpiredate())+"\n"+ df.format(data.getProductiondate());
            result.put("lot", lot);
            //result.put("goodsCount",data.getQty().stripTrailingZeros().toPlainString());
            result.put("goodsCount",data.getManufacturer());

            StringBuilder goodsguiginfo = new StringBuilder();
            HisGoods hisGoods=hisGoodsMapper.selectByPrimaryKey(data.getGoodsguid());
            if(hisGoods!=null) {
                goodsguiginfo.append(hisGoods.getGoodsspec()).append("(").append(hisGoods.getModel()).append(")");
            }
            result.put("goodsguiginfo",goodsguiginfo.toString());
            result.put("czsj",df.format(data.getCreateTime()));
            String type = data.getType();
//            1：入库  2：出库  3：盘升  4：盘损 5：退货，6还货
            switch (type) {

                case "1":
                    result.put("typeTitle", "入库");
                    break;
                case "2":
                    result.put("typeTitle", "取货");
                    break;
                case "3":
                    result.put("typeTitle", "报溢");
                    break;
                case "4":
                    result.put("typeTitle", "报损");
                    break;
                case "5":
                    result.put("typeTitle", "退货");
                    break;
                case "6":
                    result.put("typeTitle", "还货");
                    break;
                default:
                    result.put("typeTitle", "不详");
                    break;
            }
            results.add(result);
        }
        newresults.addAll(results);
        return newresults;
    }

    //使用记录查询方法
    public synchronized Page<Map<String, Object>> queryOperateData(Mfcubestockstatement statement, LocalDate beginDate, LocalDate endDate, List<String> otype) {

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM mfcubestockstatement a INNER JOIN ");
        sql.append(" ( SELECT barcode,MAX(create_time) AS maxtime FROM mfcubestockstatement " );
        sql.append(" where create_time >= '" + beginDate + "'");
        sql.append(" and create_time <= '" + endDate + "'");
        sql.append("  GROUP BY barcode ) b ");
        sql.append(" ON a.barcode=b.barcode and a.DepotGuid='"+guiContext.getLoginUserVali().getOrgGuid()+"' AND a.create_time =b.maxtime and a.storeguid='" + guiContext.getStoreguid() + "'");
        if (!StringUtils.isEmpty(statement.getGoodsname())) {
            sql.append(" and (GoodsName like '%"+statement.getGoodsname()+"%' or LabGuid like'%"+ statement.getGoodsname() + "%')");
        }
        if (!StringUtils.isEmpty(statement.getSuppliername())) {
            sql.append(" and suppliername like '%"+statement.getSuppliername()+"%'");
        }
        if (beginDate != null) {
            sql.append(" and `create_time` >= '" + beginDate + "'");
        }
        if (endDate != null) {
            sql.append(" and `create_time` <= '" + endDate + "'");
        }
        if(!StringUtils.isEmpty(statement.getCubeguid())) {
            sql.append(" and cubeguid='"+statement.getCubeguid()+"'");
        }
        if(!StringUtils.isEmpty(fxConfigure.getCabinetGroupCode())) {
            sql.append(" and CubeGuid in (select guid from  cabinet where groupcode='" + fxConfigure.getCabinetGroupCode() + "')");
        }
//        if(!StringUtils.isEmpty(statement.getDepotguid())) {
//            sql.append(" and depotGuid='" + statement.getDepotguid()+ "'");
//        }

     /*   String sql = " select m.* from mfcubestockstatement m,( SELECT barcode,max(CREATE_TIME) cr  FROM (select * from mfcubestockstatement where 1=1 " +term+
                " order by `create_time` desc limit 10000000000) t" +
                " GROUP BY barcode ) f where m.barcode=f.barcode and m.CREATE_TIME = f.cr order by create_time desc";*/
//        String sql ="SELECT * FROM mfcubestockstatement a INNER JOIN" +
////                "(SELECT barcode,MAX(create_time) AS maxtime FROM mfcubestockstatement GROUP BY barcode) b " +
////                "ON a.barcode=b.barcode AND a.create_time =b.maxtime";
        sql.append(" ORDER BY Create_time desc");
        log.info(sql.toString());
        Page<Map> datas = (Page<Map>) commonMapper.queryList(sql.toString());
        List<Map<String, Object>> results = new ArrayList();
        Page<Map<String, Object>> newresults = new Page<>();
        newresults.setPageNum(datas.getPageNum());
        newresults.setPages(datas.getPages());
        newresults.setPageSize(datas.getPageSize());
        newresults.setTotal(datas.getTotal());
        for (Map map : datas) {
            Mfcubestockstatement data = JSON.parseObject(JSON.toJSONString(map), Mfcubestockstatement.class);
            Map<String, Object> result = BeanUtil.beanToMap(data);
            StringBuilder goodsInfo = new StringBuilder();
            goodsInfo.append(data.getGoodscode()).append("\n")
                     .append(data.getGoodsname()).append("\n")
                     .append(data.getUnit());
            result.put("goodsInfo", goodsInfo.toString());
            //result.put("goodsCount",data.getQty().stripTrailingZeros().toPlainString());
            result.put("goodsCount",data.getManufacturer());
            String lot = data.getLot() + "\n" + df.format(data.getExpiredate());
            result.put("lot", lot);
            StringBuilder goodsguiginfo = new StringBuilder();
            HisGoods hisGoods=hisGoodsMapper.selectByPrimaryKey(data.getGoodsguid());
            if(hisGoods!=null) {
              goodsguiginfo.append(hisGoods.getGoodsspec()).append("\n").append(hisGoods.getModel());
            }
            result.put("goodsguiginfo",goodsguiginfo.toString());
            String type = data.getType();
//            1：入库  2：出库  3：盘升  4：盘损 5：退货，6还货
            switch (type) {
                case "1":
                    result.put("typeTitle", "入库");
                    break;
                case "2":
                    result.put("typeTitle", "取货");
                    break;
                case "3":
                    result.put("typeTitle", "报溢");
                    break;

                case "4":
                    result.put("typeTitle", "报损");
                    break;
                case "5":
                    result.put("typeTitle", "退货");
                    break;
                case "6":
                    result.put("typeTitle", "还货");
                    break;
                default:
                    result.put("typeTitle", "不详");
                    break;
            }
            results.add(result);
        }
        newresults.addAll(results);
        return newresults;
    }

    public synchronized Page<Map<String, Object>> queryExecptionData(Mfcubestockstatement statement, LocalDate beginDate, LocalDate endDate, List<String> otype) {

        StringBuilder sql = new StringBuilder();
        sql.append("SELECT * FROM mfcubestockstatement a INNER JOIN ");
        sql.append(" ( SELECT barcode,MAX(create_time) AS maxtime FROM mfcubestockstatement " );
        sql.append(" where create_time >= '" + beginDate + "'");
        sql.append(" and create_time <= '" + endDate + "'");
        sql.append("  GROUP BY barcode ) b ");
        sql.append(" ON  a.BackSts<>'N' and a.DepotGuid<>a.OperaterDeptGuid and type='2' and a.barcode=b.barcode and a.DepotGuid='"+statement.getDepotguid()+"' AND a.create_time =b.maxtime ");
        if (!StringUtils.isEmpty(statement.getGoodsname())) {
            sql.append(" and (GoodsName like '%"+statement.getGoodsname()+"%' or LabGuid like'%"+ statement.getGoodsname() + "%')");
        }
        if (!StringUtils.isEmpty(statement.getSuppliername())) {
            sql.append(" and suppliername like '%"+statement.getSuppliername()+"%'");
        }
        if (beginDate != null) {
            sql.append(" and `create_time` >= '" + beginDate + "'");
        }
        if (endDate != null) {
            sql.append(" and `create_time` <= '" + endDate + "'");
        }
        if(!StringUtils.isEmpty(statement.getCubeguid())) {
            sql.append(" and cubeguid='"+statement.getCubeguid()+"'");
        }
        if(!StringUtils.isEmpty(fxConfigure.getCabinetGroupCode())) {
            sql.append(" and CubeGuid in (select guid from  cabinet where groupcode='" + fxConfigure.getCabinetGroupCode() + "')");
        }
        sql.append(" ORDER BY Create_time desc");
        log.info(sql.toString());
        Page<Map> datas = (Page<Map>) commonMapper.queryList(sql.toString());
        List<Map<String, Object>> results = new ArrayList();
        Page<Map<String, Object>> newresults = new Page<>();
        newresults.setPageNum(datas.getPageNum());
        newresults.setPages(datas.getPages());
        newresults.setPageSize(datas.getPageSize());
        newresults.setTotal(datas.getTotal());
        for (Map map : datas) {
            Mfcubestockstatement data = JSON.parseObject(JSON.toJSONString(map), Mfcubestockstatement.class);
            Map<String, Object> result = BeanUtil.beanToMap(data);
            StringBuilder goodsInfo = new StringBuilder();
            goodsInfo.append(data.getGoodscode()).append("\n")
                    .append(data.getGoodsname()).append("\n")
                    .append(data.getUnit());
            result.put("goodsInfo", goodsInfo.toString());
            result.put("goodsCount",data.getManufacturer());
            String lot = data.getLot() + "\n" + df.format(data.getExpiredate());
            result.put("lot", lot);
            StringBuilder goodsguiginfo = new StringBuilder();
            HisGoods hisGoods=hisGoodsMapper.selectByPrimaryKey(data.getGoodsguid());
            if(hisGoods!=null) {
                goodsguiginfo.append(hisGoods.getGoodsspec()).append("\n").append(hisGoods.getModel());
            }
            result.put("goodsguiginfo",goodsguiginfo.toString());
            results.add(result);
        }
        newresults.addAll(results);
        return newresults;
    }

    public Page<Map<String, Object>> feeQuery(LocalDate beginDate, LocalDate endDate, String goodsInfo, String suppliername, String patientName,String org) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from trchargingdetail td inner join trcharging t  on td.tguid = t.guid and t.storeguid="+"'"+guiContext.getStoreguid()+"'"+" where  CubeGuid in (select guid from  cabinet where groupcode='").append(fxConfigure.getCabinetGroupCode())
           .append("') ");
        if (!StringUtils.isEmpty(patientName)) {
            sql.append(" and t.name like '%" + patientName + "%'");

        }
      //  sql.append(" and td.depotGuid ='"+guiContext.getLoginUserVali().getOrgGuid()+"'");

        if (!StringUtils.isEmpty(suppliername)) {
            sql.append(" and td.SupplierName like '%" + suppliername + "%'");
        }

        if (!StringUtils.isEmpty(goodsInfo)) {
            sql.append(" and (td.GoodsName like '%" + goodsInfo + "%' or td.LabGuid LIKE '%" + goodsInfo + "%') ");        }
        if(!StringUtils.isEmpty(org)) {
            sql.append(" and td.depotGuid='" + org+ "'");
        }
        if (beginDate != null) {
            sql.append(" and td.InputDate >= '" + beginDate + "'");
        }
        if (endDate != null) {
            sql.append(" and td.InputDate <= '" + endDate + "'");
        }
        sql.append(" order by td.InputDate desc");
        System.out.println(sql);
        Page<Map> datas = (Page) commonMapper.queryList(sql.toString());
        List<Map<String, Object>> results = new ArrayList();
        Page<Map<String, Object>> newresults = new Page<>();
        newresults.setPageNum(datas.getPageNum());
        newresults.setPages(datas.getPages());
        newresults.setPageSize(datas.getPageSize());
        newresults.setTotal(datas.getTotal());
        for (Map data : datas) {
            Map<String, Object> result = new HashMap<>();
            result.putAll(data);
            String pInfo = getStrValue(data, "name") + "\n" + getStrValue(data, "inpatienNo") + "\n" + getStrValue(data, "sickbedNo");
            result.put("patientInfo", pInfo);
            StringBuilder goodsInfol = new StringBuilder();
            goodsInfol.append(getStrValue(data, "GoodsName")).append("\n")
                      .append(getStrValue(data, "Unit"));
            result.put("goodsInfo", goodsInfol.toString());

            String lot = getStrValue(data,"Lot")  + "\n" + getStrValue(data,"ExpireDate")+"\n"+  getStrValue(data,"ProductionDate");;
            result.put("lotinfo", lot);
            result.put("goodsCount",new BigDecimal(getStrValue(data,"qty")).stripTrailingZeros().toPlainString());
            StringBuilder goodsguiginfo = new StringBuilder();
            HisGoods hisGoods=hisGoodsMapper.selectByPrimaryKey(data.get("GoodsGUID"));
            if(hisGoods!=null) {
                goodsguiginfo.append(hisGoods.getGoodsspec()).append("\n").append(hisGoods.getModel());
            }
            result.put("goodsguiginfo",goodsguiginfo.toString());

            results.add(result);
        }
        newresults.addAll(results);
        return newresults;
    }

    private String getStrValue(Map src, String key) {
        Object val = src.get(key);
        if (val == null) {
            return "";
        }
        return val.toString();

    }

    public synchronized Page<Map<String, Object>> queryDetailUseData(String barCode) {
        String sql = "select * from mfcubestockstatement where barcode = '"+barCode+"' ORDER by CREATE_TIME desc";
        Page<Map> datas = (Page<Map>) commonMapper.queryList(sql);
        List<Map<String, Object>> results = new ArrayList();
        Page<Map<String, Object>> newresults = new Page<>();
        newresults.setPageNum(datas.getPageNum());
        newresults.setPages(datas.getPages());
        newresults.setPageSize(datas.getPageSize());
        newresults.setTotal(datas.getTotal());
        for (Map map : datas) {
            Mfcubestockstatement data = JSON.parseObject(JSON.toJSONString(map), Mfcubestockstatement.class);
            Map<String, Object> result = BeanUtil.beanToMap(data);
            data.setCreateTime((java.util.Date)map.get("CREATE_TIME"));
            StringBuilder goodsInfo = new StringBuilder();
            goodsInfo.append(data.getGoodsname()).append("\n")
                    .append(data.getUnit());
            result.put("goodsInfo", goodsInfo.toString());
            result.put("goodsCount",data.getQty().stripTrailingZeros().toPlainString());
            String lot = data.getLot()  + "\n" + df.format(data.getExpiredate());
            result.put("lot", lot);
            StringBuilder goodsguiginfo = new StringBuilder();
            HisGoods hisGoods=hisGoodsMapper.selectByPrimaryKey(data.getGoodsguid());
            if(hisGoods!=null) {
                goodsguiginfo.append(hisGoods.getGoodsspec()).append("\n").append(hisGoods.getModel());
            }
            result.put("goodsguiginfo",goodsguiginfo.toString());
            result.put("czsj", df.format(data.getCreateTime()));
            String type = data.getType();
//            1：入库  2：出库  3：盘升  4：盘损 5：退货，6还货
            switch (type) {
                case "1":
                    result.put("typeTitle", "入库");
                    break;
                case "2":
                    result.put("typeTitle", "出库");
                    break;
                case "3":
                    result.put("typeTitle", "报溢");
                    break;

                case "4":
                    result.put("typeTitle", "报损");
                    break;
                case "5":
                    result.put("typeTitle", "退货");
                    break;
                case "6":
                    result.put("typeTitle", "还货");
                    break;
                default:
                    result.put("typeTitle", "不详");
                    break;
            }
            results.add(result);
        }
        newresults.addAll(results);
        return newresults;
    }

    //根据柜子名称查询库存信息
    public List<InboundItem> queryInboundItemByCabinet(String cabinetCode) {
        List<InboundItem> inboundItems = new ArrayList();
        String sql= "select * from mfcubestockdetail where storeguid = '"+guiContext.getStoreguid()+"' and CubeName = '"+cabinetCode+"'";
        List<Map> details = commonMapper.queryList(sql);
        for (Map detail : details) {
            Mfcubestockdetail data = JSON.parseObject(JSON.toJSONString(detail), Mfcubestockdetail.class);
            InboundItem inboundItem = new InboundItem();
            inboundItem.setBarcode(data.getLabGuid());
            inboundItem.setGoodsname(data.getGoodsName());
            StringBuilder goodsguiginfo = new StringBuilder();
            HisGoods hisGoods=hisGoodsMapper.selectByPrimaryKey(data.getGoodsGUID());
            if(hisGoods!=null) {
                goodsguiginfo.append(hisGoods.getGoodsspec()).append("\n").append(hisGoods.getModel());
            }
            inboundItem.setGoodsspec(goodsguiginfo.toString());
            inboundItems.add(inboundItem);


        }
        return inboundItems;
    }

    @Transactional(rollbackFor = {ApiException.class, Exception.class})
    public String getConSum(Map map,GUIContext g) throws Exception {

        Trcharging trcharging = trchargingMapper.getTrcharging(map.get("operationNo").toString());//根据手术单号查询手术单是否存在
        String stroeid=trcharging.getStoreguid();
        System.out.println("stroeid:"+stroeid);
        if (trcharging == null) {
            log.info("没有该手术单" + map.get("operationNo").toString() + "，无法进行反消耗");
            throw new ApiException("没有该手术单" + map.get("operationNo").toString() + "，无法进行反消耗");
        }
        //进行手术单删除
        int count = trchargingdetailMapper.delTrchargingDetail(trcharging.getGuid());
        if (count > 0) {
            //删除手术单头表
            trchargingMapper.delTrcharging(map.get("operationNo").toString());
            log.info("手术单清除成功=>" + map.get("operationNo").toString());
        }
        //List<ConsumDetail> consumDetailList = mfcubest.getConsumDetailList();
        // for (int j = 0; j < consumDetailList.size(); j++) {
        // ConsumDetail consumDetail = consumDetailList.get(j);
        MfcubestockdetailOld mfcubestockdetailOld = mfcubestockdetailOldMapper.searchDetailByBarcode(map.get("barcode").toString());
        if (mfcubestockdetailOld == null) {
            log.info("该耗材" + map.get("barcode").toString() + "没有消耗记录");
            throw new ApiException("该耗材" + map.get("barcode").toString() + "没有消耗记录");
        }
        Mfcubestockdetail mfcubestockdetail = mfcubestockdetailMapper.searchDetailByBarcode(map.get("barcode").toString());
        if (mfcubestockdetail != null) {
            log.info("该耗材" + map.get("barcode").toString() + "还在柜中没有被消耗");
            throw new ApiException("该耗材" + map.get("barcode").toString() + "还在柜中没有被消耗");
        }
        System.out.println("库存历史记录表"+mfcubestockdetailOld.toString());
        System.out.println("库存历史记录表时间"+mfcubestockdetailOld.getUseTime());
        Mfcubestockdetail mfcubestockdetail1 = new Mfcubestockdetail();
        BeanUtils.copyProperties(mfcubestockdetailOld,mfcubestockdetail1);
        mfcubestockdetail1.setUseTime(new Date(System.currentTimeMillis()));
        System.out.println("库存"+mfcubestockdetail1.toString()+":ss"+stroeid);
        mfcubestockdetail1.setStoreguid(stroeid);
        mfcubestockdetail1.setStorename("");
      //  mfcubestockdetail.setStoreguid(guiContext.getStoreguid());
      //  mfcubestockdetail.setStoreguid("");
        int insert = mfcubestockdetailMapper.insert(mfcubestockdetail1);
        if (insert > 0) {
            //开始删除库存历史
            mfcubestockdetailOldMapper.mfcubestockdetaildel(map.get("barcode").toString());
            log.info("反消耗成功");
        }

        return "ok";
    }

    public String getConSum2(String id,GUIContext g) throws Exception {

        Trchargingdetail trchargingdetail=new Trchargingdetail();
        trchargingdetail.setGuid(id);
        trchargingdetail= trchargingdetailMapper.selectOne(trchargingdetail);
        //进行手术单删除
        int count = trchargingdetailMapper.delTrchargingDetail(trchargingdetail.getGuid());
        if (count > 0) {
            //删除手术单头表
            trchargingMapper.delTrcharging(trchargingdetail.getTguid());
            log.info("手术单清除成功=>" +trchargingdetail.getTguid());
        }
        MfcubestockdetailOld mfcubestockdetailOld = mfcubestockdetailOldMapper.searchDetailByBarcode(trchargingdetail.getBarcode());
        if (mfcubestockdetailOld == null) {
            log.info("该耗材" + trchargingdetail.getBarcode() + "没有消耗记录");
            throw new ApiException("该耗材" + trchargingdetail.getBarcode() + "没有消耗记录");
        }
        Mfcubestockdetail mfcubestockdetail = mfcubestockdetailMapper.searchDetailByBarcode(trchargingdetail.getBarcode());
        if (mfcubestockdetail != null) {
            log.info("该耗材" + trchargingdetail.getBarcode() + "还在柜中没有被消耗");
            throw new ApiException("该耗材" + trchargingdetail.getBarcode() + "还在柜中没有被消耗");
        }
        System.out.println("库存历史记录表"+mfcubestockdetailOld.toString());
        System.out.println("库存历史记录表时间"+mfcubestockdetailOld.getUseTime());
        Mfcubestockdetail mfcubestockdetail1 = new Mfcubestockdetail();
        BeanUtils.copyProperties(mfcubestockdetailOld,mfcubestockdetail1);
        mfcubestockdetail1.setUseTime(new Date(System.currentTimeMillis()));
        mfcubestockdetail1.setStoreguid(mfcubestockdetail.getStoreguid());
        mfcubestockdetail1.setStorename("");
        //  mfcubestockdetail.setStoreguid(guiContext.getStoreguid());
        //  mfcubestockdetail.setStoreguid("");
        int insert = mfcubestockdetailMapper.insert(mfcubestockdetail1);
        if (insert > 0) {
            //开始删除库存历史
            mfcubestockdetailOldMapper.mfcubestockdetaildel(trchargingdetail.getBarcode());
            log.info("反消耗成功");
        }

        return "ok";
    }
}