package com.unis.crk.report.service.impl;

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

import com.unis.crk.report.entity.vo.SearchFieldVo;
import com.unis.crk.report.mapper.ReporterMapper;
import com.unis.crk.report.service.IReporterService;
import com.unis.crk.utils.FunUtils;
import com.unis.crk.utils.ResultUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.*;

/**
 * <p>
 * 通用查询 服务实现类
 * </p>
 *
 * @author Wys
 * @since 2021-12-10
 */
@Service
public class ReporterServiceImpl implements IReporterService {

    @Resource
    private ReporterMapper reporterMapper;
    private String getWhereCondiction( List<SearchFieldVo> searchFieldVos){
        String strWhere="";
        if (!searchFieldVos.isEmpty()){
            for(int i=0;i<searchFieldVos.size();i++){
                String tmpCondiction="";
                String startValue=searchFieldVos.get(i).getFieldStartValue();
                String endValue=searchFieldVos.get(i).getFieldEndValue();
                switch (searchFieldVos.get(i).getFieldType()){
                    case 1://字符
                        //常规
                        if(searchFieldVos.get(i).getFieldValueType()==1){
                            if(!startValue.isEmpty()){
                                tmpCondiction=" and "+searchFieldVos.get(i).getFieldName()+" like '%"+startValue+"%'\n";
                            }
                            //介于
                        }else if (searchFieldVos.get(i).getFieldValueType()==2){
                            if((!startValue.isEmpty())&&(!endValue.isEmpty())){
                                tmpCondiction=" and ("+searchFieldVos.get(i).getFieldName()+" between '"+startValue+"' and '"+endValue+"')\n";
                            }
                            //集合
                        }else{
                            if(!startValue.isEmpty()) {
                                tmpCondiction = " and " + searchFieldVos.get(i).getFieldName() + " in(" + startValue + ")\n";
                            }
                        }
                        break;
                    case 2://日期
                        //常规
                        if(searchFieldVos.get(i).getFieldValueType()==1){
                            if(!startValue.isEmpty()){
                                tmpCondiction=" and "+ FunUtils.fn_FormatDate(FunUtils.DATE_FORMAT_SHORT,searchFieldVos.get(i).getFieldName())+" = '"+startValue+"'\n";
                            }
                            //介于
                        }else if (searchFieldVos.get(i).getFieldValueType()==2){
                            if((!startValue.isEmpty())&&(!endValue.isEmpty())){
                                tmpCondiction=" and ("+FunUtils.fn_FormatDate(FunUtils.DATE_FORMAT_SHORT,searchFieldVos.get(i).getFieldName())+" between '"+startValue+"' and '"+endValue+"')\n";
                            }
                            //集合
                        }else{
                            if(!startValue.isEmpty()) {
                                tmpCondiction = " and " + FunUtils.fn_FormatDate(FunUtils.DATE_FORMAT_SHORT,searchFieldVos.get(i).getFieldName()) + " in(" + startValue + ")\n";
                            }
                        }
                        break;
                    case 3:
                        //常规
                        if(searchFieldVos.get(i).getFieldValueType()==1){
                            if(!startValue.isEmpty()){
                                tmpCondiction=" and "+searchFieldVos.get(i).getFieldName()+" = "+startValue+"\n";
                            }
                            //介于
                        }else if (searchFieldVos.get(i).getFieldValueType()==2){
                            if((!startValue.isEmpty())&&(!endValue.isEmpty())){
                                tmpCondiction=" and ("+searchFieldVos.get(i).getFieldName()+" between "+startValue+" and "+endValue+")\n";
                            }
                            //集合
                        }else{
                            if(!startValue.isEmpty()) {
                                tmpCondiction = " and " + searchFieldVos.get(i).getFieldName() + " in(" + startValue + ")\n";
                            }
                        }
                        break;
                }
                strWhere+=tmpCondiction;
            }
        }
        return strWhere;
    }
    @Override
    public List<Map<String,Object>> CustomReport(String rptID, List<SearchFieldVo> searchFieldVos){
        List<Map<String,Object>> retList = new ArrayList<Map<String,Object>>();
        //1.取得报表信息
        List<Map<String,Object>> list = reporterMapper.getReportInfo(rptID);
        if (!list.isEmpty()) {
            //2.处理报表查询条件信息
            String strWhere=getWhereCondiction(searchFieldVos);
            //3.组织并返回数据
            String sql=list.get(0).get("dymamicSQL").toString();
            String groupBy=list.get(0).get("groupByFields").toString();
            String orderBy=list.get(0).get("orderByFields").toString();
            sql=sql+"\n"+strWhere+"\n"+
                    (groupBy.isEmpty()?"":(" group by "+groupBy))+"\n"+
                    (orderBy.isEmpty()?"":(" order by "+orderBy));
            retList = reporterMapper.superSelect(sql);
        }
        return retList;
    }
    @Override
    public List<Map<String,Object>> CustomReportDynamicFields(String rptID,String lspz){
        List<Map<String,Object>> retList = new ArrayList<Map<String,Object>>();
        //入库查询明细表
        if (rptID.toLowerCase().equals("FA94462B6AE1D1D668854F33FDDEE6A7".toLowerCase())) {
            if ((!lspz.isEmpty())&&(lspz.indexOf(",")==-1)) {
                String sql="SELECT\n" +
                        "concat('def',enumCode) AS \"fieldcode\",\n" +
                        "enumName AS \"fieldname\"\n" +
                        "FROM\n" +
                        "basic_enum\n" +
                        "WHERE\n" +
                        "parentId IN (\n" +
                        "SELECT\n" +
                        "enumId\n" +
                        "FROM\n" +
                        "basic_enum\n" +
                        "WHERE\n" +
                        "parentId = '1046'\n" +
                        "AND enumCode IN (select enumCode from basic_enum where enumId in("+lspz+"))\n" +
                        ")\n" +
                        "AND enumName NOT LIKE '%总量%'";
                retList = reporterMapper.superSelect(sql);
            }
        }
        return retList;
    }
    @Override
    public ResultUtil CustomReportOnePage(String rptID, int pageSize, int pageNum, List<SearchFieldVo> searchFieldVos){
        List<Map<String,Object>> retList = new ArrayList<Map<String,Object>>();
        ResultUtil<Map<String,Object>> data = new ResultUtil<>();
        //1.取得报表信息
        List<Map<String,Object>> list = reporterMapper.getReportInfo(rptID);
        if (!list.isEmpty()) {
            //2.处理报表查询条件信息
            String strWhere=getWhereCondiction(searchFieldVos);
            String strOutWhere=strWhere;
            System.out.println("==================Where(B)=======================");
            System.out.println(strWhere);
            System.out.println("==================Where(E)=======================");

            //3.组织并返回数据
            String sql=list.get(0).get("dymamicSQL").toString();
            String groupBy=list.get(0).get("groupByFields").toString();
            String orderBy=list.get(0).get("orderByFields").toString();
            //化验日结单（入库和出库）【定制】
            if (rptID.toLowerCase().equals("CFD2EB08F9F811ED803500FFCF90C907".toLowerCase())){
                sql=CustomReportOnePageForCheck(searchFieldVos);
                /*处理入库的过滤*/
                sql=sql.replaceAll("#INWHERE#",strWhere);
                /*处理出库的过滤*/
                strOutWhere=strOutWhere
                        .replaceAll("DATA_CRKYW_RMDJ_DEFAULT","DATA_CRKYW_CKRMDJ_DEFAULT")
                        .replaceAll("DATA_CRKYW_JYXX_DEFAULT.jysj","DATA_CRKYW_CKZLJC_DEFAULT.BuildDate");
                sql=sql.replaceAll("#OUTWHERE#",strOutWhere);
            }else {
                //入库查询明细表【定制】
                if (rptID.toLowerCase().equals("FA94462B6AE1D1D668854F33FDDEE6A7".toLowerCase())) {
                    sql = CustomReportOnePageForIndetail(searchFieldVos);
                }
                sql = sql + "\n" + strWhere + "\n";
                sql = sql + (groupBy.isEmpty() ? "" : (" group by " + groupBy)) + "\n" +
                        (orderBy.isEmpty() ? "" : (" order by " + orderBy));
            }
            List<Map<String,Object>> ListCount = reporterMapper.superSelect("select count(1) as \"iCount\" from("+sql+") a");
            retList = reporterMapper.superSelect(sql+" limit "+((pageNum-1)*pageSize)+","+pageSize);
            int countRows=Integer.parseInt(ListCount.get(0).get("iCount").toString());
            int intPageCount=0;
            if((countRows%pageSize)==0)
                intPageCount=countRows/pageSize;
            else
                intPageCount=countRows/pageSize+1;

            Map<String, Object> tmppageResult = new HashMap<String, Object>();
            tmppageResult.put("pages",intPageCount);
            tmppageResult.put("curPage",pageNum);
            tmppageResult.put("totalCount",countRows);
            tmppageResult.put("rows", retList);
            data.success(tmppageResult);
        }
        return data;
    }
    private String CustomReportOnePageForIndetail(List<SearchFieldVo> searchFieldVos) {
        StringBuilder strSQL=new StringBuilder("");
        try {
                String lspz = "";
                if (!searchFieldVos.isEmpty()) {
                    for (int i = 0; i < searchFieldVos.size(); i++) {
                        String fieldName = searchFieldVos.get(i).getFieldName().toLowerCase();
                        String startValue = searchFieldVos.get(i).getFieldStartValue();
                        String endValue = searchFieldVos.get(i).getFieldEndValue();
                        if (fieldName.equals("DATA_CRKYW_RMDJ_DEFAULT.lspz".toLowerCase()) && (!startValue.isEmpty())) {
                            lspz = startValue;
                            break;
                        }
                    }
                }

                String checkDataField="#DATATABLE#.jyz";
                String DefautFields=",max(case when TJ4E7A026C2B5AE7D.enumName ='水分(%)' then "+checkDataField+" else null end) as \"DEF_11101\"\n"+
                        ",max(case when TJ4E7A026C2B5AE7D.enumName ='杂质总量(%)' then "+checkDataField+" else null end) as \"DEF_11102\"\n"+
                        ",max(case when TJ4E7A026C2B5AE7D.enumName ='容重(g/L)' then "+checkDataField+" else null end) as \"DEF_11103\"\n"+
                        ",max(case when TJ4E7A026C2B5AE7D.enumName ='不完善粒总量(%)' then "+checkDataField+" else null end) as \"DEF_11104\"\n"+
                        ",max(case when TJ4E7A026C2B5AE7D.enumName ='矿物质(%)' then "+checkDataField+" else null end) as \"DEF_11105\"\n"+
                        ",max(case when TJ4E7A026C2B5AE7D.enumName ='色泽气味' then "+checkDataField+" else null end) as \"DEF_11108\"\n";
                if ((!lspz.isEmpty())&&(lspz.indexOf(",")==-1)) {
                    String sql="SELECT\n" +
                            "enumCode AS \"code\",\n" +
                            "enumName AS \"name\"\n" +
                            "FROM\n" +
                            "basic_enum\n" +
                            "WHERE\n" +
                            "parentId IN (\n" +
                            "SELECT\n" +
                            "enumId\n" +
                            "FROM\n" +
                            "basic_enum\n" +
                            "WHERE\n" +
                            "parentId = '1046'\n" +
                            "AND enumCode IN (select enumCode from basic_enum where enumId in("+lspz+"))\n" +
                            ")\n" +
                            "AND enumName NOT LIKE '%总量%'";
                            /*
                            "SELECT ENUMITEMCODE as \"code\",enumName as \"name\" FROM enumitem WHERE EnumId = '1046' and isselected='1'\n" +
                            "AND EnumItemCode LIKE CONCAT((select ENUMITEMCODE FROM ENUMITEM WHERE  "+FunUtils.fn_trim()+"(ENUMID)="+FunUtils.fn_trim()+"('1060') and enumId='"+lspz+"' LIMIT 1),'_%')\n" +
                            (getdqVersion(connid).equals("tj")?" and (enumName not like '%总量%')\n":"")+
                            "ORDER BY ENUMITEMCODE";
                             */
                    List<Map<String,Object>> ltLSPZ =reporterMapper.superSelect (sql);
                    if (ltLSPZ.size()>0) {
                        DefautFields="";
                        for(Map<String, Object> item:ltLSPZ){
                            DefautFields+=",max(case when TJ4E7A026C2B5AE7D.enumName ='"+item.get("name").toString()+"' then "+checkDataField+" else 0 end) as \"DEF_"+item.get("code").toString()+"\"\n";
                        }
                    }
                }
                strSQL.append("Select \n"+
                        "DATA_CRKYW_RMDJ_DEFAULT.BizNo as \"BizNo\"\n" +
                        ",org_info.org_id as \"unitId\"\n" +
                        ",org_info.org_name as \"unitName\"\n" +
                        ",org_info.area_code as \"levelCode\"\n" +
                        ",DATA_CRKYW_RMDJ_DEFAULT.tzdbh as \"tzdbh\"\n" +
                        ",DATA_CRKYW_RMDJ_DEFAULT.slr as \"slr\"\n" +
                        ",TD4E7A026C2B5AE7D.enumName as \"lspzName\"\n" +
                        ",T0627A4AFC43F032C.enumName as \"lsxzName\"\n" +
                        ",TF20798F4A4718834.enumName as \"scnfName\"\n" +
                        ",TA18C98BE008FF741.enumName as \"lscdName\"\n" +
                        ",DATA_CRKYW_RMDJ_DEFAULT.cph as \"cph\"\n" +
                        ",TD9C4E9625620926D.enumName as \"lsdjName\"\n" +
                        ",DATA_CRKYW_CPZ_DEFAULT.sjcfbh as \"sjcfbh\"\n" +

                        ",DATA_CRKYW_CMZ_DEFAULT.mz as \"mz\"\n" +
                        ",DATA_CRKYW_CPZ_DEFAULT.pz as \"pz\"\n" +
                        ",DATA_CRKYW_CPZ_DEFAULT.hykl as \"hykl\"\n" +
                        /*化验增扣量*/
                        ",round((DATA_CRKYW_CMZ_DEFAULT.mz-DATA_CRKYW_CPZ_DEFAULT.pz)*ifnull(DATA_CRKYW_CPZ_DEFAULT.hykl,0)/100,0) as \"hysjkl\"\n" +
                        ",DATA_CRKYW_CPZ_DEFAULT.xcks as \"xcks\"\n" +
                        ",DATA_CRKYW_CPZ_DEFAULT.xckbfb as \"xckbfb\"\n" +
                        /*现场扣实际数量*/
                        ",round((DATA_CRKYW_CMZ_DEFAULT.mz-DATA_CRKYW_CPZ_DEFAULT.pz)*ifnull(DATA_CRKYW_CPZ_DEFAULT.xckbfb,0)/100,0) as \"xckbfbsl\"\n" +
                        ",DATA_CRKYW_CPZ_DEFAULT.xckz as \"xckz\"\n" +
                        ",DATA_CRKYW_CPZ_DEFAULT.xckqt as \"xckqt\"\n" +
                        ",DATA_CRKYW_CPZ_DEFAULT.bzwkl as \"bzwkl\"\n" +
                        ",DATA_CRKYW_JSXX_DEFAULT.smlkj as \"smlkj\"\n" +
                        ",DATA_CRKYW_CPZ_DEFAULT.xzklhjz as \"xzklhjz\"\n" +
                        ",DATA_CRKYW_JSXX_DEFAULT.jszl as \"jszl\"\n" +

                        ",DATE_FORMAT(DATA_CRKYW_JSXX_DEFAULT.jssj,'%Y-%m-%d %H:%i') as \"jssj\"\n" +
                        ",DATA_CRKYW_JSXX_DEFAULT.jsy as \"jsy\"\n" +
                        ",data_crkyw_cpz_default.jhapbh as \"jhapbh\"\n" +
                        ",DATE_FORMAT(DATA_CRKYW_JYXX_DEFAULT.jysj,'%Y-%m-%d %H:%i') as \"jysj\"\n" +
                        ",DATA_CRKYW_RMDJ_DEFAULT.cyr as \"cyr\"\n" +
                        ",DATA_CRKYW_RMDJ_DEFAULT.djr as \"djr\"\n" +
                        ",data_crkyw_qyxx_default.qyr as \"qyr\"\n" +
                        ",DATA_CRKYW_JYXX_DEFAULT.jyr as \"jyr\"\n" +
                        ",DATA_CRKYW_CPZ_DEFAULT.jpy as \"jpy\"\n" +
                        ",data_crkyw_rkcm_default.czr as \"czr\"\n"+

                        ",case when DATA_CRKYW_JYXX_DEFAULT.jyjg='3146' then '合格' else '不合格' end  as \"jyjg\"\n" +
                        ",DATA_CRKYW_JYXX_DEFAULT.jybz as \"jybz\"\n");

                strSQL.append(DefautFields.replaceAll("#DATATABLE#", "data_crkyw_jyxx_flt_001"));/*检验指标*/

                strSQL.append("from \n" +
                        "org_info\n" +
                        "inner join DATA_CRKYW_RMDJ_DEFAULT\n" +
                        " on org_info.org_id=DATA_CRKYW_RMDJ_DEFAULT.UnitID\n" +
                        "left join basic_enum TD4E7A026C2B5AE7D\n" +
                        " on TD4E7A026C2B5AE7D.parentId ='1061' and DATA_CRKYW_RMDJ_DEFAULT.lspz=TD4E7A026C2B5AE7D.enumId\n" +
                        "left join basic_enum TF20798F4A4718834\n" +
                        " on TF20798F4A4718834.parentId ='1004' and DATA_CRKYW_RMDJ_DEFAULT.scnf=TF20798F4A4718834.enumId\n" +
                        "left join basic_enum TA18C98BE008FF741\n" +
                        " on TA18C98BE008FF741.parentId ='1010' and DATA_CRKYW_RMDJ_DEFAULT.cd=TA18C98BE008FF741.enumId\n" +
                        "left join basic_enum T0627A4AFC43F032C\n" +
                        " on T0627A4AFC43F032C.parentId ='1032' and DATA_CRKYW_RMDJ_DEFAULT.lsxz=T0627A4AFC43F032C.enumId\n" +
                        " left join DATA_CRKYW_CMZ_DEFAULT\n" +
                        " on DATA_CRKYW_RMDJ_DEFAULT.UnitID=DATA_CRKYW_CMZ_DEFAULT.UnitID and DATA_CRKYW_RMDJ_DEFAULT.BizNo=DATA_CRKYW_CMZ_DEFAULT.BizNo\n" +
                        " left join DATA_CRKYW_CPZ_DEFAULT\n" +
                        " on DATA_CRKYW_RMDJ_DEFAULT.UnitID=DATA_CRKYW_CPZ_DEFAULT.UnitID and DATA_CRKYW_RMDJ_DEFAULT.BizNo=DATA_CRKYW_CPZ_DEFAULT.BizNo\n" +
                        " left join DATA_CRKYW_JYXX_DEFAULT\n" +
                        " on DATA_CRKYW_RMDJ_DEFAULT.UnitID=DATA_CRKYW_JYXX_DEFAULT.UnitID and DATA_CRKYW_RMDJ_DEFAULT.BizNo=DATA_CRKYW_JYXX_DEFAULT.BizNo\n" +
                        " LEFT JOIN data_crkyw_jyxx_flt_001 on DATA_CRKYW_JYXX_DEFAULT.UnitID=data_crkyw_jyxx_flt_001.UnitID and DATA_CRKYW_JYXX_DEFAULT.DataID=data_crkyw_jyxx_flt_001.MainDataID\n" +
                        "left join (select * from basic_enum where parentId in(select enumId from basic_enum where parentId='1046')) TJ4E7A026C2B5AE7D\n" +
                        " on data_crkyw_jyxx_flt_001.zbxmc=TJ4E7A026C2B5AE7D.enumId\n" +
                        " left join DATA_CRKYW_JSXX_DEFAULT\n" +
                        " on DATA_CRKYW_RMDJ_DEFAULT.UnitID=DATA_CRKYW_JSXX_DEFAULT.UnitID and DATA_CRKYW_RMDJ_DEFAULT.BizNo=DATA_CRKYW_JSXX_DEFAULT.BizNo\n" +
                        "left join basic_enum TD9C4E9625620926D\n" +
                        " on TD9C4E9625620926D.parentId ='1002' and DATA_CRKYW_JYXX_DEFAULT.lsdj=TD9C4E9625620926D.enumId\n" +
                        "LEFT JOIN data_crkyw_rkcm_default\n" +
                        " on DATA_CRKYW_RMDJ_DEFAULT.UnitID=data_crkyw_rkcm_default.UnitID and DATA_CRKYW_RMDJ_DEFAULT.BizNo=data_crkyw_rkcm_default.BizNo\n" +
                        "LEFT JOIN data_crkyw_qyxx_default\n" +
                        " on DATA_CRKYW_RMDJ_DEFAULT.UnitID=data_crkyw_qyxx_default.UnitID and DATA_CRKYW_RMDJ_DEFAULT.BizNo=data_crkyw_qyxx_default.BizNo\n" +

                        "left join (select \n" +
                        "storehouse_id as DataID,\n" +
                        "org_id as UnitID,\n" +
                        "storehouse_code,\n" +
                        "storehouse_name,\n" +
                        "N'' AS ParentCode\n" +
                        "from basic_storehouse) Storages\n" +
                        " on Storages.DataID=DATA_CRKYW_CPZ_DEFAULT.sjchID\n" +
                        "\n" +
                        "left join (select \n" +
                        "warehouse_id as DataID,\n" +
                        "org_id as UnitID,\n" +
                        "warehouse_code,\n" +
                        "warehouse_name,\n" +
                        "N'' AS ParentCode\n" +
                        "from basic_warehouse) TBSELF_HWXX\n" +
                        " on TBSELF_HWXX.DataID=DATA_CRKYW_CPZ_DEFAULT.sjhwID\n"+

                        "where 1=1 /*and ifnull(DATA_CRKYW_CPZ_DEFAULT.BizNo,'-1')!='-1'*/\n");
        }catch(Exception e){

        }
        return strSQL.toString();
    }
    private String CustomReportOnePageForCheck(List<SearchFieldVo> searchFieldVos) {
        StringBuilder strSQL=new StringBuilder("");
        try {
            String lspz = "";
            if (!searchFieldVos.isEmpty()) {
                for (int i = 0; i < searchFieldVos.size(); i++) {
                    String fieldName = searchFieldVos.get(i).getFieldName().toLowerCase();
                    String startValue = searchFieldVos.get(i).getFieldStartValue();
                    String endValue = searchFieldVos.get(i).getFieldEndValue();
                    if (fieldName.equals("DATA_CRKYW_RMDJ_DEFAULT.lspz".toLowerCase()) && (!startValue.isEmpty())) {
                        lspz = startValue;
                        break;
                    }
                }
            }

            String checkDataField="#DATATABLE#.jyz";
            String DefautFields=",max(case when TJ4E7A026C2B5AE7D.enumName ='水分(%)' then "+checkDataField+" else null end) as \"DEF_11101\"\n"+
                    ",max(case when TJ4E7A026C2B5AE7D.enumName ='杂质总量(%)' then "+checkDataField+" else null end) as \"DEF_11102\"\n"+
                    ",max(case when TJ4E7A026C2B5AE7D.enumName ='容重(g/L)' then "+checkDataField+" else null end) as \"DEF_11103\"\n"+
                    ",max(case when TJ4E7A026C2B5AE7D.enumName ='不完善粒总量(%)' then "+checkDataField+" else null end) as \"DEF_11104\"\n"+
                    ",max(case when TJ4E7A026C2B5AE7D.enumName ='矿物质(%)' then "+checkDataField+" else null end) as \"DEF_11105\"\n"+
                    ",max(case when TJ4E7A026C2B5AE7D.enumName ='色泽气味' then "+checkDataField+" else null end) as \"DEF_11108\"\n";
            if ((!lspz.isEmpty())&&(lspz.indexOf(",")==-1)) {
                String sql="SELECT\n" +
                        "enumCode AS \"code\",\n" +
                        "enumName AS \"name\"\n" +
                        "FROM\n" +
                        "basic_enum\n" +
                        "WHERE\n" +
                        "parentId IN (\n" +
                        "SELECT\n" +
                        "enumId\n" +
                        "FROM\n" +
                        "basic_enum\n" +
                        "WHERE\n" +
                        "parentId = '1046'\n" +
                        "AND enumCode IN (select enumCode from basic_enum where enumId in("+lspz+"))\n" +
                        ")\n" +
                        "AND enumName NOT LIKE '%总量%'";
                            /*
                            "SELECT ENUMITEMCODE as \"code\",enumName as \"name\" FROM enumitem WHERE EnumId = '1046' and isselected='1'\n" +
                            "AND EnumItemCode LIKE CONCAT((select ENUMITEMCODE FROM ENUMITEM WHERE  "+FunUtils.fn_trim()+"(ENUMID)="+FunUtils.fn_trim()+"('1060') and enumId='"+lspz+"' LIMIT 1),'_%')\n" +
                            (getdqVersion(connid).equals("tj")?" and (enumName not like '%总量%')\n":"")+
                            "ORDER BY ENUMITEMCODE";
                             */
                List<Map<String,Object>> ltLSPZ =reporterMapper.superSelect (sql);
                if (ltLSPZ.size()>0) {
                    DefautFields="";
                    for(Map<String, Object> item:ltLSPZ){
                        DefautFields+=",max(case when TJ4E7A026C2B5AE7D.enumName ='"+item.get("name").toString()+"' then "+checkDataField+" else 0 end) as \"DEF_"+item.get("code").toString()+"\"\n";
                    }
                }
            }
            //1、入库检验
            strSQL.append("Select \n"+
                    "DATA_CRKYW_RMDJ_DEFAULT.BizNo as \"BizNo\"\n" +
                    ",org_info.org_id as \"unitId\"\n" +
                    ",org_info.org_name as \"unitName\"\n" +
                    ",org_info.area_code as \"levelCode\"\n" +
                    ",DATA_CRKYW_RMDJ_DEFAULT.slr as \"slr\"\n" +
                    ",DATA_CRKYW_CPZ_DEFAULT.sjcfbh as \"sjcfbh\"\n" +
                    ",DATA_CRKYW_CPZ_DEFAULT.sjhwbh as \"sjhwbh\"\n" +
                    ",TD4E7A026C2B5AE7D.enumName as \"lspzName\"\n" +
                    ",T0627A4AFC43F032C.enumName as \"lsxzName\"\n" +
                    ",TD9C4E9625620926D.enumName as \"lsdjName\"\n" +
                    ",TF9C4E9625620926D.enumName as \"jyjgName\"\n" +
                    ",DATA_CRKYW_RMDJ_DEFAULT.cph as \"cph\"\n" +
                    ",DATE_FORMAT(data_crkyw_jyxx_default.jysj,'%Y-%m-%d %H:%i') as \"jysj\"\n" +
                    ",data_crkyw_jyxx_default.jyr as \"jyr\"\n");

            strSQL.append(DefautFields.replaceAll("#DATATABLE#", "data_crkyw_jyxx_flt_001"));/*检验指标*/

            strSQL.append("from \n" +
                    "org_info\n" +
                    "inner join DATA_CRKYW_RMDJ_DEFAULT\n" +
                    " on org_info.org_id=DATA_CRKYW_RMDJ_DEFAULT.UnitID\n" +
                    "LEFT JOIN data_crkyw_jyxx_default\n" +
                    "on DATA_CRKYW_RMDJ_DEFAULT.UnitID=data_crkyw_jyxx_default.UnitID and DATA_CRKYW_RMDJ_DEFAULT.BizNo=data_crkyw_jyxx_default.BizNo\n" +
                    "INNER JOIN data_crkyw_jyxx_flt_001 on data_crkyw_jyxx_default.UnitID=data_crkyw_jyxx_flt_001.UnitID and data_crkyw_jyxx_default.DataID=data_crkyw_jyxx_flt_001.MainDataID\n" +
                    "\n" +
                    "left join (select * from basic_enum where parentId in(select enumId from basic_enum where parentId='1046')) TJ4E7A026C2B5AE7D\n" +
                    " on data_crkyw_jyxx_flt_001.zbxmc=TJ4E7A026C2B5AE7D.enumId\n" +
                    "\n" +
                    "left join DATA_CRKYW_CPZ_DEFAULT\n" +
                    "on DATA_CRKYW_RMDJ_DEFAULT.UnitID=DATA_CRKYW_CPZ_DEFAULT.UnitID and DATA_CRKYW_RMDJ_DEFAULT.BizNo=DATA_CRKYW_CPZ_DEFAULT.BizNo\n" +
                    "\n" +
                    "left join basic_enum TD4E7A026C2B5AE7D\n" +
                    " on TD4E7A026C2B5AE7D.parentId ='1061' and DATA_CRKYW_RMDJ_DEFAULT.lspz=TD4E7A026C2B5AE7D.enumId\n" +
                    "\n" +
                    "left join basic_enum T0627A4AFC43F032C\n" +
                    " on T0627A4AFC43F032C.parentId ='1032' and DATA_CRKYW_RMDJ_DEFAULT.lsxz=T0627A4AFC43F032C.enumId\n" +
                    "\n" +
                    "left join basic_enum TD9C4E9625620926D\n" +
                    " on TD9C4E9625620926D.parentId ='1002' and DATA_CRKYW_JYXX_DEFAULT.lsdj=TD9C4E9625620926D.enumId\n" +
                    "\n" +
                    "left join basic_enum TF9C4E9625620926D\n" +
                    " on TF9C4E9625620926D.parentId ='1044' and DATA_CRKYW_JYXX_DEFAULT.jyjg=TF9C4E9625620926D.enumId\n" +
                    //仓房、货位
                    "left join (select \n" +
                    "storehouse_id as DataID,\n" +
                    "org_id as UnitID,\n" +
                    "storehouse_code,\n" +
                    "storehouse_name,\n" +
                    "N'' AS ParentCode\n" +
                    "from basic_storehouse) Storages\n" +
                    " on Storages.DataID=DATA_CRKYW_CPZ_DEFAULT.sjchID\n" +
                    "\n" +
                    "left join (select \n" +
                    "warehouse_id as DataID,\n" +
                    "org_id as UnitID,\n" +
                    "warehouse_code,\n" +
                    "warehouse_name,\n" +
                    "N'' AS ParentCode\n" +
                    "from basic_warehouse) TBSELF_HWXX\n" +
                    " on TBSELF_HWXX.DataID=DATA_CRKYW_CPZ_DEFAULT.sjhwID\n"+

                    "where 1=1 #INWHERE#\n"+
                    "GROUP BY \n"+
                    "DATA_CRKYW_RMDJ_DEFAULT.BizNo\n" +
                    ",org_info.org_id\n" +
                    ",org_info.area_code\n" +
                    ",org_info.org_name\n" +
                    ",DATA_CRKYW_RMDJ_DEFAULT.slr\n" +
                    ",DATA_CRKYW_CPZ_DEFAULT.sjcfbh\n" +
                    ",DATA_CRKYW_CPZ_DEFAULT.sjhwbh\n" +
                    ",TD4E7A026C2B5AE7D.enumName\n" +
                    ",T0627A4AFC43F032C.enumName\n" +
                    ",TD9C4E9625620926D.enumName\n" +
                    ",TF9C4E9625620926D.enumName\n" +
                    ",DATA_CRKYW_RMDJ_DEFAULT.cph\n" +
                    ",data_crkyw_jyxx_default.jysj\n" +
                    ",data_crkyw_jyxx_default.jyr\n");
            //2、出库检验
            strSQL.append("UNION ALL\n");
            strSQL.append("Select \n"+
                    "DATA_CRKYW_CKRMDJ_DEFAULT.BizNo as \"BizNo\"\n" +
                    ",org_info.org_id as \"unitId\"\n" +
                    ",org_info.org_name as \"unitName\"\n" +
                    ",org_info.area_code as \"levelCode\"\n" +
                    ",data_crkyw_ckrmdj_default.slr as \"slr\"\n" +
                    ",data_crkyw_ckzljc_default.chbh as \"chbh\"\n" +
                    ",data_crkyw_ckzljc_default.hwbh as \"hwbh\"\n" +
                    ",TD4E7A026C2B5AE7D.enumName\n" +
                    ",T0627A4AFC43F032C.enumName\n" +
                    ",TD9C4E9625620926D.enumName\n" +
                    ",TF9C4E9625620926D.enumName\n" +
                    ",data_crkyw_ckrmdj_default.cph as \"cph\"\n" +
                    ",DATE_FORMAT(data_crkyw_ckzljc_default.BuildDate,'%Y-%m-%d %H:%i') as \"jysj\"\n" + /*质检日期（zjrj）只有日期，无时分秒；为了排序，只能用BuildDate*/
                    ",data_crkyw_ckzljc_default.jyy as \"jyr\"");
            strSQL.append(DefautFields.replaceAll("#DATATABLE#", "data_crkyw_ckzljc_flt_001"));/*检验指标*/
            strSQL.append("from org_info\n" +
                    //以下支持一通知单多仓房
                    "INNER JOIN (select m.*,t.chID as tz_chID,t.hwID as tz_hwID,t.grain_grade from data_crkyw_ckrmdj_default m LEFT JOIN data_crkyw_tzdxx_flt_001 t on m.tzdcbid=t.DataID where 1=1 \n"+
                    ") data_crkyw_ckrmdj_default on org_info.org_Id=data_crkyw_ckrmdj_default.UnitID\n" +
                    "INNER JOIN data_crkyw_ckzljc_default on data_crkyw_ckrmdj_default.tzdbh=data_crkyw_ckzljc_default.tzdbh\n" +
                    "	and data_crkyw_ckzljc_default.chID=data_crkyw_ckrmdj_default.tz_chID and data_crkyw_ckzljc_default.hwID=data_crkyw_ckrmdj_default.tz_hwID\n"+
                    " left join DATA_CRKYW_CKCMZ_DEFAULT\n" +
                    " on DATA_CRKYW_CKRMDJ_DEFAULT.UnitID=DATA_CRKYW_CKCMZ_DEFAULT.UnitID and DATA_CRKYW_CKRMDJ_DEFAULT.BizNo=DATA_CRKYW_CKCMZ_DEFAULT.BizNo\n"+
                    "INNER JOIN data_crkyw_ckzljc_flt_001 on data_crkyw_ckzljc_default.UnitID=data_crkyw_ckzljc_flt_001.UnitID \n" +
                    "and data_crkyw_ckzljc_default.DataID=data_crkyw_ckzljc_flt_001.MainDataID\n" +
                    "left join basic_enum TD4E7A026C2B5AE7D\n" +
                    " on TD4E7A026C2B5AE7D.parentId ='1061' and data_crkyw_ckrmdj_default.lspz=TD4E7A026C2B5AE7D.enumId\n" +
                    "\n" +
                    "left join basic_enum T0627A4AFC43F032C\n" +
                    " on T0627A4AFC43F032C.parentId ='1032' and data_crkyw_ckrmdj_default.lyxz=T0627A4AFC43F032C.enumId\n" +
                    "\n" +
                    "left join basic_enum TF9C4E9625620926D\n" +
                    " on TF9C4E9625620926D.parentId ='1044' and data_crkyw_ckzljc_default.jyjg=TF9C4E9625620926D.enumId\n" +
                    "left join (select * from basic_enum where parentId in(select enumId from basic_enum where parentId='1046')) TJ4E7A026C2B5AE7D\n" +
                    " on data_crkyw_ckzljc_flt_001.zbxmc=TJ4E7A026C2B5AE7D.enumId\n" +
                    "left join basic_enum TD9C4E9625620926D\n" +
                    " on TD9C4E9625620926D.parentId ='1002' and data_crkyw_ckrmdj_default.grain_grade=TD9C4E9625620926D.enumId\n"+
                    //仓房、货位
                    "left join (select \n" +
                    "storehouse_id as DataID,\n" +
                    "org_id as UnitID,\n" +
                    "storehouse_code,\n" +
                    "storehouse_name,\n" +
                    "N'' AS ParentCode\n" +
                    "from basic_storehouse) Storages\n" +
                    " on Storages.DataID=DATA_CRKYW_CKCMZ_DEFAULT.sjckch\n" +
                    "\n" +
                    "left join (select \n" +
                    "warehouse_id as DataID,\n" +
                    "org_id as UnitID,\n" +
                    "warehouse_code,\n" +
                    "warehouse_name,\n" +
                    "N'' AS ParentCode\n" +
                    "from basic_warehouse) TBSELF_HWXX\n" +
                    " on TBSELF_HWXX.DataID=DATA_CRKYW_CKCMZ_DEFAULT.sjhwh\n"+

                    "where 1=1 #OUTWHERE#\n");
            strSQL.append("GROUP BY \n"+
                    "data_crkyw_ckrmdj_default.BizNo\n" +
                    ",org_info.org_id\n" +
                    ",org_info.org_name\n" +
                    ",org_info.area_code\n" +
                    ",data_crkyw_ckrmdj_default.slr\n" +
                    ",data_crkyw_ckzljc_default.chbh\n" +
                    ",data_crkyw_ckzljc_default.hwbh\n" +
                    ",TD4E7A026C2B5AE7D.enumName\n" +
                    ",T0627A4AFC43F032C.enumName\n" +
                    ",TF9C4E9625620926D.enumName\n" +
                    ",TD9C4E9625620926D.enumName\n"+
                    ",data_crkyw_ckrmdj_default.cph\n" +
                    ",data_crkyw_ckzljc_default.BuildDate\n" + /*质检日期（zjrj）只有日期，无时分秒；为了排序，只能用BuildDate*/
                    ",data_crkyw_ckzljc_default.jyy\n");
            strSQL.append("ORDER BY 4 asc,13 asc");
        }catch(Exception e){

        }
        return strSQL.toString();
    }
}
