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


import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

import org.apache.shiro.SecurityUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
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 org.springframework.web.servlet.ModelAndView;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.landtool.lanbase.common.annotation.LogAction;
import com.landtool.lanbase.common.utils.Result;
import com.landtool.lanbase.config.SysTemPropertyConfig;
import com.landtool.lanbase.modules.api.utils.JDBCUtils;
import com.landtool.lanbase.modules.api.utils.PageBean;
import com.landtool.lanbase.modules.log.service.LogActionService;
import com.landtool.lanbase.modules.org.entity.OrgUnit;
import com.landtool.lanbase.modules.org.entity.OrgUser;
import com.landtool.lanbase.modules.org.service.OrgUserService;
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.DataBaseLeftDataSource.DataBaseLeftDataSource;
import com.landtool.lanbase.modules.res.entity.JSONModels.FieldMapping;
import com.landtool.lanbase.modules.res.entity.JSONModels.ZiDuanPeiZi;
import com.landtool.lanbase.modules.res.entity.UserDefined.MainInfoJoinSubscriptions;
import com.landtool.lanbase.modules.res.service.ResApplyRecommendService;
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.core.date.DateUtil;
import cn.hutool.json.JSONUtil;

/**
 * 数据库表订阅
 */
@Configuration
@Controller
@RequestMapping("/res")
public class ResSubscriptionsController extends AbstractController {
    @Autowired
    private ResSubscriptionsService resSubscriptionsService;

    @Autowired
    private OrgUserService orgUserService;

    @Autowired
    private ResApplyRecommendService resApplyRecommendService;

    @Autowired
    public ResMainInfoService resMainInfoService;

    @Autowired
    private SysTemPropertyConfig sysConfig;

    @Autowired
    private ResExtDataSourceService resExtDataSourceService;

    @Autowired
    private ResExtDataBaseService resExtDataBaseService;

    @Autowired
    private SysFieldvalueService FieldUtils;

    @Autowired
    private LogActionService logActionService;

    /**
     * 新增资源操作记录信息
     */
    @ResponseBody
    @RequestMapping("/ressubscriptions/insert")
    public int insert(Res_Subscriptions record) {
        return resSubscriptionsService.insert(record);
    }

    @ResponseBody
    @RequestMapping("/ressubscriptions/insertSelectiveAndUpdate")
    public String insertSelectiveAndUpdate(Res_Subscriptions record,String condition) {
    	//============20190401 wangq--映射json多了\ 前端无法解析================
    	StringBuffer sbf = new StringBuffer();
    	sbf.append("{\"fieldMappings\":");
    	JSONObject obj = (JSONObject)JSONObject.parse(record.getTablefields());
    	sbf.append(obj.get("fieldMappings").toString());
    	sbf.append("}");
    	//===================
    	if(condition!=null && !condition.trim().equals("")){
    		condition = condition.replace(">", ">");
    		condition = condition.replace("&lt;", "<");
    		condition = condition.replace("&amp;ge;", ">=");
    		condition = condition.replace("&amp;le;", "<=");
    		record.setQuerywhere(condition);
    	}
        OrgUser user = getUser();
        // 获取登录人姓名和登录人id和信息资源id
        record.setAppuserid(getUserId().toString());
        String unitid = orgUserService.getDefaultUnit(user.getUserid()).getUnitid().toString();
        Long userid = user.getUserid();
        String chinesename = user.getChinesename();
        record.setAppunitid(unitid);
        record.setAppuserid(String.valueOf(userid));
        // 判断 id是否存在 存在就更新 不存在就 添加
        Res_Subscriptions resSubscriptions = resSubscriptionsService.selectByPrimaryKey(record.getAppid());
        // 订阅字段集选项
        //String tablefields = res_Subscriptions.get();
        //Res_Subscriptions subTablefields = resSubscriptionsService.selectByTablefields(tablefields);
        //判断资源ID
        int subscriptionsResult=0;
        boolean createTable =false;
        
        List<String> fieldNames = new ArrayList<String>(); //订阅表配置字段集合
    	if(record.getTablefields()!=null && !record.getTablefields().equals("")){
    		JSONObject json = JSONObject.parseObject(record.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);
				fieldNames.add(fieldMapping.getFieldName());
			}
    	}
    	
