package com.landtool.lanbase.modules.res.controller;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletResponse;

import org.apache.shiro.SecurityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.landtool.lanbase.config.SysTemPropertyConfig;
import com.landtool.lanbase.modules.api.utils.Excel;
import com.landtool.lanbase.modules.api.utils.JDBCUtils;
import com.landtool.lanbase.modules.api.utils.PageBean;
import com.landtool.lanbase.modules.res.entity.Res_ExtDataBase;
import com.landtool.lanbase.modules.res.entity.Res_ExtDataSource;
import com.landtool.lanbase.modules.res.entity.Res_MainInfo;
import com.landtool.lanbase.modules.res.entity.Res_Subscriptions;
import com.landtool.lanbase.modules.res.entity.SelectShuJuKu;
import com.landtool.lanbase.modules.res.entity.DataBaseLeftDataSource.DataBaseLeftDataSource;
import com.landtool.lanbase.modules.res.entity.JSONModels.DateType;
import com.landtool.lanbase.modules.res.entity.JSONModels.FieldMapping;
import com.landtool.lanbase.modules.res.entity.JSONModels.SqlMapJson;
import com.landtool.lanbase.modules.res.entity.JSONModels.ZiDuanPeiZi;
import com.landtool.lanbase.modules.res.service.ResExtDataBaseService;
import com.landtool.lanbase.modules.res.service.ResExtDataSourceService;
import com.landtool.lanbase.modules.res.service.ResMainInfoService;
import com.landtool.lanbase.modules.res.service.ResSubscriptionsService;
import com.landtool.lanbase.modules.sys.controller.AbstractController;
import com.landtool.lanbase.modules.sys.service.SysFieldvalueService;

import cn.hutool.json.JSONUtil;

@Controller
@RequestMapping("/res")
public class ResExtDataBaseController extends AbstractController {
	
	@Autowired
    private ResSubscriptionsService resSubscriptionsService;

    @Autowired
    private ResExtDataBaseService resExtDataBaseService;

    @Autowired
    private ResExtDataSourceService resExtDataSourceService;

    @Autowired
    private ResMainInfoService resMainInfoService;
    
    @Autowired
    private SysTemPropertyConfig sysConfig;

    @Autowired
    private SysFieldvalueService FieldUtils;