        //数据源
        DataBaseLeftDataSource database=resExtDataBaseService.selectBaseLeftSource(record.getResourceid());
        //数据库不存在该表&&表类型为属性表	 则创建
        if(record.getIsnew()==1 && database.getTabletype()==1 && resSubscriptions==null){ 
        	List<ZiDuanPeiZi> fieldMappings = new ArrayList<ZiDuanPeiZi>(); //数据源表字段集合
        	
        	if(database.getDatabasetype().equalsIgnoreCase("Oracle")){
	    		String sql = "select A.column_name as "+"\"ZiDuanName\", A.data_type as "+"\"ZiDuanType\", A.data_length as "+"\"ZiDuanKuanDu\" from user_tab_columns A where A.table_name='"+database.getTablename()+"'";
				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());
					for(int i =0;i<jsonarr.size();i++){
						ZiDuanPeiZi entity = JSONUtil.toBean(jsonarr.get(i).toString(), ZiDuanPeiZi.class);
						//if(!entity.getZiDuanName().equals(database.getPrimarykey())){
							if(fieldNames.contains(entity.getZiDuanName())){
								fieldMappings.add(entity);								
							}
						//}
					}
				}
	    	}
	    	
	    	if(database.getDatabasetype().equalsIgnoreCase("SQLServer")){
	    		String sql = "select a.name as ZiDuanName,a.length as ZiDuanKuanDu,b.name as ZiDuanType from syscolumns a ,systypes b where a.xtype=b.xtype and a.id=(select id from sysobjects where name='"+database.getTablename()+"')";
				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());
					for(int i =0;i<jsonarr.size();i++){
						ZiDuanPeiZi entity = JSONUtil.toBean(jsonarr.get(i).toString(), ZiDuanPeiZi.class);
						//if(!entity.getZiDuanName().equals(database.getPrimarykey())){
							if(!entity.getZiDuanType().equalsIgnoreCase("sysname")){
								if(fieldNames.contains(entity.getZiDuanName())){
									fieldMappings.add(entity);								
								}											
							}
						//}
					}
				}
			}
	    	
	    	if(database.getDatabasetype().equalsIgnoreCase("MySQL")){
	    		String sql = "select column_name as ZiDuanName, data_type as ZiDuanType,character_maximum_length as ZiDuanKuanDu from information_schema.columns where table_name='"+database.getTablename()+"')";
				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());
					for(int i =0;i<jsonarr.size();i++){
						ZiDuanPeiZi entity = JSONUtil.toBean(jsonarr.get(i).toString(), ZiDuanPeiZi.class);
						//if(!entity.getZiDuanName().equals(database.getPrimarykey())){
							if(fieldNames.contains(entity.getZiDuanName())){
								fieldMappings.add(entity);								
							}				
						//}
					}
				}
			}

	    	//目标表数据库
	    	Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(record.getDatasourceid());
	    	//拼接创建表sql
	    	StringBuffer sb=new StringBuffer("create table "+record.getTablename()+"(");