    @RequestMapping("/ResManage/ResRegister/ExtDataBase")
    public String ExtDataBase(Integer resMainInfoId, Model model) {
        Res_ExtDataBase resExtDataBase = resExtDataBaseService.selectByMainInfoId(resMainInfoId);

        //返回主键list
        List list = new ArrayList();
        if(resExtDataBase != null) {
            //根据数据源id，查询到账号密码
            Res_ExtDataSource resExtDataSource=resExtDataSourceService.selectByPrimaryKey(resExtDataBase.getDatasourceid());
            //开始拼接sql,然后判断是哪个数据库
            String leirong="[]";
            if(resExtDataSource != null) {
                if (resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")) {
                    String sql = " select name as TABLENAME  from sysobjects where xtype='u' ORDER BY TABLENAME";
                    leirong = JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), sql);

                }
                if (resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")) {
                    String sql = "SELECT table_name as tablename FROM user_tables ORDER BY table_name";
                    leirong = JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), resExtDataSource.getPort(), sql);
                }
                if (resExtDataSource.getDatabasetype().equalsIgnoreCase("MySQL")) {
                    String sql = " select table_name as TABLENAME  from information_schema.tables where table_type='base table' ORDER BY TABLENAME";
                    leirong = JDBCUtils.MysqlConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), resExtDataSource.getPort(), sql);
                }
            }
            LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
            if(!leirong.equals("[]")) {
                JSONArray obj = JSON.parseArray(leirong);//获取用户信息
                if(obj.getJSONObject(0).get("success").toString().equals("true")) {
                    JSONArray maps = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
                    for (int i = 0; i < maps.size(); i++) {
//                        Map maps = (Map) JSON.parse(obj.getJSONObject(0).get("data").toString());
                        map.put(maps.getJSONObject(i).get("TABLENAME").toString(), maps.getJSONObject(i).get("TABLENAME").toString());
                    }
                }
            }
            model.addAttribute("map",map);
            //查询id对应的 name
            Res_ExtDataSource res_extDataSource=resExtDataSourceService.selectByPrimaryKey(resExtDataBase.getDatasourceid());
            model.addAttribute("Databasename",res_extDataSource == null ? null : res_extDataSource.getDatabasealias());  //缺少null值处理 alert 2018/12/27
            model.addAttribute("databasetype",res_extDataSource == null ? null : res_extDataSource.getDatabasetype());
            model.addAttribute("Res_ExtDataBase",resExtDataBase);
            model.addAttribute("tabletype", res_extDataSource == null ? null : res_extDataSource.getTabletype());
            model.addAttribute("resMainInfoId",resMainInfoId);
            Res_MainInfo res_mainInfo = resMainInfoService.selectByPrimaryKey(resMainInfoId);
            model.addAttribute("resMainInfo",res_mainInfo);

            if(resExtDataBase.getPrimarykey() != null) {
                String[] keys = resExtDataBase.getPrimarykey().split(",");
                for (int i =0;i<keys.length;i++) {
                    list.add(keys[i]);
                }
            }
        }
        else {
            Res_ExtDataBase resExtDataBase1 = new Res_ExtDataBase();
            model.addAttribute("Res_ExtDataBase",resExtDataBase1);
            model.addAttribute("resMainInfo",new Res_MainInfo());
            model.addAttribute("tabletype", 0);
        }
        if(SecurityUtils.getSubject().isPermitted("org_user_admin")) {
            //判断当前用户是否是管理员，是管理员或是未提交的资源才可以修改资源相关信息
            model.addAttribute("admin",true);
        }
        else {
            model.addAttribute("admin",false);
        }

        model.addAttribute("keys",list);
        return "ResManage/ResRegister/ExtDataBase";
    }

    @ResponseBody
    @RequestMapping("/resExtDataBase/deleteByPrimaryKey")
    public int deleteByPrimaryKey(Integer resourceid) {
        return resExtDataBaseService.deleteByPrimaryKey(resourceid);
    }

    @ResponseBody
    @RequestMapping("/resExtDataBase/insert")
    public int insert(Res_ExtDataBase record) {
        return resExtDataBaseService.insert(record);
    }

    @ResponseBody
    @RequestMapping("/resExtDataBase/insertSelective")
    public int insertSelective(Res_ExtDataBase record) {
        return resExtDataBaseService.insertSelective(record);
    }

    @ResponseBody
    @RequestMapping("/resExtDataBase/selectByPrimaryKey")
    public Res_ExtDataBase selectByPrimaryKey(Integer datasourceid) {
        return resExtDataBaseService.selectByPrimaryKey(datasourceid);
    }

    @ResponseBody
    @RequestMapping("/resExtDataBase/updateByPrimaryKeySelective")
    public int updateByPrimaryKeySelective(Res_ExtDataBase record) {
        return resExtDataBaseService.updateByPrimaryKeySelective(record);
    }

    @ResponseBody
    @RequestMapping("/resExtDataBase/updateByPrimaryKey")
    public int updateByPrimaryKey(Res_ExtDataBase record) {
        return resExtDataBaseService.updateByPrimaryKey(record);
    }

    /** 插入数据库表列表功能(可以只填写必填字段)*/
    @ResponseBody
    @RequestMapping("/resExtDataBase/insertSelectiveAndUpdate")
    public int insertSelectiveAndUpdate(Res_ExtDataBase record) {
    	//============20190401 wangq--字段配置json多了\ ================
    	StringBuffer sbf = new StringBuffer();
    	sbf.append("{\"sql\":");
    	JSONObject obj = (JSONObject)JSONObject.parse(record.getFieldconfig());
    	sbf.append(obj.get("sql").toString());
    	sbf.append("}");
    	record.setFieldconfig(sbf.toString());
    	//===================
        Res_ExtDataBase resExtDataBase=resExtDataBaseService.selectByMainInfoId(record.getResourceid());
        if(resExtDataBase == null){
            //添加
            Res_ExtDataSource res_extDataSource = resExtDataSourceService.selectByPrimaryKey(record.getDatasourceid());
            if(!res_extDataSource.getDatabasetype().equals("Oracle")) {
                if(record.getSpacename() != null) {
                    record.setSpacename(null);
                }
            }
            return resExtDataBaseService.insertSelective(record);
        }else {
            //更新
            Res_ExtDataSource res_extDataSource = resExtDataSourceService.selectByPrimaryKey(record.getDatasourceid());
            if(!res_extDataSource.getDatabasetype().equals("Oracle")) {
                if(record.getSpacename() != null) {
                    record.setSpacename(null);
                }
            }
            return  resExtDataBaseService.updateByPrimaryKey(record);
        }
    }
    //查询id 是否填写了扩展表
    @ResponseBody
    @RequestMapping("/ZiYuan/selectISNULL")
    public String selectISNULL(Integer resourceid){
        DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(resourceid);
        if(database==null){
            return "1";
        }else {
            return "0";
        }
    }


    //根据id拼接sql,查询sql
    @RequestMapping("/ZiYuan/BaseLeftSource")
    public String selectBaseLeftSource(SelectShuJuKu selectShuJuKu,Model model){
        model.addAttribute("resourceid",selectShuJuKu.getResourceid());
        DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(selectShuJuKu.getResourceid());
        //如果没有配置，查询所有的资源
        String leirong="0";
        JSONArray objList = null;
        String sqlConfig="";
        List list=new ArrayList();
        Map<String, String> widthMap = new HashMap<String, String>();//存自带宽度
        if(database.getFieldconfig().equals("")){
            String sql="SELECT a.name NAME,b.name type,cast(g.[value] as varchar(500)) [remarks] FROM  syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties' left join  sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id where d.name='"+database.getTablename()+"' order by a.name";
            leirong=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),database.getUsername(),database.getPassword(),sql);
            objList = JSON.parseArray(leirong);//获取用户信息
            StringBuffer str=new StringBuffer();
            str.append("[");
            for (int i = 0; i < objList.size(); i++) {
                if(i>0){
                    sqlConfig+= ",";
                    str.append(",");
                }
                DateType dateType=JSONUtil.toBean(objList.get(i).toString(), DateType.class);
                sqlConfig+=dateType.getNAME();
                sqlConfig+=" as ";
                sqlConfig+=dateType.getNAME();
                str.append("{\"ZiDuanName\":\""+dateType.getNAME()+"\"");
                str.append(",");
                str.append("\"ZiDuanType\":\""+dateType.getType()+"\"");
                str.append(",");
                str.append("\"ZiDuanBieMing\":\""+dateType.getNAME()+"\"");
                str.append(",");
                str.append("\"ZiDuanGeShi\":{}}");
                list.add(dateType.getNAME());
            }
            str.append("]");
            JSONArray jsarr = JSONArray.parseArray(String.valueOf(str));
            model.addAttribute("map",jsarr);
        }else {
            //开始拼接sql,然后判断是哪个数据库
            JSONObject sqlObj = JSON.parseObject(database.getFieldconfig());
            JSONArray jsarr = JSONArray.parseArray(sqlObj.getString("sql"));

            for (int i = 0; i < jsarr.size(); i++) {
                ZiDuanPeiZi ziDuanPeiZi=JSONUtil.toBean(jsarr.get(i).toString(), ZiDuanPeiZi.class);
                if(Integer.parseInt(ziDuanPeiZi.getZiDuanKuanDu())>=0 && Integer.parseInt(ziDuanPeiZi.getZiDuanKuanDu())<50){
                	ziDuanPeiZi.setZiDuanKuanDu("50");//最小宽度
                }
                if(Integer.parseInt(ziDuanPeiZi.getZiDuanKuanDu())>1000){
                	ziDuanPeiZi.setZiDuanKuanDu("1000");//最大宽度
                }
                if(ziDuanPeiZi.getZiDuanKuanDu().equals("") || ziDuanPeiZi.getZiDuanKuanDu()==null){
                	ziDuanPeiZi.setZiDuanKuanDu("0");
                }
                widthMap.put(ziDuanPeiZi.getZiDuanBieMing(),ziDuanPeiZi.getZiDuanKuanDu());
                if(i>0){
                    sqlConfig+= ",";
                }
                String Geshi=ziDuanPeiZi.getZiDuanGeShi().toJSONString();
                String ZiDuanMing=ziDuanPeiZi.getZiDuanName();
                if(ziDuanPeiZi.getZiDuanType().equals("时间") && Geshi.indexOf("riqi")>0 && Geshi.indexOf("1")>0 ){
                    if(database.getDatabasetype().equalsIgnoreCase("Oracle")) {

                    }
                    else {
                        ZiDuanMing = "CONVERT(varchar(12)," + ziDuanPeiZi.getZiDuanName() + ",111 )";
                    }
                }
                if((ziDuanPeiZi.getZiDuanType().equals("小数") || ziDuanPeiZi.getZiDuanType().equals("整数")) && Geshi.indexOf("xiaoshudian")>0){
                    //获取字段保留几位小数
                    String[] xiaoshu=Geshi.split(":");
                    //获取到保留几位小数
                    String num= xiaoshu[1].replace("}","").trim();
                    if(database.getDatabasetype().equalsIgnoreCase("Oracle")) {
                        ZiDuanMing = "round(" + ziDuanPeiZi.getZiDuanName() + "," + num + ")";
                    }
                    else {
                        ZiDuanMing = "CONVERT(NUMERIC(18," + num + ")," + ziDuanPeiZi.getZiDuanName() + ")";
                    }
                }

                sqlConfig+=ZiDuanMing ;
                sqlConfig+=" as  \"";
                sqlConfig+=ziDuanPeiZi.getZiDuanBieMing();
                sqlConfig += "\"";
                list.add(ziDuanPeiZi.getZiDuanBieMing());
//            map.put(ziDuanPeiZi.getZiDuanName(),ziDuanPeiZi.getZiDuanBieMing());
            }
            System.out.println(jsarr);
            model.addAttribute("map",jsarr);
        }

        String sql="select "+sqlConfig+" from  "+database.getTablename() ;
        leirong = "[]";
        String msg = "";
        if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
            leirong=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),
                    database.getUsername(), database.getPassword(),sql);
        }
        if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
            leirong=JDBCUtils.OracleConnUtils(database.getServeraddress(),database.getDatabasename(), database.getUsername(), database.getPassword(),database.getPort(),sql);
        }
        if(!leirong.equals("[]")){
            JSONArray obj = JSON.parseArray(leirong);
//            JSONObject obj =
            if(obj.getJSONObject(0).get("success").toString().equals("true")) {
//                Map maps = (Map) JSON.parse(obj.getJSONObject(0).get("data").toString());
                model.addAttribute("list", list);
                model.addAttribute("chaxun", obj.getJSONObject(0).get("data").toString());

                double width = Double.parseDouble("96") / Double.parseDouble(String.valueOf(list.size()));
                String columns = "[{ xtype: 'rownumberer', text: '序号', width: 50, align: 'center' }";
                for (int i = 0; i < list.size(); i++) {
                    columns += ",{ text: '" + list.get(i) + "', dataIndex: '" + list.get(i) + "', width: " + widthMap.get(list.get(i)) + ", sortable: true, align: 'center', filter: { type: 'string' } }";
                }
                columns += " ]";
                JSONArray arr = JSONObject.parseArray(columns);
                model.addAttribute("columns", arr);
                System.out.println(columns);
            }
            else {
                JSONArray arr = JSONObject.parseArray("[]");
                model.addAttribute("list","[]");
                model.addAttribute("chaxun","[]");
                model.addAttribute("columns" ,arr);
                msg = obj.getJSONObject(0).get("msg").toString();
            }
        }else {
            JSONArray arr = JSONObject.parseArray("[]");
            model.addAttribute("list","[]");
            model.addAttribute("chaxun","[]");
            model.addAttribute("columns" ,arr);
        }
        model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
        model.addAttribute("systemName", sysConfig.getAppFullName());
        model.addAttribute("msg",msg);
        return "/ZiYuanZhongXin/BaseLeftSource";
    }

    //数据库别名页面
    @RequestMapping("/ResManage/ResRegister/ZiDuanBieMingBasic")
    public String ZiDuanBieMingBasic(Model model,Res_ExtDataSource res_extDataSource,String tablename,Integer resMainInfoId,Integer bianji,String primarykey){
        //根据数据源id，查询到账号密码
        model.addAttribute("datasourceid",res_extDataSource.getDatasourceid());
        model.addAttribute("tablename",tablename);
        //fieldconfig是 字段配置文本框里面的内容
        model.addAttribute("resMainInfoId",resMainInfoId);
        //如果bianji ==null 那么 bianji ==0()
        if(bianji==null){
            bianji=0;
        }
        model.addAttribute("bianji",bianji);
        model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
        model.addAttribute("systemName", sysConfig.getAppFullName());
        model.addAttribute("primarykey", primarykey);
        return "/ZiYuanZhongXin/ZiDuanBieMingBasic";
    }

    /**
     * wq:查询关联字典内容
     * @param key
     * @return
     */
    @ResponseBody
    @RequestMapping("/ResManage/ResRegister/selectDictionry")
    public LinkedHashMap<String, String> selectDictionry(String key){
    	LinkedHashMap<String, String> MapTypeList = FieldUtils.getFieldListByKey(key);
    	return MapTypeList;
    }
    /**
     * wq:字段映射配置跳转
     * @param model
     * @param datasourceid
     * @param resourceid
     * @param tablename
     * @return
     */
    @RequestMapping("/ResManage/ResRegister/fieldMapping")
    public String fieldMapping(Model model,String datasourceid,String resourceid,String tablename){
    	model.addAttribute("resourceid", resourceid);
    	model.addAttribute("datasourceid", datasourceid);
    	model.addAttribute("tablename", tablename);
    	model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
    	return "/ZiYuanZhongXin/ZiDuanYingSheBasic";
    }


    /**
     * wq:字段映射配置
     * @param model
     * @param res_Subscriptions
     * @return
     */
    @RequestMapping("/ResManage/ResRegister/fieldMapping_Edit")
    public String fieldMapping_Edit(Model model,Res_Subscriptions res_Subscriptions){
    	List<ZiDuanPeiZi> fieldMappings = new ArrayList<ZiDuanPeiZi>(); //数据源表字段集合
    	List<FieldMapping> fieldlist = new ArrayList<FieldMapping>(); 
    	List<String> fieldNames = new ArrayList<String>(); 
    	
    	//数据源已配置暴露字段
    	DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(res_Subscriptions.getResourceid());
    	model.addAttribute("primarykeys", database.getPrimarykey());
    	//数据源表类型(1为属性,2为空间)
    	model.addAttribute("tabletype", database.getTabletype());
    	model.addAttribute("resourceid", res_Subscriptions.getResourceid());
    	if(database!=null && database.getFieldconfig()!=null && !database.getFieldconfig().equals("")){
			JSONObject json = JSONObject.parseObject(database.getFieldconfig());
			JSONArray jsonArray = json.getJSONArray("sql");
			for(int i=0;i<jsonArray.size();i++){
				ZiDuanPeiZi fieldMapping =JSONUtil.toBean(String.valueOf(jsonArray.get(i)), ZiDuanPeiZi.class);
				if(database.getTabletype()==1){	//属性表
					fieldMappings.add(fieldMapping);					
				}else{	//空间表
					if(!fieldMapping.getZiDuanName().contains("$") && !fieldMapping.getZiDuanName().equals("SHAPE") && !fieldMapping.getZiDuanName().equals("OBJECTID")){
						fieldMappings.add(fieldMapping);
					}
				}
			}
    	}

    	//订阅表已配置映射字段
    	Res_Subscriptions resSubscriptions = resSubscriptionsService.selectSubscriptions(res_Subscriptions);
    	if(resSubscriptions!=null && resSubscriptions.getTablefields()!=null && !"".equals(resSubscriptions.getTablefields())){
    		JSONObject json = JSONObject.parseObject(resSubscriptions.getTablefields());
			JSONArray jsonArray = json.getJSONArray("fieldMappings");
			for(int i=0;i<jsonArray.size();i++){
				FieldMapping fieldMapping =JSONUtil.toBean(String.valueOf(jsonArray.get(i)), FieldMapping.class);
				fieldMapping.setDataSourceList(fieldMappings);
				fieldlist.add(fieldMapping);
				fieldNames.add(fieldMapping.getFieldName());
			}
    	}
    	
    	//目标表数据库
    	Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_Subscriptions.getDatasourceid());
    	//目标表字段集合
    	List<FieldMapping> fields = selectTargetSourceByDataSourceId(database.getTabletype(),res_Subscriptions.getTablename(),resExtDataSource);
    	if(!fields.isEmpty() && fields.size()>0){
    		for(FieldMapping field :fields){
        		if(!fieldNames.contains(field.getFieldName()) && field.getIsNullAble().equals("0")){
        			field.setDataSourceList(fieldMappings);
        			fieldlist.add(field);    			
        		}
        	}
    		for(FieldMapping field :fields){
        		if(!fieldNames.contains(field.getFieldName()) && field.getIsNullAble().equals("1")){
        			field.setDataSourceList(fieldMappings);
        			fieldlist.add(field);    			
        		}
        	}
    		model.addAttribute("isnew", 0);
    	}else{
    		//目标表不存在
    		for(ZiDuanPeiZi field : fieldMappings){
    			FieldMapping mapping = new FieldMapping();
    			mapping.setFieldName(field.getZiDuanName());
    			mapping.setFieldType(field.getZiDuanType());
    			mapping.setDataSourceList(fieldMappings);
    			mapping.setFieldMappingType(field.getZiDuanType());
    			String[] keys = database.getPrimarykey().split(",");
    			if(Arrays.asList(keys).contains(field.getZiDuanName())){
    				mapping.setIsPrimaryKey(1);	
    			}
    			fieldlist.add(mapping);
    		}
    		model.addAttribute("isnew", 1);
    	}
    	model.addAttribute("fieldlist", fieldlist);
    	return "/ZiYuanZhongXin/ZiDuanYingShe";
    }
    
    /**
     * wq:查询目标表字段集合
     * @param tablename
     * @param datasourceid
     * @return
     */
	private List<FieldMapping> selectTargetSourceByDataSourceId(Integer tabletype,String tablename,Res_ExtDataSource resExtDataSource) {
		
		List<FieldMapping> flist = new ArrayList<FieldMapping>();
		
		if(resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")){
			String sql = "SELECT D.column_name AS fieldName,D.data_type AS fieldType,B.is_nullable AS isNullAble FROM SYS.tables A INNER JOIN SYS.columns B ON B.object_id = A.object_id LEFT JOIN SYS.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id LEFT JOIN(SELECT table_name,column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS)D ON D.column_name = B.name and D.TABLE_NAME = A.name  WHERE A.name='"+tablename+"'";
			String json=JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),sql);
			JSONArray jsonArray = JSONObject.parseArray(json);
			JSONArray jsonarr = null;
			if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
				jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
				for(int i =0;i<jsonarr.size();i++){
					FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
					if(fieldMapping.getIsNullAble().equals("false")){
						fieldMapping.setIsNullAble("0");
					}else{
						fieldMapping.setIsNullAble("1");
					}
					if(tabletype==1){
						flist.add(fieldMapping);						
					}else{
						if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") && 
									!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
							flist.add(fieldMapping);		
						}
					}
				}
			}
		}
		
		if(resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")){
			String sql = "select distinct(t.column_name) as "+"\"fieldName\",t.data_type as "+"\"fieldType\",t.NULLABLE as "+"\"isNullAble\" from all_tab_cols t where t.table_name='"+tablename+"'";
			String json=JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sql);
			JSONArray jsonArray = JSONObject.parseArray(json);
			JSONArray jsonarr = null;
			if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
				jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
				for(int i =0;i<jsonarr.size();i++){
					FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
					if(fieldMapping.getIsNullAble().equals("N")){
						fieldMapping.setIsNullAble("0");
					}else{
						fieldMapping.setIsNullAble("1");
					}
					if(tabletype==1){
						flist.add(fieldMapping);						
					}else{
						if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") && 
									!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
							flist.add(fieldMapping);		
						}
					}
				}
			}
		}
		
		if(resExtDataSource.getDatabasetype().equalsIgnoreCase("MySQL")){
			String sql ="SELECT COLUMN_NAME AS fieldName,DATA_TYPE AS fieldType FROM information_schema.COLUMNS where table_name='"+tablename+"'";
			String json=JDBCUtils.MysqlConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sql);
			JSONArray jsonArray = JSONObject.parseArray(json);
			JSONArray jsonarr = null;
			if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
				jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
				for(int i =0;i<jsonarr.size();i++){
					FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
					if(tabletype==1){
						flist.add(fieldMapping);						
					}else{
						if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") && 
									!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
							flist.add(fieldMapping);		
						}
					}
				}
			}
		}
		return flist;
	}
	
	/**
	 * @param tablename
	 * @param resMainInfoId
	 * @return
	 */
	@ResponseBody
	@RequestMapping("/ResManage/ResRegister/changeFieldConfig")
    public String changeFieldConfig(String tablename,Integer resMainInfoId){
		String FieldConfig ="";
		Res_ExtDataBase res_extDataBase=resExtDataBaseService.selectByMainInfoId(resMainInfoId);
		if(res_extDataBase!=null && res_extDataBase.getTablename().equals(tablename)){
			FieldConfig = res_extDataBase.getFieldconfig();
		}
		return FieldConfig;
	}
    
	
	/**
	 * 判断字段是长整还是短整
	 * @param resourceid
	 * @param columnName
	 * @return
	 */
	@ResponseBody
	@RequestMapping("/ResManage/ResRegister/selectColumnType")
    public String selectColumnType(Integer resourceid,String columnName){
		//数据源
        DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(resourceid);
        String type = "short";
        if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
    		String sql = "select data_precision data_scale from all_tab_columns where table_name='"+database.getTablename()+"' and COLUMN_NAME='"+columnName+"'";
			String json=JDBCUtils.OracleConnUtils(database.getServeraddress(),database.getDatabasename(),database.getUsername(),database.getPassword(),database.getPort(),sql);
			JSONArray jsonArray = JSONObject.parseArray(json);
			JSONArray jsonarr = null;
			if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
				jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
				String length = jsonarr.getJSONObject(0).get("DATA_SCALE").toString();
				type = Integer.parseInt(length)>5?"long":"short";
			}
    	}
    	
    	if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
    		String sql = "select a.length as ZiDuanKuanDu from syscolumns a ,systypes b where a.xtype=b.xtype and a.id=(select id from sysobjects where name='"+database.getTablename()+"') and a.name='"+columnName+"'";
			String json=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),database.getUsername(),database.getPassword(),sql);
			JSONArray jsonArray = JSONObject.parseArray(json);
			JSONArray jsonarr = null;
			if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
				jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
				String length = jsonarr.getJSONObject(0).get("ZiDuanKuanDu").toString();
				type = Integer.parseInt(length)>5?"long":"short";
			}
		}
    	
    	if(database.getDatabasetype().equalsIgnoreCase("MySQL")){
    		String sql = "select character_maximum_length as ZiDuanKuanDu from information_schema.columns where table_name='"+database.getTablename()+"') and column_name='"+columnName+"'";
			String json=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),database.getUsername(),database.getPassword(),sql);
			JSONArray jsonArray = JSONObject.parseArray(json);
			JSONArray jsonarr = null;
			if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
				jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
				String length = jsonarr.getJSONObject(0).get("ZiDuanKuanDu").toString();
				if(length!=null && !length.equals("")){
					type = Integer.parseInt(length)>5?"long":"short";					
				}
			}
		}
		return type;
	}
	
    @RequestMapping("/ResManage/ResRegister/ZiDuanBieMingBasic_Edit")
    public String ZiDuanBieMingBasic_Edit(Model model,Res_ExtDataSource res_extDataSource,String tablename,String primarykey
           ,Integer resMainInfoId,Integer bianji){
        //bianji==1 表示之前有内容，我点了编辑按钮，然后不查询resMainInfoId关联的字符串内容
        //第二次打开时，加载之前的配置信息。要做一个判断
    	model.addAttribute("primarykey",primarykey);
        Res_ExtDataBase res_extDataBase=resExtDataBaseService.selectByMainInfoId(resMainInfoId);
        String FileldConfig="";
        JSONArray jsonArray = null;
        List list=new ArrayList();
        if(res_extDataBase!=null){
            if(!res_extDataBase.getFieldconfig().equals("") && res_extDataBase.getTablename().equals(tablename)){
                FileldConfig=res_extDataBase.getFieldconfig();
                JSONObject json;
                json = JSONObject.parseObject(FileldConfig);
                jsonArray= JSONArray.parseArray(json.getString("sql"));
                //获取配置字段
                for (int i = 0; i < jsonArray.size(); i++) {
                    ZiDuanPeiZi ziDuanPeiZi=JSONUtil.toBean(jsonArray.get(i).toString(), ZiDuanPeiZi.class);
                    if(ziDuanPeiZi.getZiDuanType().equals("int") || ziDuanPeiZi.getZiDuanType().equals("tinyint")){
                        String js="{\"ZiDuanName\": \""+ziDuanPeiZi.getZiDuanName()+"\"ZiDuanKuanDu\": \""+ziDuanPeiZi.getZiDuanKuanDu()+"\",\"ZiDuanType\": \"整数\",\"ZiDuanBieMing\": \""+ziDuanPeiZi.getZiDuanBieMing()+"\",\"ZiDuanGeShi\": {"+ziDuanPeiZi.getZiDuanGeShi().toJSONString()+"},\"Order\": "+ziDuanPeiZi.getOrder()+"}";
                        jsonArray.set(i,JSONObject.parseObject(js));
                    }
                    if(ziDuanPeiZi.getZiDuanType().equals("nvarchar") || ziDuanPeiZi.getZiDuanType().equals("varchar") ||ziDuanPeiZi.getZiDuanType().equals("char")){
                        String js="{\"ZiDuanName\": \""+ziDuanPeiZi.getZiDuanName()+"\"ZiDuanKuanDu\": \""+ziDuanPeiZi.getZiDuanKuanDu()+"\",\"ZiDuanType\": \"字符串\",\"ZiDuanBieMing\": \""+ziDuanPeiZi.getZiDuanBieMing()+"\",\"ZiDuanGeShi\": {"+ziDuanPeiZi.getZiDuanGeShi().toJSONString()+"},\"Order\": "+ziDuanPeiZi.getOrder()+"}";
                        jsonArray.set(i,JSONObject.parseObject(js));
                    }
                    if(ziDuanPeiZi.getZiDuanType().equals("datetime")){
                        String js="{\"ZiDuanName\": \""+ziDuanPeiZi.getZiDuanName()+"\"ZiDuanKuanDu\": \""+ziDuanPeiZi.getZiDuanKuanDu()+"\",\"ZiDuanType\": \"时间\",\"ZiDuanBieMing\": \""+ziDuanPeiZi.getZiDuanBieMing()+"\",\"ZiDuanGeShi\": {"+ziDuanPeiZi.getZiDuanGeShi().toJSONString()+"},\"Order\": "+ziDuanPeiZi.getOrder()+"}";
                        jsonArray.set(i,JSONObject.parseObject(js));
                    }
                    if(ziDuanPeiZi.getZiDuanType().equals("decimal")){
                        String js="{\"ZiDuanName\": \""+ziDuanPeiZi.getZiDuanName()+"\"ZiDuanKuanDu\": \""+ziDuanPeiZi.getZiDuanKuanDu()+"\",\"ZiDuanType\": \"小数\",\"ZiDuanBieMing\": \""+ziDuanPeiZi.getZiDuanBieMing()+"\",\"ZiDuanGeShi\": {"+ziDuanPeiZi.getZiDuanGeShi().toJSONString()+"},\"Order\": "+ziDuanPeiZi.getOrder()+"}";
                        jsonArray.set(i,JSONObject.parseObject(js));
                    }
                    String ZiDuan[]=new String[]{ziDuanPeiZi.getZiDuanName(),ziDuanPeiZi.getZiDuanBieMing(),ziDuanPeiZi.getZiDuanGeShi().toString(),ziDuanPeiZi.getZiDuanKuanDu()};
                    list.add(ZiDuan);
                }
            }
        }
        if(bianji==1){
        	//根据数据源id，查询到账号密码
            Res_ExtDataSource resExtDataSource=resExtDataSourceService.selectByPrimaryKey(res_extDataSource.getDatasourceid());
            //开始拼接sql,然后判断是哪个数据库
            String leirong="0";
            JSONArray obj = null;
            JSONArray ob = null;
            JSONArray jsonarr = new JSONArray();
            if(resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")){
//                String sql="SELECT NAME FROM SYSCOLUMNS WHERE ID=OBJECT_ID('"+tablename+"')";
                String sql="SELECT a.name NAME,b.name type,cast(g.[value] as varchar(500)) [remarks] FROM  syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties' left join  sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id where d.name='"+tablename+"' order by a.name";
                leirong=JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),sql);
                obj = JSON.parseArray(leirong);//获取用户信息
                if(obj.getJSONObject(0).get("success").toString().equals("true")) {
                    ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
                    //选择过的资源
                    int index = -1;
                    for (int j = 0; j < list.size(); j++) {
	                    for (int i = 0; i < ob.size(); i++) {
	                        Integer k = 0;
	                        DateType dateType = JSONUtil.toBean(ob.get(i).toString(), DateType.class);
	                        if (dateType.getRemarks() == null) {
	                            dateType.setRemarks("");
	                        }
	                        String js = "";
	                        String[] listDate = null;
	                        //增加一个是否显示要勾选的字段
                            if (dateType.getNAME().equals(((String[]) list.get(j))[0])) {
                                listDate = (String[]) list.get(j);
                                k = 1;
                            }
	                        if(k==1){
	                        	index++;
	                        	js = IsZiDuanXuanZe(k, dateType, js, listDate);
	                        	jsonarr.set(index, JSONObject.parseObject(js));                    	
	                        }
	                    }
                    }
                    for (int m = 0; m < ob.size(); m++) {
                        Integer k = 0;
                        DateType dateType = JSONUtil.toBean(ob.get(m).toString(), DateType.class);
                        if (dateType.getRemarks() == null) {
                            dateType.setRemarks("");
                        }
                        String js = "";
                        String[] listDate = null;
                        for (int n = 0; n < list.size(); n++) {
                            //增加一个是否显示要勾选的字段
                            if (dateType.getNAME().equals(((String[]) list.get(n))[0])) {
                                listDate = (String[]) list.get(n);
                                k = 1;
                            }
                        }
                        if(k!=1){
                        	index++;
                        	js = IsZiDuanXuanZe(k, dateType, js, listDate);
                        	jsonarr.set(index, JSONObject.parseObject(js));                    	
                        }
                    }
                }
            }
            if(resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")){
//                String sql="SELECT USER_TAB_COLS.COLUMN_NAME as name , USER_TAB_COLS.DATA_TYPE as \"type\",user_col_comments.comments as \"remarks\" FROM USER_TAB_COLS inner join user_col_comments on user_col_comments.TABLE_NAME=USER_TAB_COLS.TABLE_NAME and user_col_comments.COLUMN_NAME=USER_TAB_COLS.COLUMN_NAME where USER_TAB_COLS.TABLE_NAME='"+tablename+"'";
                String sql ="select distinct(a.column_name) as name,a.comments as \"remarks\",b.data_type as \"type\" from user_tab_columns b,user_col_comments a where a.table_name=b.table_name and a.column_name=b.column_name and a.table_name='"+tablename+"'";
                System.out.println(sql);
                leirong=JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sql);
                obj = JSON.parseArray(leirong);//获取用户信息
                if(obj.getJSONObject(0).get("success").toString().equals("true")) {
                    ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
                    //选择过的资源
                    int index = -1;
                    for (int j = 0; j < list.size(); j++) {
	                    for (int i = 0; i < ob.size(); i++) {
	                        Integer k = 0;
	                        DateType dateType = JSONUtil.toBean(ob.get(i).toString(), DateType.class);
	                        if (dateType.getRemarks() == null) {
	                            dateType.setRemarks("");
	                        }
	                        String js = "";
	                        String[] listDate = null;
	                        //增加一个是否显示要勾选的字段
                            if (dateType.getNAME().equals(((String[]) list.get(j))[0])) {
                                listDate = (String[]) list.get(j);
                                k = 1;
                            }
	                        if(k==1){
	                        	index++;
	                        	js = IsZiDuanXuanZe(k, dateType, js, listDate);
	                        	jsonarr.set(index, JSONObject.parseObject(js));                    	
	                        }
	                    }
                    }
                    for (int m = 0; m < ob.size(); m++) {
                        Integer k = 0;
                        DateType dateType = JSONUtil.toBean(ob.get(m).toString(), DateType.class);
                        if (dateType.getRemarks() == null) {
                            dateType.setRemarks("");
                        }
                        String js = "";
                        String[] listDate = null;
                        for (int n = 0; n < list.size(); n++) {
                            //增加一个是否显示要勾选的字段
                            if (dateType.getNAME().equals(((String[]) list.get(n))[0])) {
                                listDate = (String[]) list.get(n);
                                k = 1;
                            }
                        }
                        if(k!=1){
                        	index++;
                        	js = IsZiDuanXuanZe(k, dateType, js, listDate);
                        	jsonarr.set(index, JSONObject.parseObject(js));                    	
                        }
                    }
                }
            }
            model.addAttribute("list",jsonarr);
            System.out.println(jsonarr);
        }
        
        if(bianji==null){
            bianji=0;
        }
        if(bianji==1){
            FileldConfig=null;
            jsonArray = null;

        }
        
        model.addAttribute("jsonArray",jsonArray);
        model.addAttribute("FileldConfig",FileldConfig);
        model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
        model.addAttribute("systemName", sysConfig.getAppFullName());
        return "/ZiYuanZhongXin/ZiDuanBieMingBasic_Edit";
    }

    //sqlserver 数据替换
    private String IsZiDuanXuanZe(Integer k, DateType dateType, String js, String[] listDate) {
        if(k == 1){
            if(listDate[2].equals("")){
                listDate[2]="{}";
            }
            else if(dateType.getType().equalsIgnoreCase("long") || dateType.getType().equalsIgnoreCase("int") || dateType.getType().equalsIgnoreCase("number") || dateType.getType().equalsIgnoreCase("tinyint")){
                    js="{\"type\": \"整数\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
                }
            else if(dateType.getType().equalsIgnoreCase("varchar2") || dateType.getType().equalsIgnoreCase("nvarchar2") || dateType.getType().equalsIgnoreCase("nvarchar") || dateType.getType().equalsIgnoreCase("varchar") ||dateType.getType().equalsIgnoreCase("char")){
                    js="{\"type\": \"字符串\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
                }
            else if(dateType.getType().equalsIgnoreCase("date") || dateType.getType().equalsIgnoreCase("datetime") || dateType.getType().equalsIgnoreCase("TIMESTAMP")){
                    js="{\"type\": \"时间\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
                }
            else if(dateType.getType().equalsIgnoreCase("decimal") || dateType.getType().equalsIgnoreCase("float") || dateType.getType().equalsIgnoreCase("double")){
                    js="{\"type\": \"小数\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
                }
            else if(dateType.getType().equalsIgnoreCase("nclob") || dateType.getType().equalsIgnoreCase("blob") || dateType.getType().equalsIgnoreCase("binary") || dateType.getType().equalsIgnoreCase("bit") || dateType.getType().equalsIgnoreCase("tinyblob") || dateType.getType().equalsIgnoreCase("varbinary") || dateType.getType().equalsIgnoreCase("image")){
                js="{\"type\": \"二进制对象\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
            }
            else {
                js="{\"type\": \"字符串\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
            }
        }
        else{
                if(dateType.getType().equalsIgnoreCase("number") || dateType.getType().equalsIgnoreCase("long") || dateType.getType().equalsIgnoreCase("int") || dateType.getType().equalsIgnoreCase("tinyint")){
                    js="{\"type\": \"整数\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
                }
                else    if(dateType.getType().equalsIgnoreCase("varchar") || dateType.getType().equalsIgnoreCase("varchar2") || dateType.getType().equalsIgnoreCase("nvarchar") || dateType.getType().equalsIgnoreCase("varchar") ||dateType.getType().equalsIgnoreCase("char") || dateType.getType().equalsIgnoreCase("ntext")){
                    js="{\"type\": \"字符串\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
                }
                else if(dateType.getType().equalsIgnoreCase("datetime") || dateType.getType().equalsIgnoreCase("date") || dateType.getType().equalsIgnoreCase("TIMESTAMP")){
                    js="{\"type\": \"时间\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
                }
                else if(dateType.getType().equalsIgnoreCase("decimal") || dateType.getType().equalsIgnoreCase("float") || dateType.getType().equalsIgnoreCase("double")){
                    js="{\"type\": \"小数\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\":\"{}\"}";
                }else if(dateType.getType().equalsIgnoreCase("nclob") || dateType.getType().equalsIgnoreCase("blob") || dateType.getType().equalsIgnoreCase("binary") || dateType.getType().equalsIgnoreCase("bit") || dateType.getType().equalsIgnoreCase("tinyblob") || dateType.getType().equalsIgnoreCase("varbinary") || dateType.getType().equalsIgnoreCase("image")){
                	js="{\"type\": \"二进制对象\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
                }else {
                    js="{\"type\": \"字符串\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";

                }
        }
        return js;
    }

    //oracle 数据替换
    private String IsOracleZiDuanXuanZe(Integer k, DateType dateType, String js, String[] listDate) {
        if(k == 1){
            if(listDate[2].equals("")){
                listDate[2]="{}";
            }
            else if(dateType.getType().equals("NUMBER") || dateType.getType().equals("INTEGER") ||dateType.getType().equals("FLOAT") ){
                js="{\"type\": \"整数\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";

            }
            else if(dateType.getType().equals("NVARCHAR2") || dateType.getType().equals("VARCHAR2") ||dateType.getType().equals("CHAR") || dateType.getType().equals("NCHAR")){
                js="{\"type\": \"字符串\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";

            }
            else if(dateType.getType().equals("DATE") || dateType.getType().equals("TIMESTAMP")){
                js="{\"type\": \"时间\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";

            }
            else if(dateType.getType().equals("DECIMAL")){
                js="{\"type\": \"小数\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";

            }else{
                js="{\"type\": \"字符串\",\"remarks\": \""+listDate[1]+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \""+listDate[3]+"\",\"ZiDuanXuanZe\": \"1\",\"ZiDuanGeShi\": "+listDate[2]+"}";
            }

        }
        else{
            if(dateType.getType().equals("NUMBER") || dateType.getType().equals("INTEGER") ||dateType.getType().equals("FLOAT") ){
                js="{\"type\": \"整数\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";

            }
            else if(dateType.getType().equals("NVARCHAR2") || dateType.getType().equals("VARCHAR2") ||dateType.getType().equals("CHAR") || dateType.getType().equals("NCHAR")){
                js="{\"type\": \"字符串\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";

            }
            else if(dateType.getType().equals("DATE") || dateType.getType().equals("TIMESTAMP")){
                js="{\"type\": \"时间\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";

            }
            else if(dateType.getType().equals("DECIMAL")){
                js="{\"type\": \"小数\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\":\"{}\"}";

            }else {
                js="{\"type\": \"字符串\",\"remarks\": \""+dateType.getRemarks()+"\",\"NAME\": \""+dateType.getNAME()+"\",\"ZiDuanKuanDu\": \"0\",\"ZiDuanXuanZe\": \"0\",\"ZiDuanGeShi\": \"{}\"}";
            }
        }
        return js;
    }

    @ResponseBody
    @RequestMapping("/ZiYuan/BaseLeftSourceShuJu")
    public String BaseLeftSourceShuJu(SelectShuJuKu selectShuJuKu, Model model, PageBean pageBean){
        DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(selectShuJuKu.getResourceid());
        String sqlConfig="";
        String sqlid="";
        String leirong="";
        JSONArray objList = null;
        LinkedHashMap<String, String> ZiDianMap=null;
        LinkedHashMap<String,JSONArray > SqlMap=null;
        if(database.getFieldconfig().equals("")){
            String sql="SELECT a.name NAME,b.name type,cast(g.[value] as varchar(500)) [remarks] FROM  syscolumns a left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties' left join  sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id where d.name='"+database.getTablename()+"' order by a.name";
            leirong=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),database.getUsername(),database.getPassword(),sql);
            objList = JSON.parseArray(leirong);//获取用户信息
            for (int i = 0; i < objList.size(); i++) {
                if(i>0){
                    sqlConfig+= ",";
                }
                DateType dateType=JSONUtil.toBean(objList.get(i).toString(), DateType.class);
                sqlConfig+=dateType.getNAME();
                sqlConfig+=" as \"";
                sqlConfig+=dateType.getRemarks();
                sqlConfig += "\"";
            }
        }else {
            //开始拼接sql,然后判断是哪个数据库
            //获取json
            JSONObject sqlObj = JSON.parseObject(database.getFieldconfig());
            JSONArray jsarr = sqlObj.getJSONArray("sql");
            sqlConfig="";

            //字典处理，记录配置的别名，和配置的字典名称，等查询到结果之后，处理字典
            ZiDianMap=new LinkedHashMap<>();
            //sql处理，记录配置sql的字段
            SqlMap=new LinkedHashMap<>();
            for (int i = 0; i < jsarr.size(); i++) {
                ZiDuanPeiZi ziDuanPeiZi=JSONUtil.toBean(jsarr.get(i).toString(), ZiDuanPeiZi.class);
                if(i>0){
                    sqlConfig+= ",";
                }
                //sqlserver分页排序 可以写字段名，但是oracl排序设置了别名要用别名排序
                if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
                    sqlid=((JSONObject) jsarr.get(0)).get("ZiDuanName").toString();
                }
                if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
                    sqlid=((JSONObject) jsarr.get(0)).get("ZiDuanBieMing").toString();
                }

                String Geshi=ziDuanPeiZi.getZiDuanGeShi().toJSONString();
                String ZiDuanMing=ziDuanPeiZi.getZiDuanName();
                if(ziDuanPeiZi.getZiDuanType().equals("时间") && Geshi.indexOf("riqi")>0 && Geshi.indexOf("1")>0 ){
                    if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
                        ZiDuanMing="CONVERT(varchar(12),"+ziDuanPeiZi.getZiDuanName()+",111 )";
                    }
                    if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
                        ZiDuanMing=" to_date('"+ziDuanPeiZi.getZiDuanName()+"','yyyy-mm-dd')";
                    }
                }
                if(ziDuanPeiZi.getZiDuanType().equals("时间") && Geshi.indexOf("riqi")>0 && Geshi.indexOf("3")>0 ){
                    if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
                        ZiDuanMing="DATENAME(YEAR,"+ziDuanPeiZi.getZiDuanName()+")+'年'+DATENAME(month,"+ziDuanPeiZi.getZiDuanName()+")+'月'+DATENAME(day,"+ziDuanPeiZi.getZiDuanName()+")+'日'";
                    }
                    if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
                        ZiDuanMing="to_char("+ziDuanPeiZi.getZiDuanName()+",'yyyy\"年\"MM\"月\"dd\"日\"')";
                    }
                }
                if(ziDuanPeiZi.getZiDuanType().equals("时间") && Geshi.indexOf("riqi")>0 && Geshi.indexOf("4")>0 ){
                    if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
                        ZiDuanMing="DATENAME(YEAR,"+ziDuanPeiZi.getZiDuanName()+")+'年'+DATENAME(month,"+ziDuanPeiZi.getZiDuanName()+")+'月'";
                    }
                    if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
                        ZiDuanMing="to_char("+ziDuanPeiZi.getZiDuanName()+",'yyyy\"年\"MM\"月\"')";
                    }
                }
                if(ziDuanPeiZi.getZiDuanType().equals("时间") && Geshi.indexOf("riqi")>0 && Geshi.indexOf("5")>0 ){
                    if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
                        ZiDuanMing="DATENAME(YEAR,"+ziDuanPeiZi.getZiDuanName()+")+'年'";
                    }
                    if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
                        ZiDuanMing="to_char(LASTEDITDATE,'yyyy\"年\"')";
                    }
                }
                if(ziDuanPeiZi.getZiDuanType().equals("小数") && Geshi.indexOf("xiaoshudian")>0){
                    //获取字段保留几位小数
                    String[] xiaoshu=Geshi.split(":");
                    //获取到保留几位小数
                    String num= xiaoshu[1].replace("}","").trim();
                    if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
                        ZiDuanMing="Convert(NUMERIC(18,"+num+"),"+ziDuanPeiZi.getZiDuanName()+")";
                    }
                }
                if(Geshi.indexOf("guanlianzidian")>0){
                    //获取字典数组
                    String[] ZiDian=Geshi.split(":");
                    //获取到关联的是哪个字典
                    String ZiDianBiaoShi= ZiDian[1].replace("}","").replace("\"","").replace("\"","").trim();
                    ZiDianMap.put(ziDuanPeiZi.getZiDuanBieMing(),ZiDianBiaoShi);

                }
                if(Geshi.indexOf("SQLchaxun")>0){
                    SqlMap.put(ziDuanPeiZi.getZiDuanBieMing(),JSON.parseObject(Geshi).getJSONArray("SQLchaxun"));
                }
                sqlConfig+=ZiDuanMing ;
                sqlConfig+=" as \"";
                sqlConfig+=ziDuanPeiZi.getZiDuanBieMing().replace("（","").replace("）","").trim();
                sqlConfig += "\"";