//	    	String[] primarykeys = database.getPrimarykey().split(",");
//	    	if(primarykeys.length==1){
//	    		sb.append(database.getPrimarykey()).append(" int").append(" primary key ,");	    		
//	    	}else{
//	    		for(int i=0;i<primarykeys.length;i++){
//	    			sb.append(primarykeys[i]).append(" int ,");
//	    		}
//	    	}
	    	for(ZiDuanPeiZi field : fieldMappings){
	    		if(field.getZiDuanType().equalsIgnoreCase("number") || field.getZiDuanType().equalsIgnoreCase("long") ||
	    				field.getZiDuanType().equalsIgnoreCase("Integer") || field.getZiDuanType().equalsIgnoreCase("int") ||
	    				field.getZiDuanType().equalsIgnoreCase("tinyint") || field.getZiDuanType().equalsIgnoreCase("bigint")){
	    			if(resExtDataSource.getDatabasetype().equalsIgnoreCase("MySQL")){
	    				sb.append(field.getZiDuanName()).append(" bigint").append(",");	   
	    			}
	    			if(resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")){
	    				sb.append(field.getZiDuanName()).append(" bigint").append(",");	   
	    			}
	    			if(resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")){
	    				sb.append(field.getZiDuanName()).append(" number").append(",");	   
	    			}
	    		}else if(field.getZiDuanType().equalsIgnoreCase("nvarchar") || field.getZiDuanType().equalsIgnoreCase("nvarchar2") ||
	    				field.getZiDuanType().equalsIgnoreCase("varchar") || field.getZiDuanType().equalsIgnoreCase("varchar2") || 
	    				field.getZiDuanType().equalsIgnoreCase("char") || field.getZiDuanType().equalsIgnoreCase("nchar")){
	    			sb.append(field.getZiDuanName()).append(" varchar").append("(").append(field.getZiDuanKuanDu()).append(")").append(",");	    			
	    		}else if(field.getZiDuanType().equalsIgnoreCase("float") || field.getZiDuanType().equalsIgnoreCase("double") ||
	    				field.getZiDuanType().equalsIgnoreCase("numeric") || field.getZiDuanType().equalsIgnoreCase("decimal")){
	    			if(resExtDataSource.getDatabasetype().equalsIgnoreCase("MySQL")){
	    				sb.append(field.getZiDuanName()).append(" decimal").append("(").append(field.getZiDuanKuanDu()).append(",2),");	
	    			}
	    			if(resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")){
	    				sb.append(field.getZiDuanName()).append(" decimal").append("(").append(field.getZiDuanKuanDu()).append(",2),");	
	    			}
	    			if(resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")){
	    				sb.append(field.getZiDuanName()).append(" number").append("(").append(field.getZiDuanKuanDu()).append(",2),");	   
	    			}
	    		}else if(field.getZiDuanType().equalsIgnoreCase("date") || field.getZiDuanType().equalsIgnoreCase("timestamp") ||
	    				field.getZiDuanType().equalsIgnoreCase("datetime")){
	    			if(resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")){
	    				sb.append(field.getZiDuanName()).append(" date").append(",");
	    			}else{
	    				sb.append(field.getZiDuanName()).append(" ").append(field.getZiDuanType()).append(",");
	    			}
	    		}else if(field.getZiDuanType().equalsIgnoreCase("blob") || field.getZiDuanType().equalsIgnoreCase("binary") || field.getZiDuanType().equalsIgnoreCase("tinyblob") || field.getZiDuanType().equalsIgnoreCase("image")){
	    			if(resExtDataSource.getDatabasetype().equalsIgnoreCase("MySQL")){
	    				sb.append(field.getZiDuanName()).append(" blob").append(",");
	    			}
	    			if(resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")){
	    				sb.append(field.getZiDuanName()).append(" binary").append(",");
	    			}
	    			if(resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")){
	    				sb.append(field.getZiDuanName()).append(" blob").append(",");
	    			}
	    		}else{
	    			sb.append(field.getZiDuanName()).append(" varchar (").append(field.getZiDuanKuanDu()).append(")").append(",");	    			
	    		}
	    	}
	    	sb.delete(sb.length()-1, sb.length());
	    	sb.append(")");
	    	//目标库执行sql 创建表
        	if(resExtDataSource.getDatabasetype().equalsIgnoreCase("SQLServer")){
    			String json = JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),sb.toString());
    			JSONArray jsonArray = JSONObject.parseArray(json);
    			if(jsonArray.getJSONObject(0).get("msg").toString().equals("该语句没有返回结果集。")) {
    				createTable = true;
    			}
        	}
    		if(resExtDataSource.getDatabasetype().equalsIgnoreCase("Oracle")){
    			JDBCUtils.OracleConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sb.toString());
    			createTable = true;
    		}
    		if(resExtDataSource.getDatabasetype().equalsIgnoreCase("MySQL")){
    			String json = JDBCUtils.MysqlConnUtils(resExtDataSource.getServeraddress(),resExtDataSource.getDatabasename(),resExtDataSource.getUsername(),resExtDataSource.getPassword(),resExtDataSource.getPort(),sb.toString());
    			JSONArray jsonArray = JSONObject.parseArray(json);
    			if(jsonArray.getJSONObject(0).get("msg").toString().equals("该语句没有返回结果集。")) {
    				createTable = true;
    			}
    		}
        }else{
        	createTable=true;
        }
        
        if(createTable){	//表创建成功或表已存在或表类型为空间表无需创建
        	//添加或更新订阅记录
        	if (resSubscriptions == null) {
                //增加的时候 添加最后更新时间和时间戳
                Timestamp audittime = new Timestamp(new Date().getTime());
                record.setLastupdate(audittime);
                String now = DateUtil.now();
                record.setTimestampfield(new Timestamp(new Date().getTime()));
                record.setAppdate(audittime);
                record.setAuditresult(0); //新增时默认未批复：0
                record.setAppid(null);
                record.setSyncresult(2);
                record.setTablefields(sbf.toString());
                subscriptionsResult = resSubscriptionsService.insertSelective(record);
            } else {
                //更新的时候 更新最后更新时间
                Timestamp audittime = new Timestamp(new Date().getTime());
                record.setLastupdate(audittime);
                record.setAudittime(null);
                record.setAudituserid(null);
                record.setAuditopinion(null);
                record.setSyncresult(2);
                record.setAppid(resSubscriptions.getAppid());
                subscriptionsResult = resSubscriptionsService.update(record);
            }
        }
        int result = subscriptionsResult == 1 ? 1 : 0;

        return "{\"result\":\"" + result + "\"}";
    }

    /**
     * 根据ID更新所有字段
     */
    @RequestMapping("/ressubscriptions/updateByPrimaryKey")
    public int updateByPrimaryKey(Res_Subscriptions record) {
        return resSubscriptionsService.updateByPrimaryKey(record);
    }

    /**
     * 资源订阅信息填写页面 订阅表
     */
    @RequestMapping("/ressubscriptions/ziyuandingyue")
    public String ziYuanDingYue(Model model, Res_Subscriptions resSubscriptions,Integer edit) {
        //获取订阅表字段
        if(resSubscriptions.getResourceid() != null) {
            resSubscriptions.setAppuserid(getUserId().toString());
        }
        Res_Subscriptions res_subscriptions = resSubscriptionsService.selectByPrimaryKey(resSubscriptions.getAppid());
        if (resSubscriptions.getAppid() == null && resSubscriptions.getResourceid() != null) {
            Res_Subscriptions res_subscriptions1 = new Res_Subscriptions();
            res_subscriptions1.setResourceid(resSubscriptions.getResourceid());
            model.addAttribute("ResSubscriptions", res_subscriptions1);
        } else if(res_subscriptions != null && resSubscriptions.getAppid() != null ) {
            model.addAttribute("ResSubscriptions", res_subscriptions);
            if (res_subscriptions.getDatasourceid() != null) {
                //根据数据源id，查询到账号密码
                Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_subscriptions.getDatasourceid());
                //开始拼接sql,然后判断是哪个数据库
                if(resExtDataSource != null) {
                    String leirong = "0";
                    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);
                    }

                    LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
                    if (!leirong.equals("0")) {
                        JSONArray obj = JSON.parseArray(leirong);
                        if (obj.getJSONObject(0).get("success").toString().equals("true")) {
                            JSONArray ob = JSON.parseArray(obj.getJSONObject(0).get("data").toString());
                            for (int i = 0; i < ob.size(); i++) {
//                            Map maps = (Map) JSON.parse(ob.get(i).toString());
                                map.put(ob.getJSONObject(i).get("TABLENAME").toString(), ob.getJSONObject(i).get("TABLENAME").toString());
                            }
                        }
                    }
                    model.addAttribute("map", map);
                }
                //查询id对应的 name
                Res_ExtDataSource res_extDataSource = resExtDataSourceService.selectByPrimaryKey(res_subscriptions.getDatasourceid());
                if(res_extDataSource != null) {
                    model.addAttribute("Databasename", res_extDataSource.getDatabasealias());
                    model.addAttribute("databasetype", res_extDataSource.getDatabasetype());
                    model.addAttribute("datasourceid", res_extDataSource.getDatasourceid());
                }
            }
        }
        model.addAttribute("edit", edit);
        model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
        model.addAttribute("systemName", sysConfig.getAppFullName());
        return "ResManage/ResApplyRecommend/ZiYuanDingYue";
    }

    /**
     * 访问关联标注图列表页面
     */
    @RequestMapping("/ressubscriptions/guanlianbiaozhutu")
    public String MainInfoByYWTC(Res_MainInfo maininfo, Model model) {
        model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
        model.addAttribute("resourceid", maininfo.getResourceid());
//        HashMap<String, String> ResourceTypeList = FieldUtils.getFieldListByKey("ResourceType");//获取资源类型列表
//        model.addAttribute("ResourceTypeList", ResourceTypeList);
//        HashMap<String, String> DataSourceList = FieldUtils.getDataSourceList();//获取数据来源列表
//        model.addAttribute("DataSourceList", DataSourceList);
        return "ResManage/ResApplyRecommend/GuanLianBiaoZhuTu";
    }

    @RequestMapping("/ressubscriptions/shujuyuan")
    public String muLuShu(Model model) {
        model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
        model.addAttribute("systemName", sysConfig.getAppFullName());
        HashMap<String, String> DatabaseTypeList = FieldUtils.getFieldListByKey("DatabaseType");// 获取数据库类型列表
        model.addAttribute("databasetype", DatabaseTypeList);
        return "ResManage/ResRegister/ShuJuYuan";
    }

    @ResponseBody
    @RequestMapping("/ressubscriptions/addshujuyuansave")
    public String addShuJuYuanSave(Res_ExtDataSource res_extDataSource, Model model) {
        if (res_extDataSource.getDatabasename().equals("")) {
            res_extDataSource.setDatabasename(null);
        }
        if (res_extDataSource.getDatabasetype().equals("")) {
            res_extDataSource.setDatabasetype(null);
        }
        if (res_extDataSource.getUsername().equals("")) {
            res_extDataSource.setUsername(null);
        }
        if (res_extDataSource.getPassword().equals("")) {
            res_extDataSource.setPassword(null);
        }
        if (res_extDataSource.getPort().equals("")) {
            res_extDataSource.setPort(null);
        }
        Timestamp audittime = new Timestamp(new Date().getTime());
        res_extDataSource.setCreatedate(audittime);
        int count = 0;
        if (res_extDataSource.getDatasourceid() == null) {
            // 获取当前登录人id
            res_extDataSource.setCreateuserid(getUserId().toString());
            count = resExtDataSourceService.insert(res_extDataSource);
        } else {
            count = resExtDataSourceService.updateByPrimaryKey(res_extDataSource);
        }

        model.addAttribute("datasourceid", res_extDataSource.getDatasourceid());
        return "{'count':'" + count + "','id':'" + res_extDataSource.getDatasourceid() + "'}";
    }

    @RequestMapping("/ressubscriptions/AddDataSource")
    public String AddDataSource(Model model,Integer datasourceid){
    	Res_ExtDataSource resExtDataSource = new Res_ExtDataSource();
		if (datasourceid != null) {
			resExtDataSource = resExtDataSourceService.selectByPrimaryKey(datasourceid);
		}
		model.addAttribute("datasource", resExtDataSource);
		model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
		model.addAttribute("systemName", sysConfig.getAppFullName());
		HashMap<String, String> DatabaseTypeList = FieldUtils.getFieldListByKey("DatabaseType");// 获取数据库类型列表
		model.addAttribute("databasetype", DatabaseTypeList);
		return "ResManage/ResApplyRecommend/AddDataSource";
    }

    @ResponseBody
	@RequestMapping("/ressubscriptions/SaveDataSource")
	public String SaveDataSource(Res_ExtDataSource res_extDataSource, Model model) {
		if (res_extDataSource.getDatabasename().equals("")) {
			res_extDataSource.setDatabasename(null);
		}
		if (res_extDataSource.getDatabasetype().equals("")) {
			res_extDataSource.setDatabasetype(null);
		}
		if (res_extDataSource.getUsername().equals("")) {
			res_extDataSource.setUsername(null);
		}
		if (res_extDataSource.getPassword().equals("")) {
			res_extDataSource.setPassword(null);
		}
		if (res_extDataSource.getPort().equals("")) {
			res_extDataSource.setPort(null);
		}
		Timestamp audittime = new Timestamp(new Date().getTime());
		res_extDataSource.setCreatedate(audittime);
		int count = 0;
		if (res_extDataSource.getDatasourceid() == null) {
			// 获取当前登录人id 
			res_extDataSource.setCreateuserid(getUserId().toString());
			count = resExtDataSourceService.insert(res_extDataSource);
		} else {
			// 获取当前登录人id
			res_extDataSource.setCreateuserid(getUserId().toString());
			count = resExtDataSourceService.updateByPrimaryKey(res_extDataSource);
		}

		model.addAttribute("datasourceid", res_extDataSource.getDatasourceid());
		return "{'count':'" + count + "','id':'" + res_extDataSource.getDatasourceid() + "'}";
	}
    
    @ResponseBody
	@RequestMapping("/ressubscriptions/deleteDataSource")
	public int deleteDataSource(Integer datasourceid) {
		return resExtDataSourceService.deleteByPrimaryKey(datasourceid);
	}
    
    @RequestMapping("/ressubscriptions/updateHangup")
    @ResponseBody
    public int updateHangup(Res_Subscriptions subscriptions,Integer hangup){
    	subscriptions.setHangup(hangup);
    	return resSubscriptionsService.update(subscriptions);
    }
    // ============================================================================
    //                                  后台管理
    // ============================================================================

    /**
     * 后台管理 - 我的订阅列表页面
     */
    @RequestMapping("/manage/subscriptions/index")
    public String index(Model model) {
        model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
        model.addAttribute("systemName", sysConfig.getAppFullName());
        return "manage/subscriptions/index";
    }

    /**
     * 后台管理 - 获取我的订阅列表
     */
    @ResponseBody
    @RequestMapping("/manage/subscriptions/getlist")
    @LogAction("资源管理,我的订阅,订阅列表查询,查询")
    public Result getlist(MainInfoJoinSubscriptions resSubscriptions, PageBean pageBean) {
        PageHelper.startPage(pageBean.getPage(), pageBean.getLimit());
        resSubscriptions.setAppuserid(getUserId().toString());
        //我的订阅是否直接查询自己订阅的资源就可以而不需要判断管理员 aleret ykm  2019/03/12
        // 查询当前登陆用户是否是超级管理员 是：查所有 不是：查自己的
//        if (!SecurityUtils.getSubject().isPermitted("org_user_admin")) {
            // 如果是超级管理员,不添加用户id，查询所有。如果不是 添加id 查询单个
//            resSubscriptions.setExistPermission(getUserId().toString());
//        }
        List<MainInfoJoinSubscriptions> list = resSubscriptionsService.selectResSubscriptions(resSubscriptions);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int countNums = (int) ((Page) list).getTotal();
        PageBean<MainInfoJoinSubscriptions> pageData = new PageBean<>(pageBean.getPage(), pageBean.getLimit(),
                countNums);
        pageData.setItems(list);
        OrgUser orgUser = getUser();

        List<Map<String, Object>> maps = new LinkedList<>();
        for (Integer i = 0; i < list.size(); i++) {
            // 查询id对应的name
            String auditname = "";
            if (list.get(i).getAudituserid() != null) {
                OrgUser eobj = orgUserService.queryObject(Long.parseLong(list.get(i).getAudituserid().toString()));
                auditname = eobj != null ? eobj.getChinesename() : "";
            }
            Map<String, Object> map = new HashMap<>();
            map.put("resourceid", list.get(i).getResourceid());
            map.put("appid", list.get(i).getAppid());
            map.put("title", list.get(i).getTitle());
            map.put("auditresult", getAuditResultName(list.get(i).getAuditresult()));
            map.put("audituserid", auditname);
            map.put("appdate", sdf.format(list.get(i).getAppdate()));
            map.put("audittime", (list.get(i).getAudittime() != null ? sdf.format(list.get(i).getAudittime()) : ""));
            map.put("lastupdate", (list.get(i).getLastupdate() != null ? sdf.format(list.get(i).getLastupdate()) : ""));
            map.put("syncresult", (list.get(i).getSyncresult() != null ? list.get(i).getSyncresult() : ""));
            map.put("hangup", (list.get(i).getHangup() != null ? list.get(i).getHangup() : ""));
            map.put("appreason", list.get(i).getAppreason());
            maps.add(map);
        }
        return Result.ok().put("totalCount", countNums).put("topics", maps);
    }

    /**
     * 后台管理 - 批复列表页面
     */
    @RequestMapping("/manage/subscriptions/audit")
    public String audit(Model model) {
        model.addAttribute("pubzyWebRoot", sysConfig.getPubzyWebRoot());
        model.addAttribute("systemName", sysConfig.getAppFullName());
        return "manage/subscriptions/audit";
    }

    /**
     * 后台管理 - 获取批复列表
     */
    @ResponseBody
    @RequestMapping("/manage/subscriptions/getauditlist")
    @LogAction("资源管理,订阅批复,订阅批复列表查询,查询")
    public Result getauditlist(MainInfoJoinSubscriptions resSubscriptions, PageBean pageBean) {
        PageHelper.startPage(pageBean.getPage(), pageBean.getLimit());
        // 查询当前登陆用户是否是超级管理员 是：查所有 不是：查自己的
        if (!SecurityUtils.getSubject().isPermitted("org_user_admin")) {
            // 如果是超级管理员,不添加用户id，查询所有。如果不是 添加id 查询单个
            resSubscriptions.setExistPermission(getUserId().toString());
        }
        List<MainInfoJoinSubscriptions> list = resSubscriptionsService.selectResSubscriptions(resSubscriptions);
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        int countNums = (int) ((Page) list).getTotal();
        PageBean<MainInfoJoinSubscriptions> pageData = new PageBean<>(pageBean.getPage(), pageBean.getLimit(),
                countNums);
        pageData.setItems(list);
        OrgUser orgUser = getUser();

        List<Map<String, Object>> maps = new LinkedList<>();
        for (Integer i = 0; i < list.size(); i++) {
            // 查询id对应的name
            String auditname = "";
            String appusername = "";
            if (list.get(i).getAppuserid() != null) {
                OrgUser eobj = orgUserService.queryObject(Long.parseLong(list.get(i).getAppuserid()));
                appusername = eobj != null ? eobj.getChinesename() : "";
            }
            if (list.get(i).getAudituserid() != null) {
                OrgUser eobj = orgUserService.queryObject(Long.parseLong(list.get(i).getAudituserid().toString()));
                auditname = eobj != null ? eobj.getChinesename() : "";
            }
            // 判断当前用户 与 资源用户是否一致 如果一致那么显示 是否批复
            Integer isDangQianYongHu = 0;
            if (orgUser.getUserid().toString().equals(list.get(i).getCreateuserid().toString())) {
                isDangQianYongHu = 1;
            }

            Map<String, Object> map = new HashMap<>();
            map.put("resourceid", list.get(i).getResourceid());
            map.put("appid", list.get(i).getAppid());
            map.put("title", list.get(i).getTitle());
            map.put("appuserid", appusername);
            map.put("applycount", list.get(i).getApplycount());
            map.put("auditresult", list.get(i).getAuditresult());
            map.put("audituserid", auditname);
            map.put("replycount", list.get(i).getReplycount());
            map.put("appdate", sdf.format(list.get(i).getAppdate()));
            map.put("isDangQianYongHu", isDangQianYongHu);
            map.put("appreason", list.get(i).getAppreason());
            maps.add(map);
        }
        return Result.ok().put("totalCount", countNums).put("topics", maps);
    }

    /**
     * 后台管理 - 保存批复
     */
    @ResponseBody
    @RequestMapping("/manage/subscriptions/save")
    public String save(Res_Subscriptions res_subscriptions) {
        if(res_subscriptions.getAuditresult() == 1) {
            logActionService.saveLogAction("资源管理,订阅批复,订阅批复（通过）,新增");
        }
        else if(res_subscriptions.getAuditresult() == 2) {
            logActionService.saveLogAction("资源管理,订阅批复,订阅批复（不通过）,新增");
        }
        int result = 0;
        Timestamp audittime = new Timestamp(new Date().getTime());
        res_subscriptions.setAudittime(audittime);
        res_subscriptions.setAudituserid(getUserId().intValue());
        int updateresult = resSubscriptionsService.updateResSubscriptionsResult(res_subscriptions);
        if (updateresult == 1) {
            result = 1;
        }
        return "{'result':'" + result + "'}";
    }

    /**
     * 后台管理 - 查看批复意见
     */
    @RequestMapping("/manage/subscriptions/opinion")
    public String opinion(Integer appid, Model model) {
        Res_Subscriptions subscriptions = resSubscriptionsService.selectByPrimaryKey(appid);
        model.addAttribute("res_subscriptions", subscriptions);
        return "manage/subscriptions/opinion";
    }

    /**
     * 后台管理 - 查看申请理由
     */
    @RequestMapping("/manage/subscriptions/content")
    public String content(Integer appid, Model model) {
        Res_Subscriptions subscriptions = resSubscriptionsService.selectByPrimaryKey(appid);
        model.addAttribute("res_subscriptions", subscriptions);
        return "manage/subscriptions/content";
    }

    //根据批复状态获取状态名称
    private String getAuditResultName(Integer result) {
        String name = "未批复";
        if (result != null) {
            switch (result) {
                case 0:
                    name = "未批复";
                    break;
                case 1:
                    name = "已通过";
                    break;
                case 2:
                    name = "未通过";
                    break;
            }
        }
        return name;
    }

//    //查询我订阅的数据库 然后在推送
//    @Scheduled(cron = "${sys.scheduled}")
//    public void DataPush() {
//        logger.info("检查表更新");
//        //查询所有的订阅源
//        List<Res_Subscriptions> list = resSubscriptionsService.selectAll();
//        //result：0代表更新失败 1代表更新成功 2代表无更新数据
//        int result = 0;
//        for (Res_Subscriptions res_subscriptions : list) {
//            //同步状态 1为正在同步  0代表同步完成
//            if(res_subscriptions.getDatesync()!=null){
//                if(res_subscriptions.getDatesync()==1){
//                    continue;
//                }
//            }
//            //判断最后更新时间距离当前时间有没有超过 我设定的更新时间
//            Date date1=res_subscriptions.getLastupdate();
//            Date date2=new DateTime();
//            int betweenDay= (int) DateUtil.between(date1,date2,DateUnit.HOUR);
//            if(betweenDay>=res_subscriptions.getSynctimes()){
//                logger.info("开始同步表:"+res_subscriptions.getTablename());
//               result=getResult(result,res_subscriptions);
//               if(result==1){
//                   logger.info("同步表:"+res_subscriptions.getTablename()+"完成");
//               }
//                if(result==0){
//                    logger.info("同步表:"+res_subscriptions.getTablename()+"失败,请检查字段");
//                }
//                if(result==2){
//                    logger.info("表:"+res_subscriptions.getTablename()+"暂时无新数据需要同步");
//                }
//            }else {
//                int betweenMin= (int) DateUtil.between(date1,date2,DateUnit.MINUTE);
//                int Synctimes=res_subscriptions.getSynctimes()*60;
//                int timeInterval=Synctimes-betweenMin;
//                logger.info("表 "+res_subscriptions.getTablename()+"距离下次同步时间还差 "+timeInterval+" 分钟");
//            }
//        }
//    }

    //同步数据 关键方法
    private int getResult(int result, Res_Subscriptions res_subscriptions){
        //读取目标数据库设置内容
        String leirong = ReadTheContent(res_subscriptions);
        //复制到我关联的数据库表格中
        JSONObject sqlObj = JSON.parseObject(leirong);
        //无结果集 {key:'fkey',data:'[]'}
        if (sqlObj.getString("data").equals("无结果集") || sqlObj.getString("data").equals("[]")) {
            result = 2;
            //更新数据最后更新时间
            Res_Subscriptions res_subscriptions1 = new Res_Subscriptions();
            res_subscriptions1.setAppid(res_subscriptions.getAppid());
            Timestamp time = new Timestamp(new Date().getTime());
            res_subscriptions1.setLastupdate(time);
            res_subscriptions1.setDatesync(0);
            resSubscriptionsService.update(res_subscriptions1);
            return result;
        }
        JSONArray jsarr = JSONArray.parseArray(sqlObj.getString("data"));
        //获取我的数据库表账号密码
        Res_ExtDataSource resExtDataSource = resExtDataSourceService.selectByPrimaryKey(res_subscriptions.getDatasourceid());
        //查询所有数据
        String sql = "select * from " + res_subscriptions.getTablename();
        String LocalData = JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), sql);
        //获取数据库主键，然后对比(别人分享的字段必须包含主键)
        JSONArray jsarrs = JSONArray.parseArray(LocalData);
        List listId = new ArrayList();
        if(jsarrs.getJSONObject(0).get("success").toString().equals("true")) {
            jsarrs = JSONArray.parseArray(jsarrs.getJSONObject(0).get("data").toString());
            for (int i = 0; i < jsarr.size(); i++) {
                int k = 0;
                for (int j = 0; j < jsarrs.size(); j++) {
                    if (((JSONObject) jsarr.get(i)).get(sqlObj.getString("key")).equals(((JSONObject) jsarrs.get(j)).get(sqlObj.getString("key")))) {
                        k = 1;
                    }
                }
                if (k == 1) {
                    //添加要更新的id，没有添加的id就是做增加操作。
                    //((JSONObject) jsarr.get(i)).get(sqlObj.getString("key"))这个就是主键对应的值。
                    listId.add(((JSONObject) jsarr.get(i)).get(sqlObj.getString("key")));
                }
            }
            //将 jsarr中的数据拼接，放到我设置的数据库里
            for (int i = 0; i < jsarr.size(); i++) {
                String tableName = "";
                String value = "";
                String updata = "";
                String where_condition = "";
                int s = 0;
                for (Map.Entry<String, Object> entry : jsarr.getJSONObject(i).entrySet()) {
//                        System.out.println(entry.getKey() + ":" + entry.getValue());
                    if (s != 0) {
                        tableName += " ,";
                        value += " ,";
                        updata += " ,";
                    }
                    tableName += entry.getKey();
                    value += "'" + entry.getValue() + "'";
                    updata += entry.getKey() + " = " + "'" + entry.getValue() + "'";
                    s++;
                    if (entry.getKey().equals(sqlObj.getString("key"))) {
                        where_condition = entry.getKey() + " = " + "'" + entry.getValue() + "'";
                    }
                }
                int k = 0;
                for (int j = 0; j < listId.size(); j++) {
                    if (listId.get(j).equals(((JSONObject) jsarr.get(i)).get(sqlObj.getString("key")))) {
                        k = 1;
                    }
                }
                String SqlOne = "";
                if (k == 1) {
                    SqlOne = "UPDATE " + res_subscriptions.getTablename() + " SET " + updata + " WHERE " + where_condition;
                } else {
                    SqlOne = "INSERT INTO " + res_subscriptions.getTablename() + " (" + tableName + ") VALUES(" + value + ")";
                }
                try {
                    JDBCUtils.SqlServerConnUtils(resExtDataSource.getServeraddress(), resExtDataSource.getDatabasename(), resExtDataSource.getUsername(), resExtDataSource.getPassword(), SqlOne);

                    result = 1;
                } catch (Exception e) {
                    result = 0;
                }
            }
        }
        //更新订阅的 最后更新时间
        if (result == 1 ) {
            Res_Subscriptions res_subscriptions1 = new Res_Subscriptions();
            res_subscriptions1.setAppid(res_subscriptions.getAppid());
            Timestamp time = new Timestamp(new Date().getTime());
            res_subscriptions1.setLastupdate(time);
            res_subscriptions1.setDatesync(0);
            resSubscriptionsService.update(res_subscriptions1);
        }
        return result;
    }

    //读取资源对应的数据库表的内容
    private String ReadTheContent(Res_Subscriptions res_subscriptions) {
        String sqlConfig = "";
        String leirong = "";
        //获取关联的资源id,并且查询关联id对应的数据库配置，关联的字段。
        DataBaseLeftDataSource database = resExtDataBaseService.selectBaseLeftSource(res_subscriptions.getResourceid());
        //开始拼接sql,然后判断是哪个数据库
        JSONArray jsarr = new JSONArray();
        if(database != null) {  //null 值处理 alert 2019-01-08
            JSONObject sqlObj = JSON.parseObject(database.getFieldconfig());
            jsarr = JSONArray.parseArray(sqlObj.getString("sql"));
        }
        //获取订阅表的时间戳字段，然后比较时间先后
        String timestampfield = database.getTimestampfield().trim();
        LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
        for (int i = 0; i < jsarr.size(); i++) {
            ZiDuanPeiZi ziDuanPeiZi = JSONUtil.toBean(jsarr.get(i).toString(), ZiDuanPeiZi.class);
            if (i > 0) {
                sqlConfig += ",";
            }
            String Geshi = ziDuanPeiZi.getZiDuanGeShi().toJSONString();
            String ZiDuanMing = ziDuanPeiZi.getZiDuanName();
            sqlConfig += ZiDuanMing;
        }
        //对比时间戳字段
        String beginTime = res_subscriptions.getLastupdate().toString();
        String sql = "";
        if (!timestampfield.equals("")) {
            sql = "select " + sqlConfig + " from  " + database.getTablename() + " where " + timestampfield + " >=" + "'" + beginTime + "'";
            //判断是否有检索条件
            if (!res_subscriptions.getQuerywhere().replace(" ", "").equals("") ) {
                sql = "select " + sqlConfig + " from  " + database.getTablename() + " where " + timestampfield + " >=" + "'" + beginTime + "'" + " and " + res_subscriptions.getQuerywhere();
            }
        } else {
            sql = "select " + sqlConfig + " from  " + database.getTablename();
            if (!res_subscriptions.getQuerywhere().replace(" ", "").equals("") ) {
                sql = "select " + sqlConfig + " from  " + database.getTablename() + " where " + res_subscriptions.getQuerywhere();
            }
        }
        leirong = "不支持该数据库";
        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 = JSONArray.parseArray(leirong);
            if(obj.getJSONObject(0).get("success").toString().equals("true")) {
                leirong = obj.getJSONObject(0).get("data").toString();
            }
            else {
                leirong = obj.getJSONObject(0).get("msg").toString();
            }
        }
        return "{key:'" + database.getPrimarykey() + "',data:'" + leirong + "'}";

    }
    
    //=============================订阅同步日志================================//
    @RequestMapping("/manage/subscriptions/topage")
    public ModelAndView topage(){
    	ModelAndView model = new ModelAndView("manage/subscriptions/subscriptions_list");
    	model.addObject("pubzyWebRoot", sysConfig.getPubzyWebRoot());
        model.addObject("systemName", sysConfig.getAppFullName());
    	return model;
    }
    
    @RequestMapping("/manage/subscriptions/getSubscriptionsList")
    @ResponseBody
    public Result getSubscriptionsList(Res_Subscriptions res_Subscriptions,PageBean pageBean){
    	PageHelper.startPage(pageBean.getPage(), pageBean.getLimit());
    	// 查询当前登陆用户是否是超级管理员 是：查所有 不是：查自己的
        if (!SecurityUtils.getSubject().isPermitted("org_user_admin")) {
            // 如果是超级管理员,不添加用户id，查询所有。如果不是 添加id 查询单个
        	res_Subscriptions.setAppuserid(getUserId().toString());
        }
    	List<Res_Subscriptions> resSubscriptionslist = resSubscriptionsService.findList(res_Subscriptions);
    	SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        int countNums = (int) ((Page) resSubscriptionslist).getTotal();
        PageBean<Res_Subscriptions> pageData = new PageBean<>(pageBean.getPage(), pageBean.getLimit(), countNums);
        pageData.setItems(resSubscriptionslist);
    	StringBuffer sb = new StringBuffer();
    	sb.append("{'totalCount':").append("'"+countNums+"','topics':[");

    	List<Map<String, Object>> maps = new LinkedList<>();
    	if(!resSubscriptionslist.isEmpty() && resSubscriptionslist.size()>0){
    		for(Res_Subscriptions res : resSubscriptionslist){
    			OrgUser user = orgUserService.queryObject(Long.parseLong(res.getAppuserid()));
				OrgUnit unit = orgUserService.getDefaultUnit(Long.parseLong(res.getAppuserid()));
                if(res.getSyncresult()==0){
                    sb.append("'syncresult':'").append("同步成功',");
                }else if(res.getSyncresult()==1){
                    sb.append("'syncresult':'").append("同步失败',");
                }else{
                    sb.append("'syncresult':'").append("暂未同步',");
                }

                Map<String, Object> map = new HashMap<>();
                map.put("appid", res.getAppid());
                map.put("resourceid", res.getResourceid());
                map.put("resourcename", res.getTitle());
                map.put("appunitid", res.getAppunitid());
                map.put("appunitname", unit.getUnitname());
                map.put("appuserid", res.getAppuserid());
                map.put("appusername", user.getChinesename());
                map.put("appdate", sdf.format(res.getAppdate()));
                map.put("lastupdate", sdf.format(res.getLastupdate()));
                map.put("syncmessage", res.getSyncmessage());
                maps.add(map);

//				sb.append("{'appid':").append(res.getAppid()+",");
//    			sb.append("'resourceid':").append(res.getResourceid()+",");
//    			sb.append("'resourcename':'").append(res.getTitle()+"',");
//				sb.append("'appunitid':'").append(res.getAppunitid()+"',");
//				sb.append("'appunitname':'").append(unit.getUnitname()+"',");
//				sb.append("'appuserid':'").append(res.getAppuserid()+"',");
//				sb.append("'appusername':'").append(user.getChinesename()+"',");
//				sb.append("'appdate':'").append(sdf.format(res.getAppdate())+"',");
//
//				sb.append("'lastupdate':'").append(sdf.format(res.getLastupdate())+"',");
//				sb.append("'syncmessage':'").append(res.getSyncmessage()+"'},");
    		}
//    		sb.delete(sb.length()-1, sb.length());
    	}
//    	sb.append("]}");
//		System.out.println(sb.toString());
		return Result.ok().put("totalCount", countNums).put("topics", maps);
    }
}