//            map.put(ziDuanPeiZi.getZiDuanName(),ziDuanPeiZi.getZiDuanBieMing());
            }
        }
        System.out.println(sqlConfig);
        String sql="select "+sqlConfig+" from  "+database.getTablename() ;
        //查询总数的sql
        String count ="select count(*) as zongshu from  "+database.getTablename();
         leirong = "[]";
        Integer totalCount=0;

        if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
            //判断查询有没有
            if(selectShuJuKu.getSelcetZiDuan().length()>0){
                if(selectShuJuKu.getSelcetZiDuanZhi().length()>0){
                    count="select count(*) as zongshu from  "+database.getTablename() +" where "+selectShuJuKu.getSelcetZiDuan() +" like '%"+selectShuJuKu.getSelcetZiDuanZhi()+"%'";
                }
            }
            if(selectShuJuKu.getSelcetZiDuan().length()>0){
                if(!selectShuJuKu.getPubdateBegin().equals("") || !selectShuJuKu.getPubdatefinish().equals("")){
                    count="select count(*) as zongshu from  "+database.getTablename() +" where ";
                    if(!selectShuJuKu.getPubdateBegin().equals("") && selectShuJuKu.getPubdatefinish().equals("")){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" >= CAST ( '"+selectShuJuKu.getPubdateBegin()+"' AS datetime )";
                    }
                    if(!selectShuJuKu.getPubdatefinish().equals("") && selectShuJuKu.getPubdateBegin().equals("")){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" <= CAST ( '"+selectShuJuKu.getPubdatefinish()+"' AS datetime )";
                    }
                    if(!selectShuJuKu.getPubdatefinish().equals("") && !selectShuJuKu.getPubdateBegin().equals("") ){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" >= CAST ( '"+selectShuJuKu.getPubdateBegin()+"' AS datetime )";
                        count+=" and ";
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" <= CAST ( '"+selectShuJuKu.getPubdatefinish()+"' AS datetime )";
                    }

                }
            }
            //判断是否是整数查询
            if(selectShuJuKu.getSelcetZiDuan().length()>0){
                if(selectShuJuKu.getIntNumBegin()!=null || selectShuJuKu.getIntNumfinish()!=null){
                    count="select count(*) as zongshu from  "+database.getTablename()+" where ";
                    if(selectShuJuKu.getIntNumBegin()!=null && selectShuJuKu.getIntNumfinish()==null){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" >= "+selectShuJuKu.getIntNumBegin();
                    }
                    if(selectShuJuKu.getIntNumfinish()!=null && selectShuJuKu.getIntNumBegin()==null){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" <= "+selectShuJuKu.getIntNumfinish();
                    }
                    if(selectShuJuKu.getIntNumfinish()!=null && selectShuJuKu.getIntNumBegin()!=null){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" >= "+selectShuJuKu.getIntNumBegin();
                        count+=" and ";
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" <= "+selectShuJuKu.getIntNumfinish();
                    }
                }
            }

            String zongshu=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),
                    database.getUsername(), database.getPassword(),count);
            JSONArray obj = JSON.parseArray(zongshu);
            if(obj.getJSONObject(0).get("success").toString().equals("true")) {
                JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
                Map maps = (Map) JSON.parse(ob.get(0).toString());
                totalCount = (int) maps.get("zongshu");
                pageBean.setLimit(20);
                PageBean pageData = new PageBean(pageBean.getPage(), pageBean.getLimit(), (int) maps.get("zongshu"));
                //拼接sql ，分页查询
                String fenye = "select top " + pageBean.getLimit() + " * from (select row_number() over(order by  (select 0))as rownumber," + sqlConfig + " from " + database.getTablename() + ") as o where rownumber>" + pageData.getStartIndex() + "";

                //判断查询有没有s
                if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
                    if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
                        fenye = "select top " + pageBean.getLimit() + " * from (select row_number() over(order by  (select 0))as rownumber," + sqlConfig + " from " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " like'%" + selectShuJuKu.getSelcetZiDuanZhi() + "%') as o where rownumber>" + pageData.getStartIndex() + "";
                    }
                }

                //判断 是否是时间字段
                if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
                    if (!selectShuJuKu.getPubdateBegin().equals("") || !selectShuJuKu.getPubdatefinish().equals("")) {
                        String tiaojian = "";
                        if (!selectShuJuKu.getPubdateBegin().equals("") && selectShuJuKu.getPubdatefinish().equals("")) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= CAST ( '" + selectShuJuKu.getPubdateBegin() + "' AS datetime )";
                        }
                        if (!selectShuJuKu.getPubdatefinish().equals("") && selectShuJuKu.getPubdateBegin().equals("")) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= CAST ( '" + selectShuJuKu.getPubdatefinish() + "' AS datetime )";
                        }
                        if (!selectShuJuKu.getPubdatefinish().equals("") && !selectShuJuKu.getPubdateBegin().equals("")) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= CAST ( '" + selectShuJuKu.getPubdateBegin() + "' AS datetime )";
                            tiaojian += " and ";
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= CAST ( '" + selectShuJuKu.getPubdatefinish() + "' AS datetime )";
                        }
                        fenye = "select top " + pageBean.getLimit() + " * from (select row_number() over(order by  (select 0))as rownumber,"
                                + sqlConfig + " from " + database.getTablename() + " where "
                                + tiaojian +
                                ") as o where rownumber>" + pageData.getStartIndex() + "";
                    }
                }
                //判断 是否是整数查询
                if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
                    if (selectShuJuKu.getIntNumBegin() != null || selectShuJuKu.getIntNumfinish() != null) {
                        String tiaojian = "";
                        if (selectShuJuKu.getIntNumBegin() != null && selectShuJuKu.getIntNumfinish() == null) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= " + selectShuJuKu.getIntNumBegin();
                        }
                        if (selectShuJuKu.getIntNumfinish() != null && selectShuJuKu.getIntNumBegin() == null) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= " + selectShuJuKu.getIntNumfinish();
                        }
                        if (selectShuJuKu.getIntNumfinish() != null && selectShuJuKu.getIntNumBegin() != null) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= " + selectShuJuKu.getIntNumBegin();
                            tiaojian += " and ";
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= " + selectShuJuKu.getIntNumfinish();
                        }
                        fenye = "select top " + pageBean.getLimit() + " * from (select row_number() over(order by  (select 0))as rownumber,"
                                + sqlConfig + " from " + database.getTablename() + " where "
                                + tiaojian +
                                ") as o where rownumber>" + pageData.getStartIndex() + "";

                    }
                }

                System.out.println(fenye);
                leirong = JDBCUtils.SqlServerConnUtils(database.getServeraddress(), database.getDatabasename(),
                        database.getUsername(), database.getPassword(), fenye);
                //处理关联字典字段
            }

        }

        if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
            //判断查询有没有
            if(selectShuJuKu.getSelcetZiDuan().length()>0){
                if(selectShuJuKu.getSelcetZiDuanZhi().length()>0){
                    count="select count(*) as zongshu from  "+database.getTablename() +" where "+selectShuJuKu.getSelcetZiDuan()  +" like '%"+selectShuJuKu.getSelcetZiDuanZhi()+"%'";
                }
            }
            if(selectShuJuKu.getSelcetZiDuan().length()>0){
                if(!selectShuJuKu.getPubdateBegin().equals("") || !selectShuJuKu.getPubdatefinish().equals("")){
                    count="select count(*) as zongshu from  "+database.getTablename() +" where ";
                    if(!selectShuJuKu.getPubdateBegin().equals("") && selectShuJuKu.getPubdatefinish().equals("")){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" >= to_date ( '"+selectShuJuKu.getPubdateBegin()+"','yyyy-mm-dd' )";
                    }
                    if(!selectShuJuKu.getPubdatefinish().equals("") && selectShuJuKu.getPubdateBegin().equals("")){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" <= to_date ( '"+selectShuJuKu.getPubdatefinish()+"' ,'yyyy-mm-dd' )";
                    }
                    if(!selectShuJuKu.getPubdatefinish().equals("") && !selectShuJuKu.getPubdateBegin().equals("") ){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" >= to_date ( '"+selectShuJuKu.getPubdateBegin()+"' ,'yyyy-mm-dd' )";
                        count+=" and ";
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" <= to_date ( '"+selectShuJuKu.getPubdatefinish()+"' ,'yyyy-mm-dd')";
                    }
                }
            }

            //判断是否是整数查询
            if(selectShuJuKu.getSelcetZiDuan().length()>0){
                if(selectShuJuKu.getIntNumBegin()!=null || selectShuJuKu.getIntNumfinish()!=null){
                    count="select count(*) as zongshu from  "+database.getTablename()+" where ";
                    if(selectShuJuKu.getIntNumBegin()!=null && selectShuJuKu.getIntNumfinish()==null){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" >= "+selectShuJuKu.getIntNumBegin();
                    }
                    if(selectShuJuKu.getIntNumfinish()!=null && selectShuJuKu.getIntNumBegin()==null){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" <= "+selectShuJuKu.getIntNumfinish();
                    }
                    if(selectShuJuKu.getIntNumfinish()!=null && selectShuJuKu.getIntNumBegin()!=null){
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" >= "+selectShuJuKu.getIntNumBegin();
                        count+=" and ";
                        count+=""+selectShuJuKu.getSelcetZiDuan()+" <= "+selectShuJuKu.getIntNumfinish();
                    }
                }
            }

            String zongshu=JDBCUtils.OracleConnUtils(database.getServeraddress(),database.getDatabasename(),
                    database.getUsername(), database.getPassword(),database.getPort(),count);
            JSONArray obj = JSON.parseArray(zongshu);
            if(obj.getJSONObject(0).get("success").toString().equals("true")) {
                JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
                Map maps = (Map) JSON.parse(ob.get(0).toString());
                totalCount = Integer.parseInt(maps.get("ZONGSHU").toString());
                pageBean.setLimit(20);
                PageBean pageData = new PageBean(pageBean.getPage(), pageBean.getLimit(), totalCount);
                //拼接sql ，分页查询
                //判断查询有没有
                if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
                    if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
                        sql = "select " + sqlConfig + " from  " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " like '%" + selectShuJuKu.getSelcetZiDuanZhi() + "%'";
                    }
                }
                //判断 是否是时间字段
                if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
                    if (!selectShuJuKu.getPubdateBegin().equals("") || !selectShuJuKu.getPubdatefinish().equals("")) {
                        String tiaojian = "";
                        if (!selectShuJuKu.getPubdateBegin().equals("") && selectShuJuKu.getPubdatefinish().equals("")) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= to_date ( '" + selectShuJuKu.getPubdateBegin() + "','yyyy-mm-dd' )";
                        }
                        if (!selectShuJuKu.getPubdatefinish().equals("") && selectShuJuKu.getPubdateBegin().equals("")) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= to_date ( '" + selectShuJuKu.getPubdatefinish() + "' ,'yyyy-mm-dd')";
                        }
                        if (!selectShuJuKu.getPubdatefinish().equals("") && !selectShuJuKu.getPubdateBegin().equals("")) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= to_date ( '" + selectShuJuKu.getPubdateBegin() + "','yyyy-mm-dd' )";
                            tiaojian += " and ";
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= to_date ( '" + selectShuJuKu.getPubdatefinish() + "' ,'yyyy-mm-dd')";
                        }
                        sql = "select " + sqlConfig + " from " + database.getTablename() + " where " + tiaojian;
                    }
                }
                //判断 是否是整数查询
                if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
                    if (selectShuJuKu.getIntNumBegin() != null || selectShuJuKu.getIntNumfinish() != null) {
                        String tiaojian = "";
                        if (selectShuJuKu.getIntNumBegin() != null && selectShuJuKu.getIntNumfinish() == null) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= " + selectShuJuKu.getIntNumBegin();
                        }
                        if (selectShuJuKu.getIntNumfinish() != null && selectShuJuKu.getIntNumBegin() == null) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= " + selectShuJuKu.getIntNumfinish();
                        }
                        if (selectShuJuKu.getIntNumfinish() != null && selectShuJuKu.getIntNumBegin() != null) {
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " >= " + selectShuJuKu.getIntNumBegin();
                            tiaojian += " and ";
                            tiaojian += "" + selectShuJuKu.getSelcetZiDuan() + " <= " + selectShuJuKu.getIntNumfinish();
                        }
                        sql = "select " + sqlConfig + " from " + database.getTablename() + " where " + tiaojian;

                    }
                }

                String fenye = "select * from(select * from(select t.*,row_number() over(order by " + sqlid + ") as rownumber from(" + sql + ") t) p where p.rownumber>" + pageData.getStartIndex() + ") where rownum<= " + pageBean.getLimit();
                System.out.println(fenye);
                leirong = JDBCUtils.OracleConnUtils(database.getServeraddress(), database.getDatabasename(), database.getUsername(), database.getPassword(), database.getPort(), fenye);
            }
        }

        JSONArray obj = JSON.parseArray(leirong);
        if(obj.getJSONObject(0).get("success").toString().equals("true")) {
            JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
            //循环获取 字典表
            if (ZiDianMap != null) {
                if (ZiDianMap.size() > 0) {
                    for (String s : ZiDianMap.keySet()) {
                        System.out.println("key : " + s + " value : " + ZiDianMap.get(s));
                        LinkedHashMap<String, String> MapTypeList = FieldUtils.getFieldListByKey(ZiDianMap.get(s));
                        for (int i = 0; i < ob.size(); i++) {
                            JSONObject job = ob.getJSONObject(i);
                            Map m = job;
                            //null值处理 alert ykm 2019/01/03
                            m.put(s, MapTypeList.get(m.get(s)==null?null : m.get(s).toString()));
                            ob.set(i, JSONUtil.parseFromMap(m));
                        }
                    }
                }
            }
            //循环 sqlmap 当有关联数据库表的时候才循环
            if (SqlMap != null) {
                if (SqlMap.size() > 0) {
                    for (String s : SqlMap.keySet()) {
                        System.out.println("key : " + s + " value : " + SqlMap.get(s));
                        SqlMapJson sqlMapJson = JSONUtil.toBean(SqlMap.get(s).get(0).toString(), SqlMapJson.class);
                        String sql1 = "select " + sqlMapJson.getSQLkey() + " as onlykey ," + sqlMapJson.getSQLtext() + " as text from " + sqlMapJson.getSQLtable();
                        String sqlMapList = JDBCUtils.SqlServerConnUtils(database.getServeraddress(), database.getDatabasename(),
                                database.getUsername(), database.getPassword(), sql1);
                        JSONArray sqlArray = JSON.parseArray(sqlMapList);
                        LinkedHashMap<String, String> MapTypeList = new LinkedHashMap<String, String>();
                        String dataStr = sqlArray.getJSONObject(0).get("data").toString();
                        JSONArray jsonarray = JSON.parseArray(dataStr);
                        for (int i = 0; i < jsonarray.size(); i++) {
                        	String onlykey = jsonarray.getJSONObject(i).get("onlykey").toString();
                        	String text = jsonarray.getJSONObject(i).get("text").toString();
                            MapTypeList.put(onlykey, text);
                        }
                        for (int i = 0; i < ob.size(); i++) {
                            JSONObject job = ob.getJSONObject(i);
                            Map m = job;
                            //null值处理 alert ykm 2019/01/03
                            m.put(s, MapTypeList.get(m.get(s) == null ? null : m.get(s).toString()));
                            ob.set(i, JSONUtil.parseFromMap(m));
                        }
                        System.out.println(JSON.parseArray(sqlMapList));
                    }
                }
            }
            StringBuilder rsb = new StringBuilder();
            rsb.append("{'totalCount':'" + totalCount);
            rsb.append("','topics':");
            rsb.append(ob);
            rsb.append("}");
            return rsb.toString();
        }
        else {
            StringBuilder rsb = new StringBuilder();
            rsb.append("{'totalCount':'" + totalCount);
            rsb.append("','topics':");
            rsb.append("[]");
            rsb.append("}");
            return rsb.toString();
        }

    }

    //导出excel
    @ResponseBody
    @RequestMapping("/ZiYuan/BaseLeftSourceShuJuExcel")
    public String BaseLeftSourceShuJuExcel(HttpServletResponse response, SelectShuJuKu selectShuJuKu, Model model, PageBean pageBean) {
        DataBaseLeftDataSource database = resExtDataBaseService.selectBaseLeftSource(selectShuJuKu.getResourceid());
        JSONObject sqlObj = JSON.parseObject(database.getFieldconfig());
        JSONArray jsarr = sqlObj.getJSONArray("sql");
        String sqlConfig = "";
        String sqlid = "";
        for (int i = 0; i < jsarr.size(); i++) {
            if (i > 0) {
                sqlConfig += ",";
            }
            sqlConfig += ((JSONObject) jsarr.get(i)).get("ZiDuanName").toString();
            sqlid = ((JSONObject) jsarr.get(0)).get("ZiDuanName").toString();
            sqlConfig += " as  \"";
            sqlConfig += ((JSONObject) jsarr.get(i)).get("ZiDuanBieMing").toString();
            sqlConfig += "\"";
        }
        System.out.println(sqlConfig);

        //开始拼接sql,然后判断是哪个数据库
        String sql = "select " + sqlConfig + " from  " + database.getTablename();
        //查询总数的sql
        String count = "select count(*) as zongshu from  " + database.getTablename();
        String leirong = "[]";
        Integer totalCount = 0;

        if (database.getDatabasetype().equalsIgnoreCase("SQLServer")) {
            //判断查询有没有
            if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
                if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
                    count = "select count(*) as zongshu from  " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " = '" + selectShuJuKu.getSelcetZiDuanZhi() + "'";
                }
            }
            String zongshu = JDBCUtils.SqlServerConnUtils(database.getServeraddress(), database.getDatabasename(),
                    database.getUsername(), database.getPassword(), count);
            JSONArray obj = JSON.parseArray(zongshu);
            if (obj.getJSONObject(0).get("success").toString().equals("true")) {
                JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
                Map maps = (Map) JSON.parse(ob.get(0).toString());
                totalCount = Integer.parseInt(maps.get("zongshu").toString());
                pageBean.setLimit(20);
                PageBean pageData = new PageBean(pageBean.getPage(), pageBean.getLimit(), Integer.parseInt(maps.get("zongshu").toString()));
                //拼接sql
                String fenye = "select " + sqlConfig + " from  " + database.getTablename();

                //判断查询有没有
                if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
                    if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
                        fenye = "select " + sqlConfig + " from  " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " like '%" + selectShuJuKu.getSelcetZiDuanZhi() + "'%";
                    }
                }
                System.out.println(fenye);

                leirong = JDBCUtils.SqlServerConnUtils(database.getServeraddress(), database.getDatabasename(),
                        database.getUsername(), database.getPassword(), fenye);
            }

        }

        if (database.getDatabasetype().equalsIgnoreCase("Oracle")) {
            //判断查询有没有
            if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
                if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
                    count = "select count(*) as zongshu from  " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " = '" + selectShuJuKu.getSelcetZiDuanZhi() + "'";
                }
            }

            String zongshu = JDBCUtils.OracleConnUtils(database.getServeraddress(), database.getDatabasename(),
                    database.getUsername(), database.getPassword(), database.getPort(), count);
            JSONArray obj = JSON.parseArray(zongshu);
            if (obj.getJSONObject(0).get("success").toString().equals("true")) {
                JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
                Map maps = (Map) JSON.parse(ob.get(0).toString());
                totalCount = Integer.parseInt(maps.get("ZONGSHU").toString());
                pageBean.setLimit(20);
                PageBean pageData = new PageBean(pageBean.getPage(), pageBean.getLimit(), Integer.parseInt(maps.get("ZONGSHU").toString()));
                //拼接sql ，分页查询
                //判断查询有没有
                if (selectShuJuKu.getSelcetZiDuan().length() > 0) {
                    if (selectShuJuKu.getSelcetZiDuanZhi().length() > 0) {
                        sql = "select " + sqlConfig + " from  " + database.getTablename() + " where " + selectShuJuKu.getSelcetZiDuan() + " = '" + selectShuJuKu.getSelcetZiDuanZhi() + "'";
                    }
                }
                String fenye = sql;

                System.out.println(fenye);
                leirong = JDBCUtils.OracleConnUtils(database.getServeraddress(), database.getDatabasename(), database.getUsername(), database.getPassword(), database.getPort(), fenye);
            }
        }

        JSONArray obj = JSON.parseArray(leirong);
        if (obj.getJSONObject(0).get("success").toString().equals("true")) {
            JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
            StringBuilder rsb = new StringBuilder();
            rsb.append("{'totalCount':'" + totalCount);
            rsb.append("','topics':");
            rsb.append(ob);
            rsb.append("}");
            Map maps = (Map) JSON.parse(ob.get(0).toString());
            String[] list = new String[maps.keySet().size()];
            for (int i = 0; i < maps.keySet().size(); i++) {
                list[i] = (String) maps.keySet().toArray()[i];
            }
            try {
                String Filename = Excel.ShuJuKugoing(response, list, rsb.toString(),
                        sysConfig.getUploadPath() + "excel\\");
                String path2 = "/uploadPath/excel/";
                String desFile = path2 + Filename;
                System.out.println(desFile);
                return desFile;
            } catch (IOException e) {
                e.printStackTrace();
                return "异常：" + e.getMessage();
            }
        }
        else {
            return "失败：" + obj.getJSONObject(0).get("msg").toString();
        }
    }


    //查询数据库 有哪些表
    @ResponseBody
    @RequestMapping("/ZiYuan/selectSqlTable")
    public String selectSqlTable(Res_ExtDataSource res_extDataSource) {
        //根据数据源id，查询到账号密码
        Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_extDataSource.getDatasourceid());
        //开始拼接sql,然后判断是哪个数据库
        String leirong = "0";
        if (resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")) {
            String sql = " select name as TABLENAME  from sysobjects where xtype='u' ORDER BY name";
            leirong = JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), sql);

        }
        if (resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")) {
            String sql = "SELECT table_name as tablename FROM user_tables ORDER BY tablename";
            leirong = JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), resExtDataSource.getPort(), sql);
        }
        JSONArray obj = JSON.parseArray(leirong);
        if(obj.getJSONObject(0).get("success").toString().equals("true")) {
            leirong = obj.getJSONObject(0).get("data").toString();
        }
        return leirong;
    }

    //查询数据库表主键
    @ResponseBody
    @RequestMapping("/ZiYuan/selectTableKey")
    public String selectTableKey(Res_ExtDataSource res_extDataSource,String tablename,Integer resMainInfoId){
        //根据数据源id，查询到账号密码
        Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_extDataSource.getDatasourceid());
        String leirong = "0";
        if (resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")) {
            String sql = "select b.column_name as ZHUJIAN from " +
                    "information_schema.table_constraints a inner join " +
                    "information_schema.constraint_column_usage b on a.constraint_name = b.constraint_name " +
                    "where a.constraint_type = 'PRIMARY KEY' and a.table_name = '"+tablename+"'";
            leirong = JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), sql);
        }
        if (resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")) {
            String sql = "select col.column_name as zhujian" +
                    " from user_constraints con,  user_cons_columns col " +
                    " where con.constraint_name = col.constraint_name " +
                    " and con.constraint_type='P' " +
                    " and col.table_name = '"+tablename+"'";
            leirong = JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), resExtDataSource.getPort(), sql);
        }
        if(!leirong.equals("0")) {
            JSONArray obj = JSON.parseArray(leirong);
            if(obj.getJSONObject(0).get("success").toString().equals("true")) {
                leirong = obj.getJSONObject(0).get("data").toString();
            }
        }
        return leirong;
    }
    
    @ResponseBody
    @RequestMapping("/ZiYuan/selectTabFileds")
    public List<String> selectTabFileds(Res_ExtDataSource res_extDataSource,Integer tabletype,String tablename){
    	
    	List<String> slist = new ArrayList<String>();
		
    	Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_extDataSource.getDatasourceid());
		if(resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")){
			String sql = "SELECT D.column_name AS fieldName,D.data_type AS fieldType,B.is_nullable AS isNullAble FROM SYS.tables A INNER JOIN SYS.columns B ON B.object_id = A.object_id LEFT JOIN SYS.extended_properties C ON C.major_id = B.object_id AND C.minor_id = B.column_id LEFT JOIN(SELECT table_name,column_name,data_type FROM INFORMATION_SCHEMA.COLUMNS)D ON D.column_name = B.name and D.TABLE_NAME = A.name  WHERE A.name='"+tablename+"'";
			String json=JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),sql);
			JSONArray jsonArray = JSONObject.parseArray(json);
			JSONArray jsonarr = null;
			if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
				jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
				for(int i =0;i<jsonarr.size();i++){
					FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
					if(tabletype==1){
						slist.add(fieldMapping.getFieldName());						
					}else{
						if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") && 
									!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
							slist.add(fieldMapping.getFieldName());		
						}
					}
				}
			}
		}
		
		if(resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")){
			String sql = "select distinct(t.column_name) as "+"\"fieldName\",t.data_type as "+"\"fieldType\",t.NULLABLE as "+"\"isNullAble\" from all_tab_cols t where t.table_name='"+tablename+"'";
			String json=JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sql);
			JSONArray jsonArray = JSONObject.parseArray(json);
			JSONArray jsonarr = null;
			if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
				jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
				for(int i =0;i<jsonarr.size();i++){
					FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
					if(tabletype==1){
						slist.add(fieldMapping.getFieldName());						
					}else{
						if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") && 
									!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
							slist.add(fieldMapping.getFieldName());		
						}
					}
				}
			}
		}
		
		if(resExtDataSource.getDatabasetype().equalsIgnoreCase("MySQL")){
			String sql ="SELECT COLUMN_NAME AS fieldName,DATA_TYPE AS fieldType FROM information_schema.COLUMNS where table_name='"+tablename+"'";
			String json=JDBCUtils.MysqlConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sql);
			JSONArray jsonArray = JSONObject.parseArray(json);
			JSONArray jsonarr = null;
			if(jsonArray.getJSONObject(0).get("success").toString().equals("true")) {
				jsonarr = JSON.parseArray(jsonArray.getJSONObject(0).get("data").toString());
				for(int i =0;i<jsonarr.size();i++){
					FieldMapping fieldMapping = JSONUtil.toBean(jsonarr.get(i).toString(), FieldMapping.class);
					if(tabletype==1){
						slist.add(fieldMapping.getFieldName());						
					}else{
						if(!fieldMapping.getFieldName().contains("$") && !fieldMapping.getFieldName().equalsIgnoreCase("SHAPE") && 
									!fieldMapping.getFieldName().equalsIgnoreCase("OBJECTID")){
							slist.add(fieldMapping.getFieldName());		
						}
					}
				}
			}
		}
		return slist;
    }
    /**
     * wq:查询表字段及类型
     * @param res_extDataSource
     * @param tablename
     * @param resMainInfoId
     * @return 时间格式的字段
     */
    @ResponseBody
    @RequestMapping("/ZiYuan/selectDataField")
    public List<String> selectDateField(Res_ExtDataSource res_extDataSource,String tablename,Integer resMainInfoId){
        //根据数据源id，查询到账号密码
        Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_extDataSource.getDatasourceid());
        String tableinfo ="";
        ArrayList<String> slist = new ArrayList<String>();
        if (resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")) {
        	String timestamp_sql ="select syscolumns.name as ZiDuanName,systypes.name as ZiDuanType from syscolumns,systypes where syscolumns.xusertype=systypes.xusertype and syscolumns.id=OBJECT_ID('"+tablename+"')";
            tableinfo = JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), timestamp_sql);}
        if (resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")) {
            String sql = "select t.column_name as "+"\"ZiDuanName\",t.data_type as "+"\"ZiDuanType\" from all_tab_cols t where t.table_name= '"+tablename+"'";
            tableinfo = JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), resExtDataSource.getPort(), sql);
        }
        if(!tableinfo.equals("")){
        	JSONArray json = JSON.parseArray(tableinfo);
        	if(json.getJSONObject(0).get("success").toString().equals("true")){
        		String str = json.getJSONObject(0).get("data").toString();
        		JSONArray jsonArray = JSONArray.parseArray(str);
        		for(int i=0;i<jsonArray.size();i++){
        			ZiDuanPeiZi bean = JSONUtil.toBean(jsonArray.get(i).toString(),ZiDuanPeiZi.class);        			
        			if(bean.getZiDuanType().equalsIgnoreCase("date") || bean.getZiDuanType().equalsIgnoreCase("datetime") || bean.getZiDuanType().equalsIgnoreCase("timestamp")){
        				slist.add(bean.getZiDuanName());
        			}
        		}
        	}
        }
        return slist;
    }

    //数据源管理
    @ResponseBody
    @RequestMapping("/ZiYuan/DataType")
    public String DataSource(Model model, String type){
        HashMap<String, String> DatabaseTypeList = FieldUtils.getFieldListByKey(type);// 获取数据库类型列表
        String ResourceTypeListJson = "";
        for (Map.Entry<String, String> entry : DatabaseTypeList.entrySet()) {
            if (ResourceTypeListJson != ""){
                ResourceTypeListJson += ",";
            }
            ResourceTypeListJson += "{key:'" + entry.getKey() + "',value:'" + entry.getValue() + "'}";
        }
        return  ResourceTypeListJson;
    }
    //根据sql查询-对应的属性
    @ResponseBody
    @RequestMapping("/ZiYuan/SQLSource")
    public String SQLSource(Model model, SqlMapJson sqlMapJson,Integer resourceid){
        DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(resourceid);
        String sql1="select "+sqlMapJson.getSQLkey()+" as onlykey ,"+sqlMapJson.getSQLtext()+" as text from "+sqlMapJson.getSQLtable();
        String sqlMapList=JDBCUtils.SqlServerConnUtils(database.getServeraddress(),database.getDatabasename(),
                database.getUsername(), database.getPassword(),sql1);
        JSONArray sqlArray=JSON.parseArray(sqlMapList);
        LinkedHashMap<String, String> MapTypeList =new LinkedHashMap<String,String>();
        for(int i = 0; i < sqlArray.size(); i++){
            MapTypeList.put(JSON.parseArray(sqlMapList).getJSONObject(i).get("onlykey").toString(),JSON.parseArray(sqlMapList).getJSONObject(i).get("text").toString());
        }

        String ResourceTypeListJson = "";
        for (Map.Entry<String, String> entry : MapTypeList.entrySet()) {
            if (ResourceTypeListJson != ""){
                ResourceTypeListJson += ",";
            }
            ResourceTypeListJson += "{key:'" + entry.getKey() + "',value:'" + entry.getValue() + "'}";
        }
        return  ResourceTypeListJson;
    }





